MySQL tables name guide style

Question

I would like to know which is the more correct way to name the tables and fields in a MySQL database .

And how to differentiate tables that are intermediate tables .

Examples:

Table name: users or user

foreign key columns: user_id or id or user_ID

Table with two words: purchased_point or point_puchased or pointpuchased

Itermediate table (many to many) users_sites or user_sites or usersites

there's no right/wrong way. there's only guides, which ultimately are purely opinions. — Marc B, Apr 16 '15 at 20:07

score 0 · Accepted Answer · answered Apr 16 '15 at 19:03

All caps. Different words separator is _ underscore.

Master or parent tables prefix MSTR Child tables prefix MPNG Transaction tables where multiple parents are referred as TRXN

Post fix all tables as _TBL

Use singular names for tables as user and not users

E.g. MSTR_USER_TBL, MPNG_ACCOUNT_TBL, TRXN_CART_ITEM_TBL

These conventions i follow. Makes simpler to read.

score 0 · Answer 2 · answered Apr 16 '15 at 20:06

I don't know if what we do is standard but here is what we do :

We use singular name. Plural is unnecessary : we already know there can be more than one user in the table User.
Our database is used with a Java app so we sort of use Java naming convention for table name and column name. This works pretty well with our DAO layer. That means :
1. no underscore;
2. upper case for the first letter of each word of a table name (PurchasedPoint);
3. lower case for the first letter of the first word of a column name and upper case letter for the first letter of all the other words (userID);
For me, the word order inside a table name gives a hint on the relationship between tables. For instance, if there is a table Purchase and a table PurchasePoint I can guess easily than there is some kind relationship between them.
For intermediate table we would use something like UserSite.

Hope it can helps.

2 Answers2