[sytelus]

Lot of these is debatable. For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX. Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I have used both approaches with its own pro and cons.

[zzzeek]

> For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX.

those tools are wrong (and I know roughly which ones those are).

> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming.

it would be: person.id and the foreign key column that refers to it person_thing.person_id. This is much preferable to person.person_id and person_thing.person_person_id.

[sbov]

> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming.

I assume you mean you just use PersonID as the foreign key. This oftentimes introduces ambiguity into what the relationship actually is. I prefer names that describe the actual relationship (e.g. author, owner, approver, etc) rather than letting other people guess what it is.

[always_good]

I usually want to reserve the noun like "author" as the embedded record after a join. That way "author_id" is always the key and then "author" is the json_agg joined object that embeds the whole record.

Otherwise you're actually introducing ambiguity imo.

[zo1]

I think he's referring to using "[Table]ID" as the primary key on [Table]?

So now you have to join with the Person table on Person.PersonID from your local column PersonID. I much prefer the other way around: Table.ID with foreign keys being "TableID".

[slobotron]

I find "[Table]ID" to be bit more readable in joins. I.e.

    SELECT * FROM table1 INNER JOIN table2 USING (table1_id);

vs

    SELECT * FROM table1 INNER JOIN table2 ON (table2.table1_id = table1.id);

[matte_black]

> For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql

Do not do this in Postgres, it will be a pain in the ass since you will have to use quotes around everything.

[cryptonector]

Right. PG has appropriated square brackets for array notations, so you really just have to use double-quotes.

At least PG tries really hard to not add new reserved keywords, which means you mostly don't have to worry about your schema element names possibly conflicting with new keywords in future releases.

[autokad]

if you need “[First Name]” so it shows up in an UI, you could always do first_name as 'First Name'. But I would say that's still general bad practice. In many cases, you shouldn't be exposing your column names through a UI, and most UI allow for alias

[arez]

Person.PersonID stutters, you already know that you query on the person table you don't have to repeat it again. Naming it ID is the same consistency

[default-kramer]

I like "Person.PersonID" because then "alias.PersonID" will produce an error if "alias" does not have a "PersonID" column. If every table has an "ID" column then "alias.ID" pretty much never fails, even if you typed the wrong alias - you just end up joining on the wrong thing and getting the wrong result set.