What's your MySQL naming conventions and why?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

What's your MySQL naming conventions and why?

Post 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. :D

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'?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: What's your MySQL naming conventions and why?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's your MySQL naming conventions and why?

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: What's your MySQL naming conventions and why?

Post 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?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: What's your MySQL naming conventions and why?

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: What's your MySQL naming conventions and why?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: What's your MySQL naming conventions and why?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: What's your MySQL naming conventions and why?

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: What's your MySQL naming conventions and why?

Post by VladSun »

I think in this case you may use either singular or plural.
Both of them make sense :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: What's your MySQL naming conventions and why?

Post 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.
(#10850)
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: What's your MySQL naming conventions and why?

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: What's your MySQL naming conventions and why?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply