Unanswered: Linking ID from one table to data in another table
I have an assignment where I am to create two tables within a database. One of the tables have the name ContactPerson with the attributes; ID, Forename, Surname, Email, PhoneNumber. The other table is called Company and has the attributes: ID, CompanyName.
Now my problem is that I have to link a ContactPerson to a specific company, but I can't have them in the same table.
I understand that I can use the join statement to show both tables in one query but I need the database to know which person is linked to which company when I implement this databse into my asp.net project.
You have a clear description of your system.
You have Companies, that will be stored in the Company table and you have People, stored in the Person table. And you have a relation between those two with the label ContactPerson.
You can now start to think about how to implement this relation in your data model.
- How many Contactpersons can one Company have?
- For how many Companies can a Person be the Contactperson?
If a Company can only have one Contactperson, where would you store the FK? in the Contactpersons or Company table?
Same question if a Person can only be the Contactperson of one Company. (one-to-many relations)
When a Company can have only one Contactperson at a time and a Person can be the Contactperson of only one Companie, where would you store the FK? (one-to-one relation)
When a Company can have many Contactpersons and a Person can be the Contactperson of many Companies, you need an extra table to store those relations.(many-to-many relation)
Later you will use the Foreign key (FK) - Primary Key (PK) relation to JOIN the Company with it's Contactperson(s).
We don't do homework on this forum. But if you can show what you have came up with yourself, we will help you further.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages