Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    18

    Choosing composite keys

    I'm having a bit of a difficult time choosing composite keys on databases.I know that to choose primary keys I have to follow some rules like: it cannot contain null values,avoiding fields that can be changed over time(ex: phone number)., but how do I know which attributes go best for a composite key?For example:

    An agency has a number of account(sequential) for each account created,and those account numbers are unique.But in other agencies different accounts can have the same number of account.Each client can have many accounts in a same bank agency and an account can be shared by more than one client.
    There would be a roll of atributes like: number of account,code of agency,type of account,name,address,money on account,phone number,birth date.

    I would say that the primary key would be composed of number of account,agency code and name of the person, but I'm not sure.Is there any specific rule to follow in order to determine the composite primary key of a database?
    Thanks in advance for the reply!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if your PK is (acctno, agencycd, clientname), then you are saying that the same account number can be used by the same client at a different agency

    while that would be unusual, it is actually okay

    it is also possible that the same account number can be used by different clients at the same agency

    which you say is allowed, and of course it does make sense

    it is also possible that the same account number can be used by different clients at a different agency

    that sounds scary but it's actually normal

    my rule of thumb: try saying it, and ask yourself if it makes sense

    in your case, it does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    18

    ...

    So,in my example the primary key composed of that attributes is ok?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    looks okay to me

    let me know what mark you get on the assignment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2006
    Posts
    18
    Hehe, it's not an assignment...I'm having an exam Monday and I'm studying for that exam, solving exercises ,and I have a yet more difficult exercise to solve which is about the administration of medication in a clinic:

    "When a patient (npatient;name_patient;date_birth;address;contact_ patient;numberid;health_system) is interned,there's a set of medication (code_med;name_medication;active-chemical;cod_lab;name_lab) that is prescribed daily by his doctor(ndoctor;name_doctor;contact_doctor) depending on the diseases that are diagnosed (cod_disease;name_disease) and we want to register.A doctor has a special skill(code_skill;name_skill). It's essencial to store the diary dosage of each medicine that each pacient ingests.The diary prescribed medicines for the diseases that were detected by the doctor are administrated by the nurses(nurse_code;name_nurse;contact_nurse).Daily, the administration of the medicines can be done by more than one nurse.Finaly we want to register the state of evolution of the disease of each patient and for the doctos and nurses to consult the description of the effects and main counter-indications of the medicines for each disease."

    The primary key I have for this exercise consists on the following attributes:
    npatient;code_medicine;code_lab;code_disease;code_ skill;code_nurse;code_doctor.
    I'm not sure if those are correct(I think they are but I might be missing one or two attributes for the primary key) so any help is deeply appreciated!
    Thanks in advance and thanks to r937 for the reply!

  6. #6
    Join Date
    Sep 2006
    Posts
    18

    ...

    Now that I look at it,in the text it says that a doctor has only one main special skill,so i think it's not necessary to use that attribute as part of the primary key,I guess...

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    A primary composite key (or "natural key") should contain the minimum columns necessary to uniquely identify a record.

    That's all, folks.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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