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 |
|
Twilkes
Starting Member
2 Posts |
 Posted - 2011-02-25 : 04:19:22
|
| I have a database of people, and I'm storing their relationships - e.g. who is a parent of who, and which pairs are couples.I could either have a field in the main table, such that everyone has a 'spouse' field, which is either the ID of their spouse, or null if they are single. And similar fields for their father and mother (for the purposes of this database, not everyone has their parent details recorded, if their parents aren't a member of the database, so there would also be null values stored here).Or, I could have another table, 'Couples', where each row is just a pair of IDs linking back to the main table. And similarly, a 'Parents' table, with a person ID, and one field each for motherID and fatherID, one of which (but not both) may be null.What are the pros and cons of each? The former may take up more space, as it is potentially storing lots of null values in the main table. Whereas the second way would make queries longer and more complicated, possibly affecting performance.Would it depend on how many records are stored, e.g. a few thousand as compared to a few hundred thousand?Thanks for any input,Tony |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-25 : 09:15:40
|
One way to design it would be to have one "Person" table and one "Relationship" table. The person table would have characteristics of each person like name, age, sex, etc and the relationship table would have the ID of two persons and a column for their relationship. Example:PersonsID Name Sex DOB1 Henning Male 197603062 Dorthea Female 19521016RelationsPersonID1 PersonID2 Relationship2 1 Parent1 2 Child I have never really created any social networks before but this is at least a way to do it in a normalized way (the relationship will naturally be an ID as well).- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-02-25 : 10:44:17
|
Make relationship a dimension table too...  Table RelationsTargetID, SourceID, RelationshipTypeIDTable RelationshipTypeRelationshipTypeID , RelationshipTypeName
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Twilkes
Starting Member
2 Posts |
Posted - 2011-02-25 : 10:51:11
|
| So putting them in extra tables is preferable to having them as a field in the original 'Persons' table? Good good. Just didn't want to end up with dozens of tables each with only two fields when some of this could have been stored in the original.To be honest, for the small project I'm working on it doesn't really matter but it's nice to know for the future. :)Thanks for the responses, |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2011-02-25 : 18:11:20
|
| It depends on requirements. If only two relationships that matter are parent-child and couple, I would put nullable columns in the persons table. That way it is simpler to enforce rules.However, homosexual couples and harems might pose a challenge whatever design you choose. For couples you also might need to introduce start and end date.MirkoMy blog: http://mirko-marovic-eng.blogspot.com/ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-28 : 03:06:16
|
quote:
Originally posted by Twilkes
So putting them in extra tables is preferable to having them as a field in the original 'Persons' table?
As mmarovic says it depends (this is btw the answer to absolutely *all* database design question, just so you know ). If you are positive that you only need a few relations and that they are in the simplest form (only one single relationship) then it would be far easier to keep it all in the persons table. But if you want to keep information about more complex relationships you should try to use the proposed design, and maybe also adding start and end dates for the relationships as well. Two people can i.e. be enganged, married and divorced but at different times. Two people can also be i.e. sibling, school mate and colleague.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
|
|
|
|
How much normalisation is necessary?