Tuesday, May 10, 2011

Database tables and columns naming conventions

Once again I've got to explain what I think about database tables and columns naming conventions. This time, I've decided to write down my points, to be able next time just to give a link :).

Starting with tables - main question is plural or singular form should be used. Main point in favor of plural form is something like "it is a collection of entities, so it must be named in plural form". Singular form is preferred when you want to write query like "where user.id = ", and not "where users.id = ".

When it comes to columns' names, there are much more arguing about it. Main one is about primary key - should it be ID or entity_id. In case you've used plural form for tables, it become more pleasant in queries - "where users.user_id = ". Next bone of contention is about short forms in columns' names - could you name it like userGoodsCnt, or you must name it like amount_of_goods_in_user_cart.

It's funny to notice that usually, ones that love plural form of tables' names and long version of primary key prefer long version of columns' names too. Why do they love long names so much? In most cases they say that it's because "database schema must be as much readable as it could be". It is very important to have readable and pretty much self-explanatory schema, then why others disagree? Ones that like short forms says that "it is _understandable_, and it is shorter. save a byte - save a tree". One more point - description of column or table could be kept near it, not necessarily in name - in description, in repository, in comments... 

From my point of view, it is all about when and how you work with schemes. Either you usually look on DB chart, or DB schema or some entities' relations graph... in this case you prefer long, self-describing names, since comments are hidden at this time. And, what is usually much more important, you look at this chart from time to time, either you are DBA with a lot of such schemes, or it's just not your primary job - you just refer to schema sometimes, so you do not need to remember it and want to get all information from just one look. On the other side are those who work with tables' and columns' names many times a day, those who write code and queries. They usually keep whole DB schema in mind, and since they write all those names with their own fingers - they want it to be as short as possible.
First group are architects, who do not write code, technical directors and so on. Second are developers and coders.

Summary: if you are a leader and architect, do not forget about those who will work with schema every day - do not spoil their comfort in favor of pleasure look of DB chart. Developer's comfort is very important - not lesser then yours. But they spend much more time with DB - don't forget about it. It doesn't mean that table could be named as table1 with columns A,B,C - no way.

I myself prefer to name tables in plural form, but to name columns as short as possible. It's because I use ORM, so I do not usually see ugly queries like "where users.id = ", but I use forms like "select from users where name like" - it is ok with me. But when it comes to columns' names - I try to keep it as short as possible, as long as it is understandable to those, who work in the same project. I'm pretty much sure, that if you can't spend time to read and understand DB schema with shortened forms (like usr instead of user) - then you'll not have enough time to make something good to project's technical development.