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 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)
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)
prefix fields of type date with “date_” and type boolean with “is_”
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.
Don’t soften your singular views. A table is an entity, thus the term “Entity Diagram”. Entities are singular and relate so.