9 ms·

How I Write SQL, Part 1: Naming Conventions (2014)

164 points by sehrope 8y ago

sytelus 8y 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 8y 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 8y ago
    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.
- sbov 8y 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 8y ago
    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 8y ago
      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);
  - always_good 8y ago
    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.
    - deleted 8y ago
      [deleted]
    - deleted 8y ago
      [deleted]
- autokad 8y ago
  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 8y ago
  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 8y ago
    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.
- zzzeek 8y 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.
barrkel 8y ago
You should follow the conventions that make life easier in the rest of your tooling. The fact is, you're probably going to be issuing more SQL via abstractions like ORMs or querying libraries than raw SQL. If you need to work against the grain of those libraries to map your model, what upside are you getting? If most of your data is queried via ActiveRecord, for example, you should use plural table names.
- walshemj 8y ago
  Don't use an ORM is the answer just put in the effort to lean SQL
  - matthewmacleod 8y ago
    That is a weird middlebrow dismissal of a response with essentially no value to anybody. Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate. This is much better advice.
    - 11235813213455 8y ago
      it's better only if you replace "appropriate" with more insights
    - hluska 8y ago
      I'm north of 40 now, so while I both love and agree with your advice, I'd like to add a caveat. Even if you are lucky enough to be able to use an ORM for your entire career, you will still be well served by learning and truly understanding SQL. In that sense, I'd argue that an ORM is only appropriate if your understanding of SQL and database design are strong enough that you can understand what's happening under the hood if/when everything goes to shit!! :)
      - nurettin 8y ago
        Solving problems with Postgres is wildly different than solving them with mssql informix or Oracle. So I would say "there is no SQL".
walshemj 8y ago
How come I have never in a multi decade career come across "i18n". I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables. Also never really brought into adding the type as part of a name - your type is already defined in your schema.
- cryptonector 8y ago
  Really? I have. In some circles (e.g., the IETF), i18n is an ancient acronym. People who've worked on operating systems (e.g., OS X, Solaris, RHEL, whatever) have to deal with L10N (localization). G11N (globalization) is I18N + L10N. And then there's a11y: accessibility. This is all about making user interfaces accessible for people with low or no vision, low or no hearing, difficulty typing, and so on. There are generally applicable laws requiring G11N and A11Y, and these fall heavily on OS vendors, which is why people who've worked on OSes tend to know these acronyms. I18N -> dealing with Unicode in general, codeset conversions, font issues, ... L10N -> dealing with translating system/application messages to the users' preferred languages (and how to even know they preferences) (think locales) G11N -> I18N and L10N. Localization is damned difficult. There's all sort of little bothersome things, like how to format numbers (which varies quite a lot) and dates (can't we all just use ISO-8601?!). And translating printf-like format strings is often non-trivial, especially when the coder doesn't stop to think about just how hard they might be to a translator as they write their code.
  - kbenson 8y ago
    > G11N -> I18N and L10N. That's a new one to me, but makes sense. I've been lucky enough to have heard of i19n and l10n for years (almost decades, and this point) but not had to deal with it much beyond tracking down a string in some open source webapp I was patching before deploying. > can't we all just use ISO-8601?! Preach on. I sometimes find myself filling out date fields in paper forms in YYYY-MM-DD without thinking. The elementary school my kids attend probably thinks I'm a weirdo. I know my wife does...
  - dvh 8y ago
    V10N --> Velociraptor T15X --> Tyrannosaurus Rex D11S --> Dilophosaurus B11S --> Brachiosaurus T9S --> Triceratops S9S --> Stegosaurus
meritt 8y ago
I personally prefer person_id to be the primary key name (instead of id) in both the person table and any table which has it as a foreign key. One reason is for join syntax: select * from person join team_member using (person_id) The other reason is person_id now unambiguously refers to the same field regardless if we're looking at the PK or a FK. It's always person_id.
- jermaustin1 8y ago
  I use T-SQL, and this is actually why I always join with the table name: SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id
  - mtone 8y ago
    I'd bring it a little further and would write: SELECT * FROM Person as P INNER JOIN TeamMember as TM on TM.PersonId = P.Id I have: - Aliased each table and prefixed every field names with their table alias in my join conditions. - Explicited the JOIN type. The above: - Reduces mistakes due to ambiguities that tend to generate unwanted duplicates rows in SQL. - Increases the likelihood of getting an error at parse time, instead of run-time or analysis-time, thanks to added scoping. - Works in any schema, no matter what naming conventions are followed. - Keeps working as the query becomes more complex with multiples table aliases or self-joins, and similar field names appearing in the set. - Better expresses intent. Sure JOIN defaults to INNER JOIN, but writing "INNER JOIN" shows that you genuinely expect any row not matching your condition to be removed from the result set.
    - jermaustin1 8y ago
      I'm not a fan of short aliases. They obscure what you are attempting to do. Personally I write very little SQL anymore (the ORM does that for me unless I need performance), but the only time I use aliasing is when the same table is joined multiple times. Like: SELECT Mother.Name, Father.Name, Child.* FROM Person Child JOIN Person Mother ON Mother.Id = Child.MotherId JOIN Person Father ON Father.Id = Child.FatherId That said, this naming convention also obscures what Person.FatherId points to without looking at the FKeys. So Take that with a grain of salt.
daigoba66 8y ago
Also important is adapting to the existing naming conventions of the database, even if you don't like it. (Unless the existing naming conventions cause more trouble that its worth like requiring quoted identifiers or redundant prefixes/suffixes).
- ysleepy 8y ago
  I agree, consistency is worth a lot more than using a slightly better convention. It would also create surprises in the sense of "principle of least surprise" which is in general a good guideline while designing APIs schemas and so on.
- gwbas1c 8y ago
  Probably the worst thing I've encountered is a junior engineer trying to encourage me to change a coding style by having two coding styles coexist. I just kept repeating, over and over, that I expected the coding-style to be consistent. It was totally over his head, and he totally didn't even bother looking to find a code formatting utility to do a One-Shot style change.
youpassbutter 8y ago
No. That's how you write sql within your organization. Also there are syntactic differences between SQL flavors ( postgres, mysql, mssql, oracle, etc ) that make a SQL standard unrealistic. The only generic rule is "be consistent". Whatever convention/style you choose, it should be consistent.
iblaine 8y ago
> Avoid reserved words Glad you cleared this up for the rest of us. FWIW, naming conventions are like opinions. Everyone has them, and they usually differ from person to person. The best naming convention is a consistent naming convention. Also, naming conventions differ greatly by environment. A group of SQL Server engineers are going to have different standards than those of people working on mysql.
0xffff2 8y ago
Without a rigorous attempt at justifying each of these rules, I don't find this article particularly useful. For example, can someone link to or provide a formal explanation for why table names should be singular? I actually really wanted to read the full relational algebra rational for that one.
- davvolun 8y ago
  I've been through the relational algebra, I found this to be a nice, quick re-cover. More like a checklist than a full inspection.
- zzzeek 8y ago
  I'm sure this SO answer will satisfy you: https://stackoverflow.com/a/4703155/34549 https://stackoverflow.com/a/4703155/34549
  - kbenson 8y ago
    That answer satisfied me initially, but less and less so the more I read. It became very obvious by the end that what is being represented is one "standard", but presented as the only possible correct solution. It starts off with Yes. Beware of the heathens. Plural in the table names are a sure sign of someone who has not read any of the standard materials and has no knowledge of database theory. I thought the author was being flippant, but it became increasingly obvious that this is a true reflection of their dogmatic view with regard to this topic. Even if this is the same view I would settle on with all the knowledge, being presented with what is obviously a single perspective with no acknowledgement whatsoever of any positive aspects of alternatives causes me to instinctively distrust quite a bit of the reasoning presented.
    - nickpeterson 8y ago
      performancedba answers ... the sound and the fury.
- cwbrandsma 8y ago
  My own view is they should either be all plural or all singular. Just pick one. But I do gravitate to singular because all nouns have naturally consistent singular words. The same cannot be said of plural. e.g. Moose, Cactus, and any other word that ends in an 's'.
daphneokeefe 8y ago
For naming stored procedures, there were a lot of helpful answers to my question "What is your naming convention for stored procedures?" on StackOverflow a few years ago. https://stackoverflow.com/questions/238267/what-is-your-naming-convention-for-stored-procedures https://stackoverflow.com/questions/238267/what-is-your-nami...
elchief 8y ago
Joe Celko, as well as ISO-11179, tell us to use collective names ("personnel") or plural names ("employees") for tables As well, fewer keywords are plural, compared to singular, so there's less chance of accidentally using a keyword if you use plurals Haven't yet seen an "octopus" table in production...
- danso 8y ago
  I did a Google search for elaboration on this and apparently there is disagreement that ISO-11179 says this at all: https://social.msdn.microsoft.com/Forums/vstudio/en-US/d5f2faf3-3c85-413d-bc09-ce1d477b31e8/iso-111795-and-table-names?forum=transactsql https://social.msdn.microsoft.com/Forums/vstudio/en-US/d5f2f... > Yes, this is the same version as I found, but the closest thing I could find to addressing table names in the paper itself was an "Object Class name", something like an OOP Class or something you'd find in a UML diagram, but not really the same as a table name, and in any case all the examples were singular. > Was actually kinda hoping Celko would deign to comment on this himself as he seems to be the chief proponent of the "collective identifiers as specified by ISO 11179" meme.
  - elchief 8y ago
    "To remind users that tables are sets of entities, ISO-11179 Standard likes to use collective or plural nouns that describe the set of those entities for the names of tables. Thus 'Employee' is a bad name because it is singular" Page 10, SQL For Smarties (Celko), 5th Ed If Celko says it's right, it's right
    - danso 8y ago
      OK, but the ISO-11179 Standard doesn't seem to actually say that. Here's a purported copy of Part 5 -- Naming and identification principles -- of the standard (as linked to from the aforementioned MSDN forum thread: https://www.ftb.ca.gov/aboutFTB/Projects/ITSP/Part_5_Naming_Principals.pdf https://www.ftb.ca.gov/aboutFTB/Projects/ITSP/Part_5_Naming_... The word "collective" doesn't show up in the document. The word "plural" shows up twice, both times in item #a of "Lexical rules": > a) Nouns are used in singular form only. Verbs (if any) are in the present tense. > NOTE In Japanese, this rule shall not be applied because of no plural form of nouns and no distinction of verb tense. The only reference on Wikipedia to the ISO 11179 standard making that recommendation has since been deleted for being unsubstantiated: https://en.wikipedia.org/w/index.php?title=Data_element_name&type=revision&diff=677768128&oldid=544105362 https://en.wikipedia.org/w/index.php?title=Data_element_name... edit: The apparently inaccurate content is talked about on the "Data element name" Talk page: https://en.wikipedia.org/wiki/Talk:Data_element_name https://en.wikipedia.org/wiki/Talk:Data_element_name
jcadam 8y ago
I know a reasonable amount of SQL but am by no means a database guru. Naturally, this makes me the local database expert at my current place of employment :/ The other devs' eyes glaze over when I say things like 'stored procedures' and 'trigger functions.' Bah.
wvenable 8y ago
I don't understand this one: > Mixed case identifier names means that every usage of the identifier will need to be quoted in double quotes I've used quite a few RDBMS engines, including most mentioned by the author, and I've never had to quote mixed-case identifier names. They work just the same as all lower-cased names or as any other case-sensitive language. Most of the programming languages I use typically have the convention of using PascalCase for classes and public fields/properties so I prefer to use that convention for tables and columns (and then everything else for consistency). When doing operations between the application and the database, the name is exactly the same without the need for translation. Otherwise, I think it's a good list.
- wfriesen 8y ago
  In Oracle this is kind of hidden away since it is case sensitive, but unquoted identifiers are silently converted to uppercase, quoted identifiers are used as-is. So, for queries against something like create table foo ( Bar integer, "Foobar" integer ); Referring to Bar, BAR, bar, and "Foobar" will work, but foobar and Foobar will not.
- stubish 8y ago
  If you are not quoting your mixed-case identifiers, then they are not mixed-case. They are being implicitly converted to either lowercase or uppercase depending on your database, and your capitalization is lost. It normally doesn't matter, until you are introspecting your database schema, at which point your code generation generates a Organizationrole protobuf message instead of an OrganizationRole, or your ORM fails to find OrganizationRole because PostgreSQL stored it as organizationrole.
  - wvenable 8y ago
    This seems like a terrible feature of Postgres and Oracle (which apparently works the same but oppositely). All other database engines I've used have retained the case of unquoted identifiers. I haven't used Postgres enough to notice this, it's almost a deal breaker. I might be tempted to mandate that all identifiers be quoted than deal with half the possible characters for names. Although more likely all code-generation would happen on the application side with DB migrations so the database wouldn't the source of truth for identifier names, anyway.

164 points by sehrope 8y 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 8y 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 8y ago
  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.
sbov 8y 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 8y ago
  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 8y ago
    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);
