71–80 of 85 posts
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...
> 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.
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).
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.
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.
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'.
The few advantages of singular:
1. It's not always clear what the plural of something should be.
2. Chances are the singular maps better to your application layer (Person class Person table).
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...
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/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 th…
Page 10, SQL For Smarties (Celko), 5th Ed
If Celko says it's right, it's right
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 t…
create table foo (
Bar integer,
"Foobar" integer
);
Earlier quoted context omitted.
I'm sure this SO answer will satisfy you: https://stackoverflow.com/a/4703155/34549
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…
Earlier quoted context omitted.
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/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 th…
"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
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...
edit: The apparently inaccurate content is talked about on the "Data element name" Talk page: https://en.wikipedia.org/wiki/Talk:Data_element_name
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 t…
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 t…
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 fail…
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.