Conceptual Database Design Question (long Question)

[SamMaynard]

There could be many answers to this question and I would sure appreciate anyone's input. I realize it will take minute to read and answer. I am fairly new to database design and haven't been able to answer my question by reading.

I'm creating a database to track college student records over time. I track when they Graduated, GPA, Starting Salary, Company Name, Major/Minor ContactPerson (like mom or dads name and phone number incase we need to reach them in the future) and so forth. I have a table called StudentInfo. Its primary key is StudentID. I have other supplementary tables such as Company, Majors, Contacts, and so forth. I validate my StudentInfo table from these supplementary tables. (They are also related with referential integrity enforced)

Ok, here is the problem...What if a student goes to work for two companies. Or what if I want to enter more that one contact name or what if I want to add 20 or 30 contact names. How can I do this with out hard coding lots of field into my tables?

The best I have come up with is a many to many relationship between the different tables. For example I could have a junction table called StudentDetails with one to one relationships enforced to the other tables like Company, Majors, Contacts. This way I could have as many companies or contacts as I want but what if the student didn't find a job and therefore no value goes into the company field.
In this case I could have an option for the user to input 'No Company' or something but it seem there should be a better way of achieving what I'm trying to do.
Now this was long. Thanks for reading all of it. I would appreciate your thoughts. Any design help be appreciated. I want this database as normalized as possible. Thanks, Sam

 

[clares]

I think I have got the gest of what you are asking. Aviod at all costs N:M relationships, access does not support them.

In the example of the student and the company you would set up two tables, one stores the student details, unique ID for student. The second table, Company would also perhaps have a company ID,with a foriegn key, for student ID. This was one student may have many employees,each record being unique. Does this explain the solution?

 

[clares]

The other way is to have three tables,one for student details, one for Company details and the last, StudentCompany where you store the unique ID for both the student and the company. The database will be fully normalised as well. You can use queries to pull the data that you want etc.

 

[clares]

The other way is to have three tables,one for student details, one for Company details and the last, StudentCompany where you store the unique ID for both the student and the company. The database will be fully normalised as well. You can use queries to pull the data that you want etc.

 

[SamMaynard]

Thanks for the post. I believe the second option fits the best for my senario. A third table will serve as kind of a junction table. Right? Thanks so much. Sam

 

[clares]

Thats right, any subsequent tables will need to be built in the same way and always avoid n:m relationships.