February 19, 2010 at 2:57 pm
HI Guys, just want to know how some of you would handle this.
The software developer in me wants to use a generic table for "contact" attributes...
The database developer in me wants a table for each as the needs are slightly different.
This is for a pretty small database and I don't forsee needing to search for either value
For email, something like:
EmailId int identity(1,1) not null,
PersonId int not null,
EmailTypeId tinyint not null, (Home, work, other)
IsPreferred bit not null, (This email is the preferred email for contact)
EmailAddress varchar(50) not null
For phone something like:
PhoneId int not null,
PersonId int not null,
PhoneTypeId tinyint not null, (Home, work, cell, etc)
IsPreferred bit not null, (This ph# is preferred ph# for contact)
PhoneNumber char(10) (if I recall, more than 10 characters not necessary as country codes are dependent on where you are calling FROM not calling TO)
They are of course very similar with the only real difference being the meaning of the "typeid". I guess I am just trying to justify not adding a table for what is essentially one piece of data. But I suppose that I would implement it with a table for each if I didn't hear any other arguments against it.
What do you guys think?
February 20, 2010 at 6:41 am
If I'm getting the question right, you're asking if you should have seperate tables for phones & emails from your contact table. Well, let me ask you a question. Do you have more than one email address? Do you have more than one phone number associated with your name? If the answer to both these is yes, then your contact table, supposing it was denormalized, would need to have, let's say, two entries for email & two for phone (or you could go with a varchar(max) field and start storing comma delimited lists, have fun searching for emails or phone numbers in that situation). But, hey, in addition to my cell phone & home phone, I also have a work phone. Let's add a third column for phone. Oh, and I really do have four email adddresses that could be associated, so let's add four more fields... You begin to see where this is going right?
Let's take it an other step. My wife & I share a phone. Now you need to add that phone to two people, but the structure you're providing means it has to be entered twice. Let's say you're running a robo-calling service (not that I'm implying you'd be evil like that). Two calls are now going to come to my phone instead of one. Or, even worse, you'll get a typo on entry and one call will come to me and the one meant for my wife will go to some retiree in Arizona. Or you have a contact that is part of a 5000 person company and they all have the same phone number. Do you really want to enter it 5000 times, hoping to get it right, each & every time. How about 10,000 or more?
I'd suggest breaking it down further than you have. Store a phone table for phone numbers. You can add a lookup to that for phone number type. Then have a many-to-many table to join between phone number and contact. That's where you can put the preferred phone and an extension number.
Email is a little tougher. You might want to do the same structure since, in theory, more than one person can use an email address, but by & large, most people have unique entries, so your structure would probably be OK.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply