Question on DB relationships

Question

I have an employees table, and I want to track their Operating System preferences. They can have more than one OS preference:

Employee
 - ID
 - Name 
 - OS_Prefs_ID

OSTypes
 - ID
 - OperatingSystemName
 - Version

OS_Prefs
 - ID
 - OSTypes_ID

Is the relationship between Employee and OS_Prefs "one to many" or "many to many"?

I am guessing that Employees (1) to OS_Prefs (many) and for OSTypes and OS_Prefs, it seems that it is OS_Prefs (many) to OSTypes (1). Right?

Answer 1

Many-to-Many

Very simply put an Employee can have many operatingsystems and an operating system can have many employees.

That's why you need to use a third table which contains both ID's

Answer 2

One to one, as I see it.

Make it so:

Employee
 - ID
 - Name 

OSTypes
 - ID
 - OperatingSystemName
 - Version

OS_Prefs
 - EmployeeID
 - OSTypes_ID

Answer 3

I am guessing that Employees (1) to OS_Prefs (many) and for OSTypes and OS_Prefs, it seems that it is OS_Prefs (many) to OSTypes (1). Right?

That is the way it should be, making the relationship from Employee to OSType many-to-many

But for that you need to change your schema a little:

Employee

• ID
• Name

OSTypes

• ID
• OperatingSystemName
• Version

OS_Prefs

• employee_ID
• OSTypes_ID

Answer 4

Is the relationship between Employee and OS_Prefs "one to many" or "many to many"?

I am guessing that Employees (1) to OS_Prefs (many) and for OSTypes and OS_Prefs, it seems that it is OS_Prefs (many) to OSTypes (1). Right?

Yes, correct.

BTW, the relationship you are really trying to model is Employees <-> OSTypes. This is a many-to-many relationship. As this cannot be (nicely) done with two tables, you use a third table (OS_Prefs), to decompose the many-to-many into one one-to-many and one many-to-one. This is a common technique, called a join table .