DEV Community

Discussion on: Database, Table and Column naming conventions

Collapse
aarone4 profile image
Aaron Reese

My preferences from 30 years of SQL development.
Singular table names which match your class names.
Primary key is table.id and is int autoincrement. UUID is not only more storage but also slows down writes as you are not adding records to the end of the cluster but have to keep page tearing to make space in the leaf. If you want uniqueness across tables then most RDBMS have solutions. In MS-SQL this is SEQUENCE.
Foreign key is table.relatedTable_id
Putting createdAt etc at the end of the table can cause issues. If you need to add columns they will no longer be at the end. Better to actually put them immediately after the primary key fields. Also use a rownumber/rowstamp/timestamp field, especially if you are updating records using REST protocols. You can check if the payload rowstamp matches the database. If not something else has updated the record since you pulled it and your data is stale.
camelCase fields are good but can cause challenges when the standard entity is known by its acronym. CSSElement.