eliminates question of proper case as well as errors related to case-sensitivity
speeds typing rate and accuracy
differentiates table and field names from uppercase SQL keywords
separate words and prefixes with underlines, never use spaces
promotes readability (e.g. book_name vs. bookname)
avoid having to bracket names (e.g. [book name] or `book name`)
offers greater platform independence
avoid using numbers
may be a sign of poor normalization, hinting at the need for a many-to-many relationship
table names
choose short, unambiguous names, using no more than one or two words
distinguish tables easily
facilitates the naming of unique field names as well as lookup and linking tables
give tables singular names, never plural (update: i still agree with the reasons given for this convention, but most people really like plural table names, so i’ve softened my stance)
promotes consistency with naming of primary key fields and lookup tables
ensures alphabetical ordering of a table before its lookup or linking tables
avoid confusion of english pluralization rules to make database programming easier (e.g. activity becomes activities, box becomes boxes, person becomes people, data remains data, etc.)
more grammatical SQL (e.g. SELECT activity.activity_name –rather than– SELECT activities.activity_name)
avoid abbreviated, concatenated, or acronym-based names
promotes self-documenting design
easier for developer and non-developer to read and understand
prefix lookup tables with the name of the table they relate to
groups related tables together (e.g. activity_status, activity_type, etc.)
prevents naming conflicts between generic lookup tables for different entities
for a linking (or junction) table, concatenate the names of the two tables being linked in alphabetical order
orders linking table with a related entity table
expresses composite purpose of the table
this should be waived if the linking table has a natural, standard, or obvious name (e.g. “item” in: [order] 1 to M [item] M to 1 [product])
this must be waived if there are multiple linking tables between the same two tables, (e.g. “student” and “instructor” between the tables “person” and “class”)
rare problem
what about a linking table for a generic lookup table?
this can be avoided by renaming the generic lookup (and removing the prefix)
suggestion: as a variation on the linking table naming convention above, use a special character (such as a plus-sign “+”, a hyphen “-”, or a double-underline “__”) to separate concatenated table names (e.g. the linking table for activity and activity_type would be activity__activity_type)
field/column names
the primary key should be the singular form of the table name suffixed with “_id” (update: i now just name all my auto-increment primary keys “id”)
allows primary key to be deduced/recalled from the table name alone (e.g. primary key of the product table would be product_id)
consistent with the name of the foreign key
prevents having to alias primary keys in programming
prefix the name of every field with the table name, excluding foreign keys (update: i no longer follow this convention because it’s very tedious to type, and because it’s obviated by the use of abbreviated table aliases in SQL)
prevents using “name”, “order”, “percent”, etc. as field names and clashing with SQL/RDBMS reserved words
creates near unique field names (e.g. product_name, product_code, product_description, etc., often simplifying query design and SQL coding, recommended for PHP)
makes the field names consistent with the primary key
differentiates foreign key fields from fields native to the table
maintains semantic transparency of field names when using table aliases (e.g. SELECT a.activity_name FROM activity a)
prevents naming a field the same name as the table
this can be waived for databases with many tables (30+), tables with many fields (30+), and long and obviously unique field names
this can be waived if your database programming always refers to fields in the form tablename.fieldname
foreign key fields should have the same name as the primary key to which they refer (update: obviously now that i just name my primary keys “id”, i name my foreign keys the singular form of the table name + “_id”)
makes the table to which they refer completely obvious
if there are multiple foreign keys referencing same table, prefix the foreign key field name with an appropriately descriptive adjective (e.g. lead_person_id, technical_person_id, etc. which transparently reference person_id in the person table)
suffix fields of type date with “_on”, suffix fields of time datetime with “_at”, and prefix fields of type boolean with “is_” or “has_”
prevents confusing with more common text/number data types
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.
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.
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.
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.
The ANSI SQL standard is that object names are case insensitive. A plague on Microsoft for introducing case sensitive names which when translated to other database can require wrapping them in double-quotes. So pick a case, lower or upper, but please avoid mixed case.
table names 2. Use of the singular form
I have always used the singular form for both table and class names for the simple reason that I got sick and tired (25 years ago) of trying to remember whether a table or class was SOMETHING or SOMETHINGS and decided that if I used a more primitive grammar that only included singulars I would be able to use my memory for more important things.
Thanks for the list, it is very similar to my practice (been programming for 30+ years) but it is good to know that someone else has arrived at very similar conclusions and was nice enough to write it out and blog it.
Mark, thanks for leaving a comment. Can you believe I created this 6 years ago, after working with Microsoft Access for only 3 years. Since then I’ve been working almost exclusively with MySQL, and save for a few adjustments (marked in pink) based on the agreed upon styles of my employers, they’ve held very well.
justin, i hope not to be a interuptor. i’m just a casual access 2003 personal user. this has been one of the most difficult areas for me, i spend much time trying to keep names of things straight and logical.
i do split my databases, my question is: would you prefix table names with a 3 letter acronym of the database it comes from especially if there is multiple back-end databases connected to one front-end?
i’m not a hefty programmer but enjoy learning. i want to use generic relink code – maybe modified a bit – to call a relinking if it was moved using table prefix and wildcard or would i just go for the link string itself?
this discussion again has been immensly helpful.
i can learn a thing or two and it stays with me but when lots of things need to be renamed it’s a big time waster.
thanks
Would you believe there are not too many such articles like this on the web and most of them are m$ sql not standard arrogant ones that promote stuff like uppercase and stuff? thanks for this great article
Don’t soften your singular views. A table is an entity, thus the term “Entity Diagram”. Entities are singular and relate so.