Should I prefer generic names or strongly typed names for the primary keys and foreign keys in a database?

Question

Suppose I have two tables in a parent - child relationship. Let's call them "sites" and "buildings" where a "site" is a parent of one or more "buildings". I want the IDs to be unique across databases so I will be using GUIDs for the IDs.

Should I prefer generic names for the ID fields in the tables or strongly typed names and why?

Example 1 (Generic Names):

CREATE TABLE sites (
   id VARCHAR(38) NOT NULL,
   -- other attributes
);

CREATE TABLE buildings (
   id VARCHAR(38) NOT NULL,
   parent_id VARCHAR(38) NOT NULL,
   -- other attributes
);

Example 2 (Strongly Typed Names):

CREATE TABLE sites (
   site_guid VARCHAR(38) NOT NULL,
   -- other attributes
);

CREATE TABLE buildings (
   building_guid VARCHAR(38) NOT NULL,
   site_guid VARCHAR(38) NOT NULL,
   -- other attributes
);

Answer 1

One convention is to prefix all of the fields in the buildings table with "building_", so you'd have "building_id", "building_name", etc. Foreign keys, such as "site_id", would keep their original name, making it completely obvious what's going on.

edit

I should mention that I chose "_id" over "_uid" or "_guid" because there's only one ID field so I didn't see any reason to emphasize its data type.

Answer 2

I prefer simple id, as this is standard conventions, here is a great link for all database naming conventions: http://weblogs.asp.net/jamauss/pages/DatabaseNamingConventions.aspx#Columns

"Rule 2a (Identity Primary Key Fields) - For fields that are the primary key for a table and uniquely identify each record in the table, the name should simply be “Id“ since, that's what it is - an identification field. This name also maps more closely to a property name like “Id“ in your class libraries. Another benefit of this name is that for joins you will see something like "Customers JOIN Orders ON Customer.Id = Orders.CustomerId“ which allows you to avoid the word “Customer“ again after the Customer table."

Answer 3

It's mainly a matter of taste. There should be some consistency over the project. Most important are:

• that you can remember the names of repetitive columns that you don't need to look into table definitions to type simple sql queries
• that you don't need to look into code or documentation to roughly understand the meaning of a column

Personally I prefer ids with the same name all over the project. It's also easier to write generic code, that is independent of a specific table.

For foreign keys I usually use the <ForeignTable>_FK pattern. If there is more then one foreign key to the same table, I use the role name, as usual in object oriented design, <Role>_FK, for instance CurrentUser_FK.

Answer 4

I prefer to use building_id, site_id so that the column name defines its contents more explicitly than just "id". This also makes it possible to use the ANSI join "using" syntax:

select site.site_id, building.building_id
from building
join site using (site_id);

Another advantage is that when such columns are used in queries (or views or subqueries), they don't need re-aliasing so often - like this:

select site.id as site_id, building.id as building_id
from building
join site on site.id = building.site_id;