Monday, April 03, 2006

Database Design Relationships

  • 1-1:

    • In order to create the relationship between two relations with a 1-1 relationship - post the primary key of one table into the other table, it doesn't matter which way around.

    • The key placed into the other relation is called a foreign key.

    • MP{name, dob, party, constituencyName*}

    • Constituency{name, size}

  • 1-Many:

    • To create the relationship - the primary key of the 1 side is posted to the many side as a foreign key.

    • LibraryBook{id_no, name}

    • Reader{membership_no, name, book_id*}

    • Imagine if the many side was posted to the one side instead.

  • Many-Many (M-N):

    • To create the relationship we can't add a foreign key, because will result in repeted data.

    • Instead, create an extra link table between the two relations, which has as its primary key the foreign keys from the two relations.

    • Actor{id_no, name}

    • Film{id_no, title}

    • Cast{actor_no*, film_no*}

    • The foreign keys from the original tables form a compound primary key in the link table.

    • The link table has a 1-many relationship with both the original tables.

    • This method prevents duplications of the relationship - actors could not be entered for the same film twice because the foreign keys are the primary key.

    • A link table can have its own attributes, if it is appropriate for the relationship to have attributes.

    • E.g. the Cast table could have an attribute 'time_spent_on_set'

No comments: