Primary Key / Foreign Key Assignment Agreement

Question

In our development team, we have a debate over the naming convention for primary and foreign keys. There are basically two schools of thought in our group:

one:

Primary Table (Employee) Primary Key is called ID Foreign table (Event) Foreign key is called EmployeeID 

or

2:

 Primary Table (Employee) Primary Key is called EmployeeID Foreign table (Event) Foreign key is called EmployeeID 

I prefer not to duplicate the table name in any column (so I prefer option 1 above). Conceptually, this is consistent with many recommended practices in other languages, where you do not use the name of an object in its property names. I think that naming the foreign key EmployeeID (or Employee_ID could be better) tells the reader that this is the Employee table ID column.

Some others prefer option 2, in which you specify a primary key with a table name prefix so that the column name is the same throughout the database. I see this, but now you cannot visually distinguish the primary key from the foreign key.

In addition, I consider it redundant to have the table name in the column name, because if you consider the table as an entity and the column as a property or attribute of this object, you consider this attribute to be an attribute of the Employee , not the EmployeeID employee. I do not ask my colleague what his PersonAge or PersonGender . I ask him what his age is.

So, as I said, this is a heated debate, and we go on and on. I am interested in getting new perspectives.

Answer 1

It does not really matter. I have never come across a system where there is a real difference between choice 1 and choice 2.

Jeff Atwood had a great article on this topic. Basically, people argue and discuss in the most violent way those topics on which they cannot be proved. Or from a different angle, those topics that can only be won by endurance based on styles of styles from the filibuster, based on timeless arguments.

Choose one and tell them to focus on issues that really affect your code.

EDIT: If you want some fun, ask them to detail why their method is superior to recursive table references.

Answer 2

If both columns have the same name in both tables (convention # 2), you can use the USING syntax in SQL to save some typing and some template noise:

 SELECT name, address, amount FROM employees JOIN payroll USING (employee_id) 

Another argument in favor of agreement number 2 is that he developed a relational model method.

The value of each column is partially transmitted by marking its name with the corresponding domain.

Answer 3

I think it depends on how you use the application. If you use ORM or create your own tables to represent objects, then option 1 may be for you.

I like to encode the database as my own layer. I control everything, and the application simply calls stored procedures. It's nice to have result sets with full column names, especially when there are many tables and many columns. With this type of application, I like option 2. I really like to see the coincidence of column names on joins. I worked on old systems where they did not match, and it was a nightmare,

Answer 4

None of the conventions work in all cases, so why are they not at all? Use common sense ...

for example, for a self-regulation table, when there is more than one FK column that self-names the same PK table, you must violate both "standards" because the two FK columns cannot be called the same ... for example, EmployeeTable with EmployeeId PK, SupervisorId FK, MentorId Fk, PartnerId FK, ...

Answer 5

I agree that there is little choice between them. For me, a much more significant thing in any standard is the "standard" part.

If people begin to "do their job," they should be strung with their paws. IMHO :)

Answer 6

If you are looking for application code, not just database queries, it seems to me that some things seem clear to me:

• Table definitions are usually directly mapped to a class that describes a single object, so they should be unique. To describe the collection of an object, I usually add “Array” or “List” or “Collection” to a single name, since it is more clear than using plurals, not only indicates that it is a collection, but also what collection it is. In this view, I see the name of the table, not the name of the collection, but the name of the type of object the collection is part of. A database administrator who does not write application code may skip this point.

• The data I'm dealing with often uses the "ID" for misleading identification purposes. To eliminate confusion between the key "identifiers" and non-key "identifiers", we use the "Key" for the primary key name (this is what it is, right?) With the table name or acronym prefix table name. This prefix (and I leave it for the primary key only) makes the key name unique, which is especially important because we use variable names that match the database column names, and most classes have a parent identified by the parent key name. It is also necessary to make sure that this is not a reserved keyword, which is the "key". To make it easier to keep key variable names consistent and to ensure programs that perform natural joins, foreign keys have the same name as the table in which they are the primary key. More than once I came across programs that work much better using natural associations. In this last paragraph, I admit a problem with the self-regulation tables that I used. In this case, I would make an exception to the foreign key naming rule. For example, I would use ManagerKey as a foreign key in the Employee table to point to another record in this table.

Answer 7

I like agreement # 2 - in researching this topic and searching for this question, before publishing my own, I ran into a problem where:

I select * from a table with a large number of columns and attach it to the second table, which also has a large number of columns. Both tables have the id column as the primary key, and this means that I have to specifically highlight each column (as far as I know) to make these two values ​​unique as a result, that is:

 SELECT table1.id AS parent_id, table2.id AS child_id 

Although using convention # 2 means that I will still have some columns as a result with the same name, now I can specify which identifier I need (parent or child), and, as Stephen Hewig suggested, the USING statement simplifies further.

Answer 8

I always used userId as a PC in one table and userId in another table as FK. “We are seriously thinking about using userIdPK and userIdFK as names to identify one from the other. This will help me quickly identify the PC and FK while browsing the tables, and it looks like it will clear the code when using PHP / SQL to access the data, making it easier to understand Especially when someone else is looking at my code.

Answer 9

The convention that we use when I work is pretty close to A, except that we call the plural tables (for example, "employees") and use underscores between the table and the column name. The advantage of this is that for a column reference it is either "employee _ id" or "employee.id", depending on how you want to access it. If you need to specify which table the column follows from, "employee.employees _ id" is definitely redundant.

Answer 10

I use agreement # 2. Now I work with an outdated data model, where I do not know what is in this table. Where is the harm to be detailed?

Answer 11

How to name a foreign key

ROLE_ID

where the role is the role to which the reference object relates to the table. This solves the problem of a recursive link and multiple fks to the same table.

In many cases, this will be identical to the name of the link table. In this case, it becomes identical to one of your offers.

Anyway havin long arguments are a bad idea

Answer 12

"Where in the" employee INNER JOIN order on order.employee_id = employee.id "is there a need for additional qualifications?".

There is no need for additional qualifications, because the qualifications I spoke about already exist.

"The reason a business user refers to an order identifier or an employee identifier is to provide context, but at the database level you already have context because you are referencing a table."

Pray, tell me, if the column is called "ID", then how does this "refer to [sic] to the table" execute exactly, unless qualifying this link to the identifier column exactly as I said?

Answer 13

Did you consider the following?

 Primary Table (Employee) Primary Key is PK_Employee Foreign table (Event) Foreign key is called FK_Employee