- always_good 8y ago
  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.
  - deleted 8y ago
    [deleted]
  - deleted 8y ago
    [deleted]
autokad 8y ago
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 8y ago
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 8y ago
  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.
zzzeek 8y 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.

barrkel 8y ago

You should follow the conventions that make life easier in the rest of your tooling. The fact is, you're probably going to be issuing more SQL via abstractions like ORMs or querying libraries than raw SQL. If you need to work against the grain of those libraries to map your model, what upside are you getting? If most of your data is queried via ActiveRecord, for example, you should use plural table names.

walshemj 8y ago
Don't use an ORM is the answer just put in the effort to lean SQL
- matthewmacleod 8y ago
  That is a weird middlebrow dismissal of a response with essentially no value to anybody. Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate. This is much better advice.
  - 11235813213455 8y ago
    it's better only if you replace "appropriate" with more insights
  - hluska 8y ago
    I'm north of 40 now, so while I both love and agree with your advice, I'd like to add a caveat. Even if you are lucky enough to be able to use an ORM for your entire career, you will still be well served by learning and truly understanding SQL. In that sense, I'd argue that an ORM is only appropriate if your understanding of SQL and database design are strong enough that you can understand what's happening under the hood if/when everything goes to shit!! :)
    - nurettin 8y ago
      Solving problems with Postgres is wildly different than solving them with mssql informix or Oracle. So I would say "there is no SQL".

walshemj 8y ago

How come I have never in a multi decade career come across "i18n". I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables. Also never really brought into adding the type as part of a name - your type is already defined in your schema.

cryptonector 8y ago
Really? I have. In some circles (e.g., the IETF), i18n is an ancient acronym. People who've worked on operating systems (e.g., OS X, Solaris, RHEL, whatever) have to deal with L10N (localization). G11N (globalization) is I18N + L10N. And then there's a11y: accessibility. This is all about making user interfaces accessible for people with low or no vision, low or no hearing, difficulty typing, and so on. There are generally applicable laws requiring G11N and A11Y, and these fall heavily on OS vendors, which is why people who've worked on OSes tend to know these acronyms. I18N -> dealing with Unicode in general, codeset conversions, font issues, ... L10N -> dealing with translating system/application messages to the users' preferred languages (and how to even know they preferences) (think locales) G11N -> I18N and L10N. Localization is damned difficult. There's all sort of little bothersome things, like how to format numbers (which varies quite a lot) and dates (can't we all just use ISO-8601?!). And translating printf-like format strings is often non-trivial, especially when the coder doesn't stop to think about just how hard they might be to a translator as they write their code.
- kbenson 8y ago
  > G11N -> I18N and L10N. That's a new one to me, but makes sense. I've been lucky enough to have heard of i19n and l10n for years (almost decades, and this point) but not had to deal with it much beyond tracking down a string in some open source webapp I was patching before deploying. > can't we all just use ISO-8601?! Preach on. I sometimes find myself filling out date fields in paper forms in YYYY-MM-DD without thinking. The elementary school my kids attend probably thinks I'm a weirdo. I know my wife does...
- dvh 8y ago
  V10N --> Velociraptor T15X --> Tyrannosaurus Rex D11S --> Dilophosaurus B11S --> Brachiosaurus T9S --> Triceratops S9S --> Stegosaurus

meritt 8y ago

I personally prefer person_id to be the primary key name (instead of id) in both the person table and any table which has it as a foreign key. One reason is for join syntax: select * from person join team_member using (person_id) The other reason is person_id now unambiguously refers to the same field regardless if we're looking at the PK or a FK. It's always person_id.

