Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ErinT
Starting Member
3 Posts |
 Posted - 2007-10-01 : 15:37:47
|
| I've done some basic database design in the past, but am a relative newbie to design. I have recently come across a situation I'm not sure how to handle. Here's the situation...Assume we've got a contacts table which holds information about our contacts. All contacts have the same basic information - name, address, telephone, etc. Each contact is of a certain type - let's just say a, b, and c, for ease. This contact type is stored in the contacts table. Now, on contacts of type b, I also have to store some additional data. What it is doesn't really matter. I found a way to set this up, but I'm not sure that I'm going about it the right way, and would love some advice on the proper way to do this. Basically, what I did is create my contacts table:Contact_id, contactName, ContactAddress, ContactPhone, ContactType.Created a contacttype tableContactType, ContactTypeDescription, ContactAddInfoWhat I've done is left contactaddInfo as a nullable field. When it has a value, that value is the name of a table which holds the additional information that I need for the contact... So when I'm running a query, I can tell if I need to reference another table by checking the value of ContactAddInfo.I can't imagine that this is the best solution, but it was the first thing that popped into my head, and it's a really small database that's using it. However, I'm now being faced with the same situation in a much more important, larger database, and I'd love to know the 'right' way.Thanks for any guidance you can provide!Erin |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-10-02 : 04:24:38
|
| Have another table for each of a, b & c with a reference to your main contact_ID. You then have 'base data' and 'additional data' dependent on the contact type.Is this what you mean? |
 |
|
|
ErinT
Starting Member
3 Posts |
Posted - 2007-10-03 : 12:58:42
|
| Yes, that's what I meant, and basically what I've done. I just wasn't sure if there was a more efficient way of handling it. Thanks for the response. |
|
|
|
hshot_rooke
Starting Member
8 Posts |
Posted - 2007-10-04 : 15:38:44
|
| Erin,I am looking for a similar solution.Using a similar DB structure to yours:We want to store coaches, students, etc. in a contacts table, but want to store Coach ID#s, Student ID#s and any additional information for different types of contacts.LoztInSpace has a correct idea which we have implemented in the past, but we are looking for a more efficient solution. The problem with this solution is that you must now find out what type of contact the person is in order to query the specific table that the additional information is in. You MUST find out the type of contact EACH time you query the contacts table and the additional table.This isn't that big of a problem, but I am trying to find an alternative. There is another solution we have tried. For each contact create a stored procedure that will gather the proper information from your extended tables. Store these SP names in a table and relate each record in your contacts table to the table with the SP name. Now grab the SP name and dynamically execute that stored procedure that will get you the extended contact information from the correct tables.We have run into problems with this too, but not a bad solution. |
|
|
|
ErinT
Starting Member
3 Posts |
Posted - 2007-10-05 : 09:19:34
|
| hshot_rooke,Interesting approach. I never thought about the possibility of using sprocs to do this. I'll have to experiment and see what seems to be more efficient. I've been doing limited database design work unofficially for 3-4 years, and I've had some classes and such on it, but about 2 weeks ago, I officially got a DBA position - and I'd really like to look competent! I'm glad to see that my original solution wasn't too far off base - that increase my confidence level a tad. For now, at least, I'll probably end up designing the web interface to access this too, so having to write the queries to check that contact type each time isn't a big deal, but in the future, it might be nice not to have the web developer have to know to do that. I think sprocs may be able to help with this.... Neat idea. Thanks. |
|
|
|
|
|
|
|
|
Database Design Question - holding additional data