Column naming conventions..hmm 1

[matthewking]

Hi all,

Im not unfamiliar with sql server 2k but this time I want to make sure I have 'proper' naming conventions in the database. So heres the scenario:

I have a table called 'Users' which lets just say for now has a primary key column called 'UserID_PK'.

I also have a table called 'Properties' which has a pk column called 'PropertyID_PK', now this table needs to store a foreign key to the UserID_PK column in the 'Users' table, it represents the user that added the property, so what do I call it:

UserID
UserID_PK
UserID_FK
PropertyAddedByUserID
PropertyUserID

Any advice, Links? Thanks in advance,

Matthew King.

 

[dan1967]

I think I would use UserID_FK, going by your current naming convention.



Dan.

 

[matthewking]

Thanks for the advice Dan. I've just run into my next problem however, I need to store a UserID_FK for 'Added by' and one for 'Managed By'. Or maybe I should put this stuff in a many to one table relating to the User, I don't know..

 

[dan1967]

I think for this I would use:

AddedID_FK
ManagedID_FK

You can have more than one field hold a value from the same table. When you go to query the database, you would do the following:

SELECT ...
FROM Properties P
JOIN Users A on P.AddedID_FK = A.UserID_PK
JOIN Users M on P.ManagedID_FK = A.UserID_PK

As you can see, I joined to the Users table twice ... the first time I gave it the alias A for Added and the second M for Managed.

Hope this helps,



Dan.

 

[matthewking]

Seems logical, I'll go with that.

Thanks again Dan.

 

[dan1967]

You're welcome. And thanks for the star.


Dan.