Which Joined Table to Put Foreign Key (1 Viewer)

AJJJR

Registered User.
Local time
Today, 04:23
Joined
Mar 19, 2018
Messages
56
Hi I'm operating on a pretty basic level with access and have just started using ID fields as foreign keys to join tables. Given the following example:

tblInventory has two columns InvName and InvID

tblSupplier has two columns SupName and SupID

I could join these in two ways:

1) Add a field to tblInventory called SupID and Join it with tblSupplier!SupID
2) Add a field to tblSupplier called InvID and Join it with tblInventory!InvID

I'm wondering what criteria one would use determine which would be the best way?

Thanks in advance for any comments.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:23
Joined
May 21, 2018
Messages
8,463
1. In the first case a single supplier could relate to many inventories (likely)
2. In the second Many suppliers could relate to a single inventory but only one inventory (highly unlikely)
You may also need a junction table

jTbl_Suppilers_Inventories
supplierID_FK
InventoryID_FK

This would allow a single inventory to have more that 1 supplier. And a single supplier can relate to many inventories.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Sep 12, 2006
Messages
15,613
not necessarily either.

what is in tblInventory
what is in tblSupplier

how do you expect to relate these two tables? what are you trying to do

if you store the supplierID in the inventory, then you are really saying that each inventory item can only be supplied by a single supplier.
if you store the inventoryID in the supplier, then you are really saying that each supplier only supplies a single inventory item.

I suspect neither of these is true, so you probably need a junction table to manage the various inventory items supplied by each supplier.

As @MajP also said.
 

AJJJR

Registered User.
Local time
Today, 04:23
Joined
Mar 19, 2018
Messages
56
Hi and thanks for the comments.

My brain was busy digging down a rabbit hole when I was thinking about this and I missed the obvious fact that the type of relationship determines which table contains the Foreign key.

However, what about the case of a one-to-one relationship? e.g.:

You are designing a DB for a boarding school. The boarding school offers each student the same standard, single, room. So you have a table for students and a table for Rooms. Each student has 1 room assigned and each room has one student assigned.

tblStudents tblRooms

StudentID RoomID
StudentName RoomNumber

In this case, which is just an example, is there any way of deciding which table the Foreign Key field should be in. To me it looks like it would make no difference if you added RoomID to tblStudents, or if you added StudentID to tblRooms. I'm just wondering if that's true, and if so, is there any generally accepted convention, or anything else to consider, when determining where to put the foreign key?

Thanks again for the help, I really do appreciate it
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,553
your example is too simple

this year student A is in room 1
next year student B is in room 1 and student A is in room 2

so you have a many to many relationship - dependent on date

so you need a separate joining table

tblRoomStudents
RoomStudentPK
RoomFK
StudentFK
DateFrom
DateTo
 

AJJJR

Registered User.
Local time
Today, 04:23
Joined
Mar 19, 2018
Messages
56
your example is too simple

this year student A is in room 1
next year student B is in room 1 and student A is in room 2

so you have a many to many relationship - dependent on date

so you need a separate joining table

tblRoomStudents
RoomStudentPK
RoomFK
StudentFK
DateFrom
DateTo
First off I just want to make clear that I am not saying anything about my example needing a join table or not not needing one. I'm using a simple example to try and demonstrate my question. I guess what I'm trying to say is --In the event that a one-one relationship exists are there any criteria, including simple user conventions, that would dictate which table to put the foreign key in,
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:23
Joined
May 21, 2018
Messages
8,463
It could be either way, but when you build your DB it will probably seem obvious.

If you are in the Student Administration office you database is probably centered on Students. Your main data is about students and things that relate to a student. So you are going to want to be able to pick a room and give it to your student. You are not going to want to go to the table of rooms and pick a student. So you are likely wanting to pick a room key and store that in your student table.

Now you are a facility management guy. Your database is centered on Room and building information. Your data table has a whole lot of room information. Power source, location, size, number of fixtures..... You are going to want to pick a student and store it in your room table. It would not be natural to pull up the table of students and search for a room.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:23
Joined
May 21, 2018
Messages
8,463
One other thing to keep in mind is flexibility. Your schoold really wants everyone to have their own room, but during renovations some kids might have to get doubled up. Your admin database is good to go since the room foreign key is in the student table. The foreign key is always on the many side so you are able to give 1 room to many students. Student 1 and Student 3 both have roomID_FK of 10.

