Which table should a foreign key live on

I'm learning about databases. In my very simple database, I have 2 tables.

WorkingHoursDetail

ID
NameOfWeek
Hours

and

WorkingHours

ID
Name

As you can see, they currently do not relate.

Now, I'm faced with the question of making them relate. I understand I do this by adding a foreign key, something like MyOtherTableID and linking this to the other tables ID.

In the example above, I could add a foreign key to EITHER

WorkingHoursDetail

ID
NameOfWeek
Hours
WorkingHoursId

with

WorkingHours

ID
Name

OR

WorkingHoursDetail

ID
NameOfWeek
Hours

with

WorkingHours

ID
Name
WorkingHoursDetailId

I've tried this, and it seems to work but by having this option frightens me. Is there a 'right' answer or will this change only affect the order in which data can be written to the database. If this is the case, then surely this will dictate the flow of the program using it (the users GUI).

As a rule of thumb, you should add a foreign key on the child table referencing the parent table. In your case it appears that WorkingHoursDetail is the child table and WorkingHours the parent table.

You can identify the parent table by asking which table can exists on its own without the presence of the other table. In your case WorkingHours can exists without WorkingHoursDetails (but not the other way around, so WorkingHours appears to be the parent table.

Alternatively you can look at the data flow when inserting new records. If both tables are empty, you should have to insert a row in WorkingHours first and then a record in WorkingHoursDetail. This makes WorkingHours the parent table and therefore the foreign key should be added on WorkingHoursDetail referencing WorkingHours.

StackExchange Database Administrators Q#123169, answer score: 11