One to One Relationship question

[ittechguy]

I'm working on a database (I have a few related posts on here, and you guys have been very helpful). Basically its a database which stores customer information, building information, room information, and equipment information.

I've setup my relationships and run quite a few queries/test scenarios. Everything has tested out great. I do have a couple one to one relationships. I was hoping someone here could shed some light on this as far as best practices are. I've read one to one relationships are rarely needed.

One example is I have an equipment table which contains switch information. That is linked to my rooms table because equipment belongs in a room. A switch is in one room however one room can have many switches. So, I created a junction table with a one to many relationship on the rooms side (setting roomID to indexed, duplicated OK), and a one to one relationship on the equipment side (setting equipmentID as indexed, no duplicates).



Is it a good idea to do it this way or is it better to simply have a field for RoomID inside of my equipment table and setup a one to many relationship between rooms and equipment? I personally prefer to have the junction table. I like that my equipment table only contains equipment information and doesn't have any room information.

[ranman256]

Anytime you have 1 thing owning many things, make a 1 to many relationship.
1 person-has many phones.
1 room-has many switches.

this can still be used to inspect the 1 switch.
but sometimes I DO have a 1-1 table. But mostly 1-many.

[JamesDeckert]

A junction table is normally used to create a many to many relationship. One to many is created with two tables only. you can do it the way you're doing, but that is not the norm and you'll be introducing additional complexity into your table structure. If the equipment is in one room only, the only piece of room info in the equip table would be the roomID.

RE: one to one relationship. I have used this, but usually for some specific reason. I'm importing files into a table with additional data which I'm adding to the data from the file. So I have a table that directly mirrors the input file fields and another table that has the additional fields linked one to one. This will make it easier for me to purge or overwrite data in the first table without worrying about the second table info.