3 Top Tips for Database Naming
08/10/2010
Total Views: 3843
Lorna Jane Mitchell is a PHP developer, blogger, trainer and evangelist from Leeds in the UK. She is active with phpwomen.org and her local user group PHP North West, and writes for a variety of outlets, including her own blog at lornajane.net. She is an active member of the PHP and open source communities and contributes to the joind.in event feedback project. When she's not at her computer, Lorna enjoys yarn craft, hobby electronics, and her home renovation project. Lorna is a DZone MVB and is not an employee of DZone and has posted 32 posts at DZone. View Full User Profile
Perhaps this is more of a rant than a post but I do keep running into
issues with databases with names that are inconsistent - which makes
them really difficult to work with. When designing a database, there
are a few points to consider:
This goes for table names, and also for the names of join tables. If you call your tables "user" and "group" then you probably want your linking tables to be "user_group". If you go for plurals (my personal favourite) then be consistent over whether the linking tables are called "user_groups" or "users_groups".
I've seen two main variations on the column names for primary keys, one is to call them all simply "id", and the other is to name them after their table name such as "user_id" or "group_id". It doesn't really matter but my recommendation is for the latter - that way, the user_id column in any other table clearly joins on to the user_id column in the users table, making it easy to read and understand.
Due to my EXtreme DOuble CApitalitis, I prefer everything to be lower case, but the key is consistency, so that it is easy for developers to get used to the patterns in the database setup and to develop against your schema without having to refer back to it all the time.
In general, I like database schemas which are predictable and well-laid-out. Although I have my own preferred conventions, I don't mind what is used so long as it is predominantly in step with itself - this makes my life as a developer so much easier! What's your top tip for sane database naming conventions? Leave a comment and let me know!
Singular and Plural
This goes for table names, and also for the names of join tables. If you call your tables "user" and "group" then you probably want your linking tables to be "user_group". If you go for plurals (my personal favourite) then be consistent over whether the linking tables are called "user_groups" or "users_groups".
ID Columns
I've seen two main variations on the column names for primary keys, one is to call them all simply "id", and the other is to name them after their table name such as "user_id" or "group_id". It doesn't really matter but my recommendation is for the latter - that way, the user_id column in any other table clearly joins on to the user_id column in the users table, making it easy to read and understand.
Case and Capitalisation
Due to my EXtreme DOuble CApitalitis, I prefer everything to be lower case, but the key is consistency, so that it is easy for developers to get used to the patterns in the database setup and to develop against your schema without having to refer back to it all the time.
Consistency is Key
In general, I like database schemas which are predictable and well-laid-out. Although I have my own preferred conventions, I don't mind what is used so long as it is predominantly in step with itself - this makes my life as a developer so much easier! What's your top tip for sane database naming conventions? Leave a comment and let me know!
- Login or register to post comments
- 3843 reads
- Printer-friendly version
(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)
Comments
Hassan Turhal replied on Sun, 2012/01/22 - 12:34pm