Friday, February 21, 2014

Many to many relations in relational database design



Many to many relations are important in relational database design.
For example in publication database, one publication has many authors and one author has  many publications.
In publication table: pub_id is primary key
pub_id, pub_title, jour_name, pub_date, volume, start_page, end_page, pub_date

In author table: author_id is primary key
author_id, prefix,  firstnme, lastname, institute, position

 Bad design 1) in publication table, add author1, author2, author3 columns.
Bad design 2)  in publication table, add  one author  column, which requires 3,4,5 (authors are separated by comma) in input.

The good design is to  create another junction or linking table, i.e.
pub_id, author_id
(both of them are not primary keys)/

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thank you for sharing this knowledge in a blogpost.Really simple and even more effective and this worked great, very useful tips
    sas training in hyderabad

    ReplyDelete

  3. this blog is new and informative , it is really interesting and useful too , thanks for sharing this information.

    sas training in chennai


    ReplyDelete