Groups-People Database Design - Need advise

views:

answers:

Groups-People Database Design - Need advise

+1 A:

Oy. A given email can be associated with different "people"? I would argue the semantics of that are a quite ugly. I would rename the people table Contacts. I would also rename the 'name' field in the Emails table to DefaultName to indicate it's strictly a fallback.

Semantics aside (which are a big deal, if someone else has to use this db), you reference an owner as a foreign key using varchar(20). There's no indication of what it is referencing, I assume it is some sort of Owners table, with a username as the primary key. I would recommend an int surrogate key... What happens if a user wants to change their username?

Lastly, the stickler in me would change the 'groupless' contacts. I would make a default group for each contact, call it _Groupless, and assign 'groupless' contacts like Tanya to the _Groupless group. The application can sort out the details. I don't particularly like optional foreign keys.

Shlomo 2010-08-09 23:36:47

Thanks a lot for your answer. I accept most of what you say. However, I didn't say that "A given email can be associated with different people". **person_id** 1 and 5 in `People Table` do have the same **email_id** but it doesn't mean that these are different people. This is the same person, John, who's email is `[email protected]`. misha calls him John (the default name) while jenya calls him Johnny. Does this makes sense, or I'm still missing something ? There is indeed another `Owners Table` and I will really change owner name to owner_id.

Misha Moroshko 2010-08-10 00:15:07

I understand what you're saying, I think it would be better to make the name of the table Contacts.Reason is that under your scenario, two different lines in the 'People' table can refer to the same real-life person. Normally you wouldn't think of different rows in a 'People' table as referring to the same person. A Contacts table on the other hand... if I have my mother in my email address book, and my sister does as well, that's two different contacts, but one person. Calling it people makes it look like my mother is two different people....

Shlomo 2010-08-10 04:19:11

I agree regarding changing the name to Contacts. Thanks!

Misha Moroshko 2010-08-10 09:39:27

ansaurus

tags:

views:

answers:

Groups-People Database Design - Need advise

related questions