Database Naming Conventions

So, there were a few articles months and months ago I didn't get to post, on database naming conventions. I still haven't drank the proverbial Koolaid yet, but Ruby on Rails provides a really well thought out way to name your tables and columns, that allows objects to be mapped to the database without much (any) mapping files. Taking that lesson back to other languages might be worth anyone's time, as even annotations with Hibernate Annotations would help.

Basically, they took all of the best practices that were around when Ruby on Rails was put together, and they used them intelligently. It's the first place I've seen formal database naming conventions aimed at developers and not DBAs, and most projects don't get a full DBA, maybe don't need a full DBA, and usually name the columns before the DBA gets a say.

Unfortunately, the trophy article isn't there anymore.

  • So, table names all get underscores (_) as spaces, all lowercase text.
  • Object names are all singular.
  • Tables store multiple objects, so table names are all plural, which makes grammar more natural. (persons, orders, etc.)
  • The primary key of every table? (id)
  • The foreign key to another table is the singular object name followed by id. (person_id, order_id)
  • If something links to a parent in the same table, parent_id.
  • There are magic names that get treated specially. created_at gets autoset with the timestamp; created_on gets autoset with the date. updated_at and updated_on work the same way.
  • Mapping tables - like a link to persons and orders, earlier - get both table names combined, ordered alphabetically; orders_persons, for example. That would have order_id and person_id in it, as well.

Rails takes this at least two steps farther; if you build the Rails object, and run the script to autogenerate the database, it'll build it for you as scaffolding.

Anyways, I'm wondering if it'd be a significant timesaver or an ongoing disaster to have a language that built all of it's objects dynamically from the database schema present at compile time.

No comments: