3 ms·

Not bad advice. The one about “where possible avoid simply using id as the primary identifier for the table” stood out to me. In the past with multiple ORMs (

Not bad advice. The one about “where possible avoid simply using id as the primary identifier for the table” stood out to me. In the past with multiple ORMs (ya, ya, we all hate them) the default was to map to a column named id. Also when doing joins its cleaner to use the table_name.id or alias.id then table_name.table_name_id or alias.table_name_id or whatever else besides id is used. The best is when multiple people have worked on the project over the years and the columns are a combo of camel, snake, camel_snake, all UPPER / lower. Must look at the table definitions or ERD every time you want to write some non-trivial query. So having a consistent style guide is better than having any one specific style guide. This would be a good starting point and adjust with your team as needed.

psadri 2y ago
I have found that naming ids as <thing>_id helps downstream code when trying to figure out which thing's id you are dealing with. It also helps with avoiding renaming fields when a structure contains multiple ids. I do agree it makes joins more verbose.
- otteromkram 2y ago
  This isn't a great idea. Maybe it works for you, but you can alias it, too. The main identification column of a table should just be id. Any foreign keys can have a table prefix. Please don't prefix the main table id with the table name.
  - thiht 2y ago
    The fact the "USING" keyword exists would disagree with you. I also use "id" but I would say SQL was designed with the opinion that ids should be prefixed with the table name.
  - psadri 2y ago
    Most of the schemas I have worked with used “id”. I agree it’s the default. But aliasing was inconsistent and made it hard to figure out which table id is being referred to later in the code. I know it is a matter of discipline but any tool that encourages consistency helps. A database schema is definitely one of those tools. One unrelated idea is to include the entity in the id itself. I have never done this but I’d imagine it would help with things like logging / observability. It would not play nice with indices though.
jpnc 2y ago
> Also when doing joins its cleaner to use the table_name.id or alias.id then table_name.table_name_id or alias.table_name_id or whatever else besides id is used However, using 'table_name.table_name_id' and then having another table with an FK that references it with the same name i.e. 'table_2.table_name_id' allows you to use a shorthand 'USING' clause instead of 'ON' in databases that support it.
- Sn0wCoder 2y ago
  Great point thanks for calling USING out. Since you end up putting table_name_id for FKs it totally makes sense to just use that in the main table. Seems I am just so accustomed to having id as the default PK over the years it become habit (my DB professor was an old time IBM-er who preached all tables will have an ID). With auto complete in just about every tool these days and ORM limitations improving will need to update my thinking on this reality. 95% of the time living in the MSSQL world so USING is not something that can even be used (I don’t think).

psadri 2y ago

I have found that naming ids as <thing>_id helps downstream code when trying to figure out which thing's id you are dealing with. It also helps with avoiding renaming fields when a structure contains multiple ids. I do agree it makes joins more verbose.

otteromkram 2y ago
This isn't a great idea. Maybe it works for you, but you can alias it, too. The main identification column of a table should just be id. Any foreign keys can have a table prefix. Please don't prefix the main table id with the table name.
- thiht 2y ago
  The fact the "USING" keyword exists would disagree with you. I also use "id" but I would say SQL was designed with the opinion that ids should be prefixed with the table name.
- psadri 2y ago
  Most of the schemas I have worked with used “id”. I agree it’s the default. But aliasing was inconsistent and made it hard to figure out which table id is being referred to later in the code. I know it is a matter of discipline but any tool that encourages consistency helps. A database schema is definitely one of those tools. One unrelated idea is to include the entity in the id itself. I have never done this but I’d imagine it would help with things like logging / observability. It would not play nice with indices though.

jpnc 2y ago

> Also when doing joins its cleaner to use the table_name.id or alias.id then table_name.table_name_id or alias.table_name_id or whatever else besides id is used However, using 'table_name.table_name_id' and then having another table with an FK that references it with the same name i.e. 'table_2.table_name_id' allows you to use a shorthand 'USING' clause instead of 'ON' in databases that support it.

Sn0wCoder 2y ago
Great point thanks for calling USING out. Since you end up putting table_name_id for FKs it totally makes sense to just use that in the main table. Seems I am just so accustomed to having id as the default PK over the years it become habit (my DB professor was an old time IBM-er who preached all tables will have an ID). With auto complete in just about every tool these days and ORM limitations improving will need to update my thinking on this reality. 95% of the time living in the MSSQL world so USING is not something that can even be used (I don’t think).