Don’t soften your singular views. A table is an entity, thus the term “Entity Diagram”. Entities are singular and relate so.

Link, I blame DHH

It’s true; a fairy dies every time a table name is pluralised.

Each table is a single entity which gets a single name. The name reflects its function, which is to contain multiple records of similar type. The plural name Customers accurately describes a table containing multiple records of type Customer.

A single record contains multiple attributes, but they are dissimilar. The singular name Person describes the single essence of the multiple attributes Name, Birthdate, SSN, etc.

That doesn’t negate Justin’s reasoning, of course. Just explains why people like plural table names so much.

Justin,
Why do you no longer follow column rule 2?

Douglas, you’re asking why I no longer “prefix the name of every field with the table name”? There are a few reasons.

Primarily, it’s just murder to have to type out SQL like that.

Secondly, after I started using plural table names (because that is the standard where I work), it didn’t make sense to use a singular prefix with a plural table name.

Thirdly, I frequently use one or two letter table aliases in my queries that join multiple tables, which is much easier to type than the prefixed table name, over and over and over again.

Note: I’ve clarified my reasoning in the post itself.

Personally I prefer plural table names. I echo the reasons laid out by Douglas in a previous comment, although I believe this is just a nuance.

A consistent convention that effectively communicates the database’s design is the ultimate goal, and your article is an excellent guide.

Thanks for this. I followed this guide when i started developing, because it seemed most thought through to me.

After my third DB with now ~150 tables, i hope i can make a useful suggestion.

lets take orders as the base table. So we have:

- the base table orders
(order_id, order_type_id, date_created, date_changed, price, etc.)
- the a lookup table orders_type. Note the missing s in the end (is this n->1 or 1->n ?!). This is just common sense, because for one order_type there are multiple orders ;)
- the n:m table orders_products. Note both parts are plural. products is another base table.

Now the problem: the third type of tables, where order_id is referenced in a n->1 (or 1->n? no idea ;) ) style. fe, when you want to add multiple rows of notes. Lets say notes is an stand-alone table. Then we have (note_id, order_id, note_text, etc.). In this case, no reference to orders in the table name is needed.

IF we want to connect notes hard for orders (fe, if we want the table notes for other purpose) you can switch to:
- order_notes. Note the missing s in the first part. Like before, this is just common sense: There are multiple notes for one order.

So i came down to 5 different table types:

- base: object1+s
- lookup (n:1 ?): object1+s_object2
- n:m: object1+s_object2+s
- reference (1:n ?) type 1, standalone object: object2+s (object1_id is referenced)
- reference (1:n ?) type 2, non-standalone object: object1_object2+s

I could now safely use some skripts which will automatically (!) join tables and distinguish which type a table is and what to do with this. For instance my func. $order = v(“order”, 12345) will insert the order 12345 in the assoc. array $order with the correct joined order_type automatically!

or $order_note = v(“order_note”, 12345) will create a 2-dimensional array like $order_note[$order_note_id][order_note_text] etc.

last one ;) n:m. $order_product = v(“order product”, 12345) gets 2-dimensional like the one above where complete info for the products is referenced (YES, the product_type ALSO !, so 2 automatic joins). This can be used the other way around with v(“product order”) which will reference the same table because it is named alphabetically but look up for all orders and their order_type. Note, that there is no _ in between, this way the script notes there is a n:m relation.

Of course you can do some more magic with this or just cut it down to 1 or no automatic inclusions, include a where-clause or prepare a listing.

I hope you like this and can feed me some more ideas.

If anybody else has a suggestion mail me at riwale at gmx net

ROFFFLLLL over fairy dies!!! of course my co-worker tells me that only a geek would laugh @ that. when you have an object, do you have many of the same or one distinctive? (i’d hope you’d have one)so, logically, a single thing would not be pluralised. so if i am holding one apple, i would not call it apples or seeds. OR how about a basket holding many apples. would i call it apples or baskets or a BASKET?

I agree 100% but taking your linguistic analogy one step further, if there was a basket of fruit in the corner of a room that happened to contain apples and I pointed at it and asked you “What’s in there?” How would you respond? Probably “apples”. Or “it’s a basket of apples”. In both cases plural.

I have always used plural table names; moreover, I have never seen a production system that used singular names. But after reading the arguments presented here, I am considering changing my practice.

However, the argument for “more grammatical SQL” is erroneous. It is more grammatical in a SELECT clause (if you even use the full table name in the select statement and not a prefix), but it is less grammatical in a WHERE clause, e.g. “FROM activity” instead of “FROM activities”.

These are solid conventions. I use singular table names for simplicity. Obviously a table can hold more than one record so whats the point of a plural table name when it “should be” understood.