0

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.

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223

3 Answers3

3

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).

Sampson
  • 265,109
  • 74
  • 539
  • 565
  • And what if you only need one NOW, but later need more? Why risk that? – Peter Jul 06 '09 at 13:57
  • I'm assuming the user has planned out his business and database. – Sampson Jul 06 '09 at 14:00
  • @Peter, your comment makes me think YAGNI. Beware of overcomplicating the current design because you might need it later. – Nathan Koop Jul 06 '09 at 14:06
  • I agree. Lending yourself too much to a "what-if" mentality can quickly create a mess of your schema. I do agree with Peter on the point that you should consider common things like multiple phone numbers (if you track phone numbers), etc. – Sampson Jul 06 '09 at 14:11
  • not the same at all i'm afraid you mingle databasedesign with programming... In the real world, all tables are split up... – Peter Jul 06 '09 at 14:50
  • That is : in companies that survive crisis times too : ;-) – Peter Jul 06 '09 at 14:53
  • Ooh yes, and as far as the addresses go, what about historical data? – Peter Jul 06 '09 at 14:58
  • Peter, you're assuming too much about the user. They didn't ask about keeping a history of the data. If they did, our solutions would be different. I'm basing my answer on what the user provided. – Sampson Jul 06 '09 at 15:05
  • I do stand with you though in pointing out the user didn't provide much detail, and if it were me, I would make a phone number table, even if I only have one phone number. – Sampson Jul 06 '09 at 15:08
3

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.

Alan
  • 3,815
  • 1
  • 26
  • 35
0

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.

Peter
  • 47,963
  • 46
  • 132
  • 181
  • 1
    I disagree with your address example, unless there are extenuating cercumstances, I would find it difficult to believe there will be so many "friends" living at the same address that it would be beneficial to model it that way. However, I agree regarding email addresses & phone numbers should be stored in a different table (unless you *only* want to store one) – Nathan Koop Jul 06 '09 at 14:10
  • And I rest my case here, databasedesigners (which is my job) contra programmers is an endless figth I guess :-) – Peter Jul 06 '09 at 14:56