4 ms·
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 t
by sytelus 9y ago
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.
- matte_black 9y ago> 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 9y agoRight. 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.
- sbov 9y ago> 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.
- zo1 9y agoI 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 9y agoI 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);
- always_good 9y agoI 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.
- deleted 9y ago[deleted]
- deleted 9y ago[deleted]
- autokad 9y agoif 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 9y agoPerson.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 9y agoI 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.
- zzzeek 9y ago> 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.