How to determine where to place the foreign key in this scenario?

Question

If I have two tables - Logins and Users, as follows:

Logins
 LoginIdNo
 UserIdNo
 HashedPassword 

Users
 UserIdNo
 LoginIdNo
 Username

Each login "has a user" and each user "has a login" but which "belongs to" which?

Is it simply a judgment call or is there a clearcut formula for determining where the foreign key should reside?

Answer 1

Since a Login will never be associated with more than one User (and vice-versa), it is an arbitrary decision of how you define the relationship (and therefore where you place the Foreign Key). Unless you are also going to add other attributes (Firleds) or use Roles, it might be simpler to simply define it like so:

Users 
    UserIdNo 
    Username
    HashedPassword

Answer 2

This is a one-to-one relationship. Where you put the foreign key is probably decided by optionality in those cases.

Is there any particular reason you've split this into two entities? I'm not a huge fan of one-to-one mappings, particularly on ORMs like JPA where they're awkward to implement (if you use the primary key from one as the foreign key to another).

What, in your system, is the difference between a Login and a User?

If Login were to happen each time a user logins in (ie it's an audit trail of user activity) then you have a one-to-many relationship between User and Login and Login should use a UserID foreign key.

But in this case where the username is in one table and the password is in another and there's a one-to-one relationship betwene the two you have to ask why they're separated.