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'?
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?
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.
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.
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
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?
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.
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.
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