February 3, 2015 at 3:13 pm
Helo, I'm looking for help as to what the right table setup is when you have a table with values that will never change.
I'm building a small project for our sales team so they can track their leads & customers. I have a table called "Contacts", which contains the names of every prospect they've ever reached out to. In that table is a field called "ContactTypeId", which is a reference to a table called "ContactTypes".
My ContactTypes table looks like this:
ContactTypeId | Name | Description
----------------------------------------------
1 | Lead | A contact who has shown interest in our service
2 | Qualified Lead | A more deeply engaged, sales-ready contact
3 | Customer | A contact paying for our services
So, I set it up this way because I figured it was cleaner to associate a ContactTypeID# to each Contact in the Contacts table, rather than actually spelling out "Lead", "Customer", etc for each record in the Contacts table.
But is this right? If these values in my ContactTypes table are NEVER going to change, should I still have this table at all or is it better to just insert "Lead", "Qualified Lead", or "Customer" into the Contacts table? Just looking to take the correct approach.
Thanks
February 3, 2015 at 3:31 pm
But the values in the Contact table can change, correct? What you have setup is a lookup table, and doing so is fine. You may find that you add additional ContactTypes, you may find you made a spelling error in the name/description of a ContactType.
February 3, 2015 at 3:53 pm
The separate table is really the only acceptable method for that type of data, since it could see many modifications in the future. For example, you could add types, change the names somewhat, maybe add foreign-language versions of those names to support Spanish, etc..
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 3, 2015 at 7:24 pm
Never say never!
One day you'll want to add "4 - prior customer" for a contact that used to be a customer but is no longer paying for your services.
I'd go with the separate table, even though it is small, because it gives you a nice place to source a dropdown list in the application's front end.
Don't forget the FOREIGN KEY constraint on your contacts table to maintain the referential integrity.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 3, 2015 at 11:11 pm
dwain.c (2/3/2015)
Never say never!One day you'll want to add "4 - prior customer" for a contact that used to be a customer but is no longer paying for your services.
I'd go with the separate table, even though it is small, because it gives you a nice place to source a dropdown list in the application's front end.
Don't forget the FOREIGN KEY constraint on your contacts table to maintain the referential integrity.
Not to mention possibly adding a "blackball" category and a "preferred" category.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply