Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    18

    combined primary keys

    what is much better design for combined primary keys?

    First Design:
    companycd, branchcd = always combined primary in all linked tables

    ..tblcompany.........tblbranch...........tblclient
    ....companycd.........companycd.........companycd
    ....companyname.....branchcd............branchcd
    ............................branchname........clie ntcd
    .................................................. ..clientname

    Second Design:
    companybranchcd = primary in all linked tables (which is created in tblref)

    ..tblcompany.........tblbranch...........tblref.....................tblclient
    ....companycd.........companycd.........companycd. ............clientcd
    ....companyname.....branchcd............branchcd.. ..............companybranchcd
    ............................branchname........comp anybranchcd....clientname

    I'm worrying about the performance of the two designs. The system can be use by different companies with different branches.

    In first design, the combined primary (companycd, branchcd) is always linked to all tables in order to determine which company and branch as well.

    In second design, the combined primary in first design was referenced to tblref in order to use only one primary (companybranchcd) in all linked tables that can also determine which company and branch as well by referencing to tblref.

    Please share your vast knowledge. Thanks in advance.
    bachie
    student
    php&postgresql
    Shizuoka-ken, Japan

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the companybranchcd surrogate key does not need to be in a separate tblref table, it can be declared right in the tblbranch table

    (by the way, you should learn to exclude the "tbl" prefix from your table names)

    which is better? it's your choice

    generally speaking, it all depends on the complexity of your application code

    for example, if you get a client record in design two, you must do a join just to find out which company it belongs to, and this is not necessary if you're using natural keys

    in addition, you have also to consider what you will do if you have clients that don't belong to a branch, just a company -- i.e. what will you do if you get a company that doesn't have branches? will you "invent" a "phony" branch just to fit the company into your design?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2005
    Posts
    18
    in addition, you have also to consider what you will do if you have clients that don't belong to a branch, just a company -- i.e. what will you do if you get a company that doesn't have branches? will you "invent" a "phony" branch just to fit the company into your design?
    i added a field called type (headoffice or branch) in tblbranch. On that case, there is a company that has one branch (headoffice).

    Thank you very much.
    bachie
    student
    php&postgresql
    Shizuoka-ken, Japan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •