3 ms·
1. Strictly speaking, we’re not naming a table, we’re naming a relation. And a relation is a set, hence plural. 2. It reads well everywhere else in the SQL qu
by danbruc 1y ago
1. Strictly speaking, we’re not naming a table, we’re naming a relation.
And a relation is a set, hence plural.
2. It reads well everywhere else in the SQL query:
SELECT Employee.Name
, Manager.Name
FROM Users AS Employee
, Users AS Manager
WHERE Employee.ManagerID = Manager.ID
AND Employee.DateOfBirth IS NULL;
3. The name of the class you’ll store the data into is singular (User). You therefore have a mismatch, and in ORMs (e.g., Rails) they often automatically pluralize, with the predictable result of seeing tables with names like addresss.
The class User represents a single row, not the entire table, hence singular. If the O/R mapper or some other tooling has issues with singular and plural, then I agree, it might not be worth fighting the tools.
4. Some relations are already plural. Say you have a class called UserFacts that store miscellaneous information about a user, like age and favorite color. What will you call the database table?
I think having the table and the class name both in plural would be fine. That also seems rare enough in practice that I would not let this dictate the decision. In the given example I would also tend to record the user facts as a list of them. A user fact is a key value pair associated with an user, the keys living in their own table. Having the keys implicit as column names will also make some queries unnecessarily complicated and as the number of facts grows, the table will become increasingly wide.
Also sometimes we have singular names for collections of things, then it is fine to have a singular table name, you can name your Trees table Forrest if that makes sense in the domain.
- dan-robertson 1y agoA relation is a set in the same way that a function is a set. I don’t think that’s a useful fact when thinking about naming.
- DougBTX 1y ago> they often automatically pluralize, with the predictable result of seeing tables with names like addresss. This is a very poor example, that case is literally in their unit tests file: https://github.com/rails/rails/blob/b0c813bc7b61c71dd21ee3a6c6210f6d14030f71/activesupport/test/inflector_test_cases.rb#L10 https://github.com/rails/rails/blob/b0c813bc7b61c71dd21ee3a6... That test has been there over 18 years!
- gorgoiler 1y agoAre you advocating for using “select … from plural as singular” in all schema and queries? select sum(cat.length) from cats as cat That’s an interesting approach I haven’t seen before. (For me, I’m very much a singular it’s-a-datatype-not-a-collection person, but selecting from cat has always felt a bit awkward with that pattern.)
- danbruc 1y agoI think that is the correct way to do it, you iterate over all the cats picking one cat at a time and it becomes quite obvious when you join a table to itself forcing you to do this. I am not writing that many SQL queries and I am certainly too lazy to always do that, especially if I am writing not too complicated ad hoc queries, but if I want the code to be as good as I can make it, then I always do this. Sure, it makes things a bit more verbose but you can also make the query more readable by picking a descriptive alias, FROM Users AS Manager, FROM Users AS NewEmployee, and so on.
- orphea 1y agoWhether you alias your plural tables to singular nouns would probably be one of the very last things I would check out if I would need to assess your code.
- danbruc 1y agoAnd you should not but I think it is still useful. You will probably not even become consciously aware of the difference but Cat.Name will be ever so slightly easier to read than Cats.Name and maybe that difference in cognitive load is what makes you spot some issue that you would have missed if your brain got repeatedly slightly tripped up by incorrect grammatical numbers.