select * from person join team_member using (person_id)
The advantages of using person_id are even more obvious in multiple joins, such as a star schema where you can using(person_id) all the things, reducing both the typing and the cognitive load.
I suspect that if this convention was more pervasive, programmers would be a little bit less afraid of diving into SQL.
Well, at least we're settling with some convention, so it's not all bad.
EDIT: typo
SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id
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.
There are cons, of course. This is a matter that divides people and when working with other people's projects you have to ignore your own preference and follow the "local" convention.
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...
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.
Page 10, SQL For Smarties (Celko), 5th Ed
If Celko says it's right, it's right
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.
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).
I don't think one standard is necessarily better than another, but the important thing is to have rules. Over the years I've adopted similar rules for myself, and just internal consistency is so much better. I have old projects with table names including: "logs", "log_requests", "log_users", "game_logs", etc., not to mention mixing of plural and singular, to the point where I need to `show tables` before writing any query just to remember what I even called the table I need.
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.
The other devs' eyes glaze over when I say things like 'stored procedures' and 'trigger functions.' Bah.
> 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.
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.
create table foo (
Bar integer,
"Foobar" integer
);
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.
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.
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...
T15X --> Tyrannosaurus Rex
D11S --> Dilophosaurus
B11S --> Brachiosaurus
T9S --> Triceratops
S9S --> Stegosaurus
We're always learning.
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.
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.
Otherwise you're actually introducing ambiguity imo.
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".
Do not do this in Postgres, it will be a pain in the ass since you will have to use quotes around everything.
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.
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.
I've spent weeks of my life tuning SQL, to the point of writing SQL generation libraries to effectively override the database optimizer when it consistently makes poor decisions in specific use cases. But I don't expect the rest of the team to know SQL as well as I do.
When I'm writing or generating SQL, I don't really care much what the naming convention is. If it's consistent, then SQL is easier to write. Consistency is more important than the specifics of any conventions.
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.
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.
The only generic rule is "be consistent". Whatever convention/style you choose, it should be consistent.