jermaustin1 8y ago
I use T-SQL, and this is actually why I always join with the table name: SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id
- mtone 8y ago
  I'd bring it a little further and would write: SELECT * FROM Person as P INNER JOIN TeamMember as TM on TM.PersonId = P.Id I have: - Aliased each table and prefixed every field names with their table alias in my join conditions. - Explicited the JOIN type. The above: - Reduces mistakes due to ambiguities that tend to generate unwanted duplicates rows in SQL. - Increases the likelihood of getting an error at parse time, instead of run-time or analysis-time, thanks to added scoping. - Works in any schema, no matter what naming conventions are followed. - Keeps working as the query becomes more complex with multiples table aliases or self-joins, and similar field names appearing in the set. - Better expresses intent. Sure JOIN defaults to INNER JOIN, but writing "INNER JOIN" shows that you genuinely expect any row not matching your condition to be removed from the result set.
  - jermaustin1 8y ago
    I'm not a fan of short aliases. They obscure what you are attempting to do. Personally I write very little SQL anymore (the ORM does that for me unless I need performance), but the only time I use aliasing is when the same table is joined multiple times. Like: SELECT Mother.Name, Father.Name, Child.* FROM Person Child JOIN Person Mother ON Mother.Id = Child.MotherId JOIN Person Father ON Father.Id = Child.FatherId That said, this naming convention also obscures what Person.FatherId points to without looking at the FKeys. So Take that with a grain of salt.

daigoba66 8y ago

Also important is adapting to the existing naming conventions of the database, even if you don't like it. (Unless the existing naming conventions cause more trouble that its worth like requiring quoted identifiers or redundant prefixes/suffixes).

ysleepy 8y ago
I agree, consistency is worth a lot more than using a slightly better convention. It would also create surprises in the sense of "principle of least surprise" which is in general a good guideline while designing APIs schemas and so on.
gwbas1c 8y ago
Probably the worst thing I've encountered is a junior engineer trying to encourage me to change a coding style by having two coding styles coexist. I just kept repeating, over and over, that I expected the coding-style to be consistent. It was totally over his head, and he totally didn't even bother looking to find a code formatting utility to do a One-Shot style change.

youpassbutter 8y ago

No. That's how you write sql within your organization. Also there are syntactic differences between SQL flavors ( postgres, mysql, mssql, oracle, etc ) that make a SQL standard unrealistic. The only generic rule is "be consistent". Whatever convention/style you choose, it should be consistent.

iblaine 8y ago

> Avoid reserved words Glad you cleared this up for the rest of us. FWIW, naming conventions are like opinions. Everyone has them, and they usually differ from person to person. The best naming convention is a consistent naming convention. Also, naming conventions differ greatly by environment. A group of SQL Server engineers are going to have different standards than those of people working on mysql.

0xffff2 8y ago

Without a rigorous attempt at justifying each of these rules, I don't find this article particularly useful. For example, can someone link to or provide a formal explanation for why table names should be singular? I actually really wanted to read the full relational algebra rational for that one.

davvolun 8y ago
I've been through the relational algebra, I found this to be a nice, quick re-cover. More like a checklist than a full inspection.
zzzeek 8y ago
I'm sure this SO answer will satisfy you: https://stackoverflow.com/a/4703155/34549 https://stackoverflow.com/a/4703155/34549
- kbenson 8y ago
  That answer satisfied me initially, but less and less so the more I read. It became very obvious by the end that what is being represented is one "standard", but presented as the only possible correct solution. It starts off with Yes. Beware of the heathens. Plural in the table names are a sure sign of someone who has not read any of the standard materials and has no knowledge of database theory. I thought the author was being flippant, but it became increasingly obvious that this is a true reflection of their dogmatic view with regard to this topic. Even if this is the same view I would settle on with all the knowledge, being presented with what is obviously a single perspective with no acknowledgement whatsoever of any positive aspects of alternatives causes me to instinctively distrust quite a bit of the reasoning presented.
  - nickpeterson 8y ago
    performancedba answers ... the sound and the fury.
cwbrandsma 8y ago
My own view is they should either be all plural or all singular. Just pick one. But I do gravitate to singular because all nouns have naturally consistent singular words. The same cannot be said of plural. e.g. Moose, Cactus, and any other word that ends in an 's'.

daphneokeefe 8y ago

For naming stored procedures, there were a lot of helpful answers to my question "What is your naming convention for stored procedures?" on StackOverflow a few years ago. https://stackoverflow.com/questions/238267/what-is-your-naming-convention-for-stored-procedures https://stackoverflow.com/questions/238267/what-is-your-nami...

elchief 8y ago

Joe Celko, as well as ISO-11179, tell us to use collective names ("personnel") or plural names ("employees") for tables As well, fewer keywords are plural, compared to singular, so there's less chance of accidentally using a keyword if you use plurals Haven't yet seen an "octopus" table in production...

