Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2014

    Unanswered: composite primary key

    Hi Guys,

    I am new to Database and Postgresql.

    I created an ERD diagram to store information on students, parents, teachers and schools, part of my design instead of having address for each entity I create a new entity called address and contains a composite primary key from (studentID, partentID, techerID, schoolID), streetName, suburb, postcode and state:

    • Student can have one or more Address
    • Parent can have one or more Address
    • Teacher can have one or more Address
    • School can have one or more Address

    I used this method avoid the redundancy for address, and I created the tables for each (student, parent, teacher, school, address) then I populated the tables with values using “INSERT VALUE” statement, but I have problem now when I am inserting values for address because I have enter the (studentID, partentID, techerID, schoolID) for each row and the issue is say for instance a studentID and prentID may have the address but for the teacher and school may have different address.

    I designed the database to have a separate entity for address and with composite primary key from (studentID, partentID, techerID, schoolID), but after populating the values for the address table how do I know which address belongs to whom, because I have to have 4 IDs from the (studentID, partentID, techerID, schoolID), I am really confuse.

    Thank you in advance.

  2. #2
    Join Date
    Oct 2014
    Hi and welcome!

    You probably should start by reading a book on database design, like Elmasri & Navathe's "Fundamentals of database design".

    With the details you've provided, I though you could approach the situation with these kind of relations:

    STUDENT(id, ...)
    TEACHER(id, ...)
    PARENT(id, ...)
    SCHOOL(id, ...)
    ADDRESS(city, street, state, zip, suburb, id)

    Of course it's not considered a good practice to give the attributes same names but you can now see how to map foreign keys. You should create a constraint between ADDRESS and the other relations using "id". You can choose the minimal set of attributes within ADDRESS for its primary key. The minimal set need to be selected so that it's unique every row - I'm not sure what that set would be in the US since I'm not familiar whether or not e.g. there are same street names within a state etc. Do not include the "id" in ADDRESS' primary key, but you can also create a supplementing attribute within that relation to act as a primary key.

Posting Permissions

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