Is it more common to use table_id or id in database design-CodePudding

I have a situation where I would like to know if it is more commonplace to use table_id or just id? (in my opinion, using table_ would cause slight confusion as to if it a foreign key). Which do people prefer, and is there really any difference between the two? Or should it just be left up to picking one and being consistent?

CodePudding user response：

There are two main currents in terms of naming columns in tables:

Schema Namespace

This strategy was conceived by teams documenting the "data dictionary" of a database. The idea is that the name itself of the column tells you which table it belongs to across the whole schema or database. For example, CLIENT_NAME would represent the name of the client in the CLIENT table.

There are variations of this strategy where a limited number of letters are assigned as prefixes (specially for M:N relationship tables). For example, the date of purchase of a car by a client could take the form CLI_CAR_DATE, CLICAR_DATE, or even CLCADT.

In your example the "id" column would be named CAR_ID.

Table Namespace

In this strategy column names do not include the name of the entity they belong to, but only their property name. This strategy aligns more with object design, and produces shorter names (i.e. less typing). The name of the table must be indicated when mentioning a column. For example, you would need to say the column NAME on the table CLIENT.

In your example the id column would be named ID.

Summary

I personally like the second one, but there are teams who adhere to both strategies and there's no clear winner. My leaning towards the second one is [I think] the first one suffers from longer names (more typing), longer SQL (more errors), cryptic names (they don't play well with ORMs and app objects), and foreign keys that cannot follow the strategy well.

In fact, virtually all the PKs in my databases are named ID regardless of the entities.

But on the flip side, some teams value very highly the idea of knowing the table name of a column by just looking at it. And this is great for big databases (with 200-1000 relational fact tables) that can become quite complex, specially for new members of a team.

But above all, pick one and be consistent.