Product.id or product.productId ?

Database structuring

For the product table:

Table: product
Field: Id

But as a foreign key, product_id

Always use snake_case on a db and save yourself a headache.

@C0D4 disagree with snake case. camelCase for the win - ever and always and everywhere.

@tosensei camelcase in programming yes. But not sql

@jonas-w are you saying SQL isn't programming?

Both *madness*

@tosensei most SQL databases are case insensitive by default so camelCase can be meaningless. Therefore its best practice to use snake_case in SQL

@bioDan i doubt this will ever matter, unless you have two different tables that you would call `something` and `someThing` - which would be utterly terrible design, no matter the naming scheme.

so it doesn't really matter that casing doesn't really matter. but in contrast to snake_case or kebap-case, camelCase isn't an eyesore.

that being said, since i'm using entity framework as orm, 1) it doesn't really matter since EF does everything and 2) it's consistent with the programming languages best practices

@tosensei 👀 I'll take that as trolling, or I'll hunt you down and slap you one.

this_awesome_field_thats_a_boolean

Is a lot easier to read then

thisAwesomeFieldThatsABoolean

it's not just the DB engine being case sensitive, but the language using the data set as well.

snake_case is easier to follow when fetching data from a database in a backend language then finding and remembering the bloody case of everything. Only to find out some peasant dba didn't UpperCaseaLetter when they probably should have 🤡

@tosensei its not the problem with the table or columns. Its the consistency of what you query and the results

SELECT numberOfItems
FROM orders

You will may get a table result with the column "NUMBEROFITEMS" or "numberofitems"
(depending on the DDL and DB configuration)

Instead of

SELECT nuMber_Of_IteMs
FROM orders

Where you will get a column name of NUMBER_OF_ITEMS or number_of_items

@tosensei It does make a difference when looking at statistics and query plans since they usually use all caps for table names, snake case is easier to read than wall of text.

@tosensei I've had issues with PostgreSQL forcing me to use quotes for uppercase letters on column names, which means that unless I had a really good ORM, I could not reuse some custom queries coming from another database, so yeah I think snake_case wins out for me

Depends

The industry has pretty much chosen id as the standard.Take a large backend web framework with an integrated ORM, and the default with be "id" as a PK.

The main advantage of thing_id is that SQL has natural joins. If "pets" has a FK "user_id", and the PK on users is ALSO called "user_id", you can do:

select * from users natural join pets

That's a lot more readable & ergonomic than:

select * from users join pets on pets.user_id = users.id

Both make sense. And you may need both in some apps. E.G. Id -- a unique id, taken from a seq. productId - a natural id - smth you may consider sending/showing to the frontend.

So it really depends.

@bittersweet

Or you can use USING.

Join bla USING bla_id

I always use snake case and I forbid using the field name "id".

It makes life much easier when you immediately know to what table an id field relates.

Especially in analysis of query plans and when the database has reached a size / quantity of tables where "finding" the matching table can be very cumbersome.

last but not least - it ensures that an primary key auto increment field name is uniquely identifiable.

It makes stuff like statistics and maintenance easier.

I prefer the more verbose because I find it easier to read if you are joining and aliasing several tables in a large query