danso 8y ago
I did a Google search for elaboration on this and apparently there is disagreement that ISO-11179 says this at all: https://social.msdn.microsoft.com/Forums/vstudio/en-US/d5f2faf3-3c85-413d-bc09-ce1d477b31e8/iso-111795-and-table-names?forum=transactsql https://social.msdn.microsoft.com/Forums/vstudio/en-US/d5f2f... > Yes, this is the same version as I found, but the closest thing I could find to addressing table names in the paper itself was an "Object Class name", something like an OOP Class or something you'd find in a UML diagram, but not really the same as a table name, and in any case all the examples were singular. > Was actually kinda hoping Celko would deign to comment on this himself as he seems to be the chief proponent of the "collective identifiers as specified by ISO 11179" meme.
- elchief 8y ago
  "To remind users that tables are sets of entities, ISO-11179 Standard likes to use collective or plural nouns that describe the set of those entities for the names of tables. Thus 'Employee' is a bad name because it is singular" Page 10, SQL For Smarties (Celko), 5th Ed If Celko says it's right, it's right
  - danso 8y ago
    OK, but the ISO-11179 Standard doesn't seem to actually say that. Here's a purported copy of Part 5 -- Naming and identification principles -- of the standard (as linked to from the aforementioned MSDN forum thread: https://www.ftb.ca.gov/aboutFTB/Projects/ITSP/Part_5_Naming_Principals.pdf https://www.ftb.ca.gov/aboutFTB/Projects/ITSP/Part_5_Naming_... The word "collective" doesn't show up in the document. The word "plural" shows up twice, both times in item #a of "Lexical rules": > a) Nouns are used in singular form only. Verbs (if any) are in the present tense. > NOTE In Japanese, this rule shall not be applied because of no plural form of nouns and no distinction of verb tense. The only reference on Wikipedia to the ISO 11179 standard making that recommendation has since been deleted for being unsubstantiated: https://en.wikipedia.org/w/index.php?title=Data_element_name&type=revision&diff=677768128&oldid=544105362 https://en.wikipedia.org/w/index.php?title=Data_element_name... edit: The apparently inaccurate content is talked about on the "Data element name" Talk page: https://en.wikipedia.org/wiki/Talk:Data_element_name https://en.wikipedia.org/wiki/Talk:Data_element_name

jcadam 8y ago

I know a reasonable amount of SQL but am by no means a database guru. Naturally, this makes me the local database expert at my current place of employment :/ The other devs' eyes glaze over when I say things like 'stored procedures' and 'trigger functions.' Bah.

wvenable 8y ago

I don't understand this one: > Mixed case identifier names means that every usage of the identifier will need to be quoted in double quotes I've used quite a few RDBMS engines, including most mentioned by the author, and I've never had to quote mixed-case identifier names. They work just the same as all lower-cased names or as any other case-sensitive language. Most of the programming languages I use typically have the convention of using PascalCase for classes and public fields/properties so I prefer to use that convention for tables and columns (and then everything else for consistency). When doing operations between the application and the database, the name is exactly the same without the need for translation. Otherwise, I think it's a good list.

wfriesen 8y ago
In Oracle this is kind of hidden away since it is case sensitive, but unquoted identifiers are silently converted to uppercase, quoted identifiers are used as-is. So, for queries against something like create table foo ( Bar integer, "Foobar" integer ); Referring to Bar, BAR, bar, and "Foobar" will work, but foobar and Foobar will not.
stubish 8y ago
If you are not quoting your mixed-case identifiers, then they are not mixed-case. They are being implicitly converted to either lowercase or uppercase depending on your database, and your capitalization is lost. It normally doesn't matter, until you are introspecting your database schema, at which point your code generation generates a Organizationrole protobuf message instead of an OrganizationRole, or your ORM fails to find OrganizationRole because PostgreSQL stored it as organizationrole.
- wvenable 8y ago
  This seems like a terrible feature of Postgres and Oracle (which apparently works the same but oppositely). All other database engines I've used have retained the case of unquoted identifiers. I haven't used Postgres enough to notice this, it's almost a deal breaker. I might be tempted to mandate that all identifiers be quoted than deal with half the possible characters for names. Although more likely all code-generation would happen on the application side with DB migrations so the database wouldn't the source of truth for identifier names, anyway.