CODEWITHSUNDEEP
sqlsql-servert-sql
atatko
atatko

Reputation: 481

More joins or more columns?

I have a very basic question, which would be a more efficient design, something that involves more joins, or just adding columns to one larger table?

For instance, if we had a table that stored relatives like below:

Person  |  Father  |  Mother  |  Cousing  | Etc.
________________________________________________

Would it be better to list the name, age, etc. directly in that table.. or better to have a person table with their name, age, etc., and linked by person_id or something?

This may be a little simplistic of an example, since there are more than just those two options. But for the sake of illustration, assume that the relationships cannot be stored in the person table.

I'm doing the latter of the two choices above currently, but I'm curious if this will get to a point where the performance will suffer, either when the person table gets large enough or when there are enough linked columns in the relations table.

Upvotes: 1

Views: 199

Answers (3)

Jeffrey Blake
Jeffrey Blake

Reputation: 9709

It is a much more flexible design to separate out the details of each person from the table relating them together. Typically, this will lead to less data consumption.

You could even go one step further and have three tables: one for people, one for relationship_types, and one for relationships.

People would have all the individual identifying info -- age, name, etc.

Relationship_types would have a key, a label, and potentially a description. This table is for elaborating the details of each possible relationship. So you would have a row for 'parent', a row for 'child', a row for 'sibling', etc.

Then the Relationships table has a four fields: one for the key of each person in the relationship, one for the key of the relationship_type, and one for its own key. Note that you need to be explicit in how you name the person columns to make it clear which party is which part of the relationship (i.e. saying that A and B have a 'parent' relationship only makes sense if you indicate which person is the parent vs which has the parent).

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Id' go for more "Normality" to increase flexibility and reduce data duplication.

PERSON:
ID
First Name
Last Name

Person_Relations
PersonID
RelationID
TypeID

Relation_Type
TypeID
Description

This way you could support any relationship (4th cousin mothers side once removed) without change code.

Upvotes: 2

Ian Kenney
Ian Kenney

Reputation: 6426

Depending on how you plan to use the data a better structure may be

  • a table for Person ( id , name etc )

  • a table for relationships (person_a_id, person_b_id, relation_type etc)

where person_a_id and person_b_id relate to id in person sample data may look like

Person 

 ID Name 
  1 Frank
  2 Suzy
  3 Emma

Relationship

  A B Relationship 
  1 2 Wife
  2 1 Husband
  1 3 Daughter
  2 3 Daughter
  3 1 Father
  3 2 Mother

Upvotes: 1

Related Questions