The facility guy is screwed because they cannot assign a room to 2 people. RoomID of 10 cannot hold two foreign keys. The good news for the Facility guy is Baron Trump's parents paid for him to have 2 rooms. The facility guy can show this on his database since the Student_FK is in the room table allowing for Many rooms to 1 student. Rooms 11 and Rooms 12 both have studentID_FK 9.

So then if you could have both cases. Rich kids get 2 rooms and kids in the renovation dorm are doubled up then you would need the junction table to allow the many to man. 1 room with many students, 1 student with many rooms.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,553
one to one relationships are extremely rare - because you would simply combine both tables. However if they were to exist, you would still have one table with the PK and the other with the FK - but the twist is you would set the FK to not allow duplicates.

tblPrimary
PK......Name
8.........A
35.......B

tblSecondary
PK...FK....OtherName
1......8.......X
2.....35......Y

You could drop the PK in the secondary table and make the FK the primary key - but it would be a long datatype, not an autonumber

as to which table is which, it shouldn't matter.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Sep 12, 2006
Messages
15,613
You never truly need a 1 to 1 relation.

One reason is that there might be a lot of optional settings that are often left blank, so rather than add them to the main table, you hold them in a 1-1 table

Another reason is security. You have an employee table, but some information in that is confidential, so you put the confidential stuff in a 1-1 table, and then you can limit access to user the confidential stuff more easily.

Allocating students and rooms is really not 1 to 1. A student may move out, leave the course, and you want a new student in there. Assuming all rooms are 1-person, you still need a RoomOccupation junction table to store the roomID, studentID, and DateOfOccupation. Then you might want to store contract information, rent payments, and so on - so you might want to store these in the junction table, but you might equally decide you need more tables linked to the RoomOccupation table to store payments, another table to store links to accommodation contracts and so on.
 

AJJJR

Registered User.
Local time
Today, 04:23
Joined
Mar 19, 2018
Messages
56
You never truly need a 1 to 1 relation.

One reason is that there might be a lot of optional settings that are often left blank, so rather than add them to the main table, you hold them in a 1-1 table

Another reason is security. You have an employee table, but some information in that is confidential, so you put the confidential stuff in a 1-1 table, and then you can limit access to user the confidential stuff more easily.

Allocating students and rooms is really not 1 to 1. A student may move out, leave the course, and you want a new student in there. Assuming all rooms are 1-person, you still need a RoomOccupation junction table to store the roomID, studentID, and DateOfOccupation. Then you might want to store contract information, rent payments, and so on - so you might want to store these in the junction table, but you might equally decide you need more tables linked to the RoomOccupation table to store payments, another table to store links to accommodation contracts and so on.
Your first reason for a 1-1 relationship is the reason I asked this question in the first place. I have a situation exactly like that, where there are not only many options, but many types of options. Thanks for the examples
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:23
Joined
May 21, 2018
Messages
8,463
Your first reason for a 1-1 relationship is the reason I asked this question in the first place. I have a situation exactly like that, where there are not only many options, but many types of options.
You may want to give a real example. That sounds like potentially a true one to one. In a true one to one you link from primary key to primary key. That is not similar to the student room example. Also many options and many types of options hints to me as more 1 to many tables not a one to one.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:23
Joined
Feb 19, 2013
Messages
16,553
Your first reason for a 1-1 relationship is the reason I asked this question in the first place.
so what do you see as the potential benefit of splitting a record across two or more tables? How many fields are you talking about?

I would however agree with gemma's second reason
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:23
Joined
Feb 19, 2002
Messages
42,970
When deciding how tables are related, think of parents and children. If I put the ChildID in the parent record, I have to have multiple columns and I have to fix some number. What if the parent has more than the 8 slots I allowed? BUT, if I put the ParentID in the child record, I only ever need one slot (don't take this too literally. I know we have a sire and a dame).
So the "parent" table is the "1-side" and the "1-side" PK goes into the other table as the FK.
 

Users who are viewing this thread

Top Bottom