In case the reasoning behind lower-case names wasn't clear, here's why: Postgres automatically and invisibly folds names down. So, all of these names are the same:
They're all user_platform internally. Unless you put them in double-quotes:
"UserPlatform" is "UserPlatform". But to use this field or table, you would then need to put the name in double quotes everywhere you use it. It's a total pain. So, it's pretty standard to see names like
With that said, as long as you don't use the double-quotes, you can write the name
if you find it easier to read.
As far as the naming convention goes, yes, pick one, stick to it and document your rules. You get 64 characters to work with (by default), which I haven't found to be an issue, except sometimes for index names.
For linking tables, whatever you like is fine. Linking users and platforms?
whatever you prefer.
As far as the singular vs. plural argument, it's a matter of taste. Some people care. A lot. The gutters run red with the blood of the unrighteous! I say do whatever you like. I'm of the make-table-names-singular school, but mostly because it's a habit that I picked up long ago.
Ah, one thing about the double-quotes...I do use them. We ship Postgres data to a visualization/analytics program called Domo. For that, it's nicer to have column (attribute) names in MixedCase. For that, I create views with "MixedCase" column aliases so that the data exports with a "MixedCase" name. This is a slightly exotic scenario, but maybe you'll run into something similar one day. Normally, I'd change the tables client-side, but the views were the easier solution.
Bonus tip! If you haven't discovered it already, Postgres text fields are case-sensitive. If you don't like that, you can:
- Write your queries to deal with the data case-blindly. Every. Single. Query.
- Use the standard citext extension. (Case Insensitive Text.)
- Wait for PG12 to ship with case-insensitive collations. (Haven't tried it out yet.)
Forgot to mention...Postgres has unusually good support for commenting on system elements, including tables:
Someone has to look for and read your comments, of course, but it's another place that you can provide context. Even if that context is nothing but a link to a wiki you maintain, etc.