Page 1 of 1
What's your MySQL naming conventions and why?
Posted: Sun Mar 08, 2009 1:42 pm
by JellyFish
What conventions do you prefer to use when naming your MySQL tables, fields, etc. and why?
I feel kind of stuck in a dilemma where neither conventions I choose have anything particularly over the other; it all depends on my personal preference, I guess. So I'd like know what your preferences are for naming things in MySQL, and why you prefer what you prefer over what other preferences you've considered preferring.
Thanks for reading.
PS: I know MySQL is case-insensitive, but does that also go for table names and field names too? Let's say I had a field named ID and I wrote a query "SELECT * FROM table WHERE id = 23". Would this query throw an error saying that there is no such column named 'id', or will it assume 'id' mean 'ID'?
Re: What's your MySQL naming conventions and why?
Posted: Sun Mar 08, 2009 7:18 pm
by VladSun
[sql]SELECT user.id, user.name, account.level, account.name AS account_nameFROM userINNER JOIN account ON user.id = account.FK_user_id[/sql]
Singular instead of plural name for tables/fields.
No prefixes for none foreign key fields.
FK_ prefix for foreign fields.
Re: What's your MySQL naming conventions and why?
Posted: Sun Mar 08, 2009 7:53 pm
by Bill H
I know MySQL is case-insensitive, but does that also go for table names and field names too?
From MySQL docs:
Database and table names are case sensitive in MySQL Server on operating systems that have case-sensitive filenames (such as most Unix systems).
and
Column and index names are not case sensitive on any platform, nor are column aliases.
Re: What's your MySQL naming conventions and why?
Posted: Sun Mar 08, 2009 7:59 pm
by Benjamin
VladSun wrote:
Singular instead of plural name for tables/fields.
No prefixes for none foreign key fields.
FK_ prefix for foreign fields.
Those are all good ideas. I think I will follow them. I like the FK_ prefix for foreign keys, but I think will use fk_ instead unless there is a good reason.
One question though, if you have a field that contains a bitmask which indicates what options are turned on/off, would you still call it option rather than options? Why?
Re: What's your MySQL naming conventions and why?
Posted: Sun Mar 08, 2009 11:37 pm
by Bill H
field that contains a bitmask
Oh my goodness, are we still using bitmasks? I don't mean that as any kind of jest or poking fun, but with the vast amount of disk space that is available today, I have long concluded that the coding required for bitmasks far outweighed trifling advantages of disk space savings to be achieved. I used them a lot back in the days of 360Kb floppies, but have long since given them up for the simplicity of CHAR or TINYINT fields.
Re: What's your MySQL naming conventions and why?
Posted: Mon Mar 09, 2009 1:45 am
by Benjamin
Being able to store 64 boolean values in a single bigint field when you have 20 million records is a no brainer. Besides, I'm a performance zealot, using frameworks and database abstraction layers kill me.
Re: What's your MySQL naming conventions and why?
Posted: Mon Mar 09, 2009 8:37 am
by VladSun
One thing which we (my colleague and me) always negotiate before starting the DB design: how to name relational tables.
For example, we have have users and groups tables. We need a table that will contain the user membership. So, there are two options for its name: membership or user__group
It looks like we've always chosen the second option.
Re: What's your MySQL naming conventions and why?
Posted: Mon Mar 09, 2009 2:31 pm
by Benjamin
Hey VladSun, did you see my post?
astions wrote:VladSun wrote:
Singular instead of plural name for tables/fields.
No prefixes for none foreign key fields.
FK_ prefix for foreign fields.
Those are all good ideas. I think I will follow them. I like the FK_ prefix for foreign keys, but I think will use fk_ instead unless there is a good reason.
One question though, if you have a field that contains a bitmask which indicates what options are turned on/off, would you still call it option rather than options? Why?
Re: What's your MySQL naming conventions and why?
Posted: Mon Mar 09, 2009 2:42 pm
by VladSun
I think in this case you may use either singular or plural.
Both of them make sense

Re: What's your MySQL naming conventions and why?
Posted: Mon Mar 09, 2009 3:38 pm
by Christopher
I don't use anything like the 'FK_' prefix. I just use <table>_<column> naming for those keys, so:[sql]SELECT... FROM user JOIN account ON user.id = account.user_id[/sql]
This is a standard that several systems, like RoR ActiveRecord, use.
Re: What's your MySQL naming conventions and why?
Posted: Mon Mar 09, 2009 3:45 pm
by JellyFish
I think I've chosen all my columns and tables to be lower-case (because columns are case-insensitive) and separating words with an underscore. And all my primary key IDs with a prefix like 'post_id' or 'user_id', 'article_id', etc. And all my foreign keys to be the same name as the primary key they represent.
Re: What's your MySQL naming conventions and why?
Posted: Mon Mar 09, 2009 5:26 pm
by VladSun
arborint wrote:I don't use anything like the 'FK_' prefix. I just use <table>_<column> naming for those keys, so:[sql]SELECT... FROM user JOIN account ON user.id = account.user_id[/sql]
Yes, I used that before, but now I prefer to distinguish foreign keys in a table at a glance.
JellyFish wrote:And all my primary key IDs with a prefix like 'post_id' or 'user_id', 'article_id', etc. And all my foreign keys to be the same name as the primary key they represent.
I don't like it - post.post_id ... I think many people use this because of the USING operator. it's interesting to know that:
Previously, a USING clause could be rewritten as an ON clause that compares corresponding columns. For example, the following two clauses were semantically identical:
a LEFT JOIN b USING (c1,c2,c3)
a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
Now the two clauses no longer are quite the same:
* With respect to determining which rows satisfy the join condition, both joins remain semantically identical.
* With respect to determining which columns to display for SELECT * expansion, the two joins are not semantically identical. The USING join selects the coalesced value of corresponding columns, whereas the ON join selects all columns from all tables.