DB design : members table separate or all in one t

问题:

I want to create a table of friends with personal information and log on details.

What better to separate the members table to 2 tables , one contain minimal details , second with Other details.

or remain in one table ?

i have a lot of tables that contain the foreign key of the member.

回答1:

It depends a lot on what those "other" details are. This is a common and interesting question, and there is no "hard and fast" answer at first glance. But if we think of the issue more abstractly, about the actual relationship among the attributes ("details") of any particular thing you want to represent, we may find some clarity.

In your question you state that friends have "minimal" and "other" details. Rather than classifying these details as "minimal" or "other", let's classify them by whether or not any individual ("atomic") detail can be fully determined by whatever makes a friend unique.

I presume there is some primary key (PK), like FriendID or e-mail address or something. Considering this unique identifier, ask yourself: "If I'm given exactly one FriendID (or e-mail or whatever you are using as PK) what details of that friend am I absolutely sure of? E.g., given FriendID=2112, I absolutely know that friend's first name, last name, and date of birth, but I do not absolutely know that friend's phone number because there is more than one of them.

Group together in one table all the details you unambiguously know given the PK. Put the details for which you need more data (like "home" or "work" in the case of phone numbers) in "child" tables, foreign-keyed back to "parent" table on the PK. (Note: It's extremely likely that the PK of the child table will be composite; that is, composed of the parent table's PK and the differentiating factor (like "home" or "work" in this example). Composite keys for the many side of 1-M relations are very good.)

Database geeks call this decomposition based on functional dependencies.

回答2:

One table, unless you potentially need to associate one member to multiple sets of details (ie multiple email addresses, user-groups, day-phone, night-phone, cell-phone, etc).

回答3:

No question about it : always split up tables when it makes sense logically.

Eg : Friend 1 : Tom Jones lives in The Valley Friend 2 : Erin Jones lives their too since it's his brother

tables :

Friends
Id  Name          Address
1   Tom Jones     1
2   Erin Jones    1

Adresses 
Id Address
1  The valley

Otherwise things always will come up like :

Friends
Id  Name          Address
1   Tom Jones     The Valey
2   Erin Jones    The Valley

Which will lead to erroneous queries.

That's just one issue, there are numerous. Like what if so has 2 e-mail addresses and 3 cell phone numbers? What if a streetname changes and 5 friends live in it?

If you are very sure your table will be small, and you don't have to query it, than you could use just one table. But than you can just use some excell like sw too, or a piece of paper for that matter :-)

But if you want to have a database, treat it as one.

Read about Normalization for the whole issue.