Simple 1 to 1 Question

[TamedTech]

Hello Chaps,

Bit of a simple one here, but thought I would ask it just to get a little clarification on how 'best' to do this. Say I have two tables which are meant to have a 1 to 1 relatioship, which table should carry the FK referance? For instance, say I have a user, and that you has a profile.

User
-----
User_ID
Created
Updated
Email
Telephone

Profile
-------
Profile_ID
FirstName
MiddleName
LastName
ChartColour
MaritalStatus
ImagePath

Which one of those tables should have the Fk column? should the profile have a User_ID column? or should the user have a Profile_ID column?

I know that it could be worked iether way in a query so logicaly I cant see any reason for one choice over the other, but thought i'd come here and ask your thoughts and explanations.

Thanks,

Rob

 

[LNBruno]

I don't believe it really matters in this case. I would point out, though, that you already have the logical answer in hand in that you refer to the two tables in a specific order: you reference "user" first and then "profile." Since "user" would appear to be at a higher level (from a hierarchial point of view), I would lean toward putting the foreign key in the profile table.

If that seems like over-analyzing the situation, you could always fall back on one of my personal favorites: flip a coin! ;-)

< M!ke >
"Believe, but verify.

 

[LNBruno]

Then again, why have two tables?

< M!ke >
"Believe, but verify.

 

[TamedTech]

Thanks LNBruno,

There isnt really a -need- for 2 tables per se, but My users also have a lot of other information attached to thier account, like credentials, application settings, contact details and a whole manner of other things.

If I were to consolidate them all into a single table then it would start to become quite fat, which from reading past posts is liable to start hitting on my performance. I did start a thread about 2 weeks ago on the subject and it got mixed reception but I think in the end everyone came to the agreement that it was a good idea to split them up, especialy if they are in to logical chunks that mirror the objects in your application.

Thanks for your input on the FK stuff though, its greatly appreciated.

Rob

 

[SQLSister]

Use that table withthe information that is the main information as the primary table and the table withthe less likely to be queried as the foreign key. Likey you have the important stuff like name, addres in one table and the less critical stuff inthe other. Afterall you wouldn;t create the table withthe credentials etc, until the record with the name was already inthe other table would you?

Questions about posting. See faq183-874

 

[FlaBusInt]

Taking SQLSister's reply one step further, it really does depend on how the data is loaded. The table containing the data loaded first needs to be your primary table; it should be FK'd to the table with data loaded after. Otherwise, you could get a key violation.

 

[TamedTech]

Hello Guys,

Thanks a great deal for your advice on this. The 'User' is always going to be the first thing that is created, so I'll be sure to make that my primary table, and then FK to the others.

Thanks again,

Rob