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.
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.