FK Naming

Hello,

I have 2 tables: Articles and Users.
These 2 tables are related by AuthorId (FK) in Articles and UserId (PK) in Users.

My question is: should the use the same name for the 2 keys, i.e., UserId?

Or it is normal to use AuthorId in Articles table and UserId in Users table. This makes more sense.

Just a naming question.

Thanks,
Miguel

It's normal to use AuthorID. Actually this would make it more clear (for anyone else looking at your database) that users from the Users table are the authors of these articles.

So, if you ever created for instance a ReaderComment table, you can have a ReaderID FK that is also a reference to the Users table.

Good Luck :)

|||

If you want to make it cleat that this is an Author and is also an FK from the Users table, you can name it something like ' Author_UserID_FK "

|||

Answer is May be or may not be

As per the general norms in paent and child tables if columns names are same there will be no confusion.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Ania:

If you want to make it cleat that this is an Author and is also an FK from the Users table, you can name it something like ' Author_UserID_FK "

It's all about what flavor you prefer. Personally, I try to avoid involving prefixing/suffixing columns for PKs and FKs. I mean, if you were ever curious if it is an FK or not, have a look in the table definition. Making complex column names like this will only make your SQL code harder to read and debug.

My suggestion: Keep names simple and intuitive. Don't use names for expressing relations. The DDL is perfectly capable of doing this already.

(Besides, you have no guarantee that a column named FK is actually an FK...)

|||

I typically name the foreign key like this:

<child_table_name>_<parent_table_name>_fk.

If there are more than one, I name it like this:

<child_table_name>_<parent_table_name>_author_fk.

<child_table_name>_<parent_table_name>_reader_fk.

As for the foreign key column names themselves, I typically use this method:

UserIdAuthor

UserIdReader

It's a bit more typing than AuthorId and ReaderId, but, at a glance, you know both it's fk lineage and its business purpose.

PS. I don't put the fk column name in the fk name because there may be more than one column in the fk!