Names Mean Something

This week I had an ARGen user contact me.  They had purchased a license and it wasn’t outputting the tables like it should.  He was able to send me the database and I confirmed the problem.   I looked at the XML file that ARGen was producing and it appeared to be an error in the XML generation.  I did a quick fix and sent it back to him.

Except, that didn’t really solve the issue.  Silly me for not looking more into the issue (it’s good to be busy, but sometimes…).  Of his four tables, only the first two were generated for the DataFile namespace we use for ActiveRecord.  Digging into it some more I discovered that Database.FieldSchema was failing.

Silly me for not checking for a database error.  I find this ironic that I preach db error checking and I didn’t check it in that particular case.  I guess I just didn’t think that FieldSchema would return an error.  Anyway, the SQLite database had a table named ‘group’.  That happens to be a reserved keyword in SQLite and you really shouldn’t be able to do it, but some tools will let you do it anyway.  In fact, all of my tools let me do it but they must be qualifying table names internally.

In SQLite, if you put single quotes around the name like ‘group’ it will work.  In a quick test against MySQL and PostgreSQL that doesn’t work and generates a db error.  Now ARGen checks for the error and will warn if it bails prematurely because it can’t find the schema of a table.

I also recommended to the developer that he change his table name.  Having ‘group’ in every SQL statement is going to be a royal pain.  It’s easy to forget and unless your QA and testing staff (stop laughing and catch your breath) tests every single code path it’s quite possible that your customer will find it before you.  Why make life harder on yourself?

Which leads me to what BKeeney Software does.  Carol, our resident DBA (otherwise known as the Database Goddess) has a bunch of naming conventions for databases.  All tables have a prefix.  Regular tables have a “t_” prefix.  Reference tables like states, types, zip codes, etc, get a ‘tr_’ prefix.  Tables that are a many to many relationship have a ‘tx_’ prefix.  Views get a ‘vw_’ prefix and stored procedures have a ‘sp_’ prefix.

Some other rules:

  • Table names are never plural.  It’s never t_people, it would be t_person.  t_check not t_checks and so on.
  • If it’s a child table it will have the parent table name in its name.  So an invoice line items table might be t_invoicelineitems.
  • Avoid abbreviations unless it makes sense to use them.  We just finished a large accounting app with payroll.  We had well over 100 tables JUST for payroll, so rather than having payroll spelled out in every single table we just used ‘pr’ in the table names.
  • For the Primary Key field we insist on auto increment integers.
  • Primary key field name cannot be a simple ‘id’.  It’s too easy to get confused with which ‘id’ you’re talking about in joins.   Our primary field name is TableName_ID (underscore ID at the end).  So the primary key for t_person would end up being Person_ID.

I’m sure some of you are thinking that these rules are overboard and I’d agree if I didn’t do a dozen large database applications every year.  Once you get past a dozen tables or so things start to get confusing so having rules give us some advantages on big projects.  At a glance we can tell the function of a table.  Time is money and with a staff of four, the last thing we want to do is make it harder for a team member to determine what the function of a table is.

Names mean something and some names have implications.  If the developer had used our naming rule so that his table name was t_group, ARGen would have had zero issues.  Oh well, it was an instructive bug report.  I get to chastise myself for not doing some error checking and I get to write about it.

What things do you do on databases to make your life easier?

1 thought on “Names Mean Something

  1. What do I do to make my life easier with databases? A LOT! 🙂 But I will highlight three things:
    1) Name constraints. There is nothing less intuitive than looking at an object name that has been auto-generated by a database. P_094586555 is not meaningful. Whereas PK_ or FKXX_ are much MORE meaningful (where XX is a sequential number).
    2) I use class words. Class words are suffixes that go at the end of column names that give a general idea about the kind of data being stored in a column. Here are some examples:
    _AM = Amount ~ this is going to be hold currency values: ex. TOTAL_SALE_AM
    _QY = Quantity ~ this is holding a quantity or count of something: ex TOTAL_SALE_QY
    _CD = Code ~ so I know code values are in this column (whether numeric or alpha is irrelevant). ex. SALE_TYPE_CD (where values may be “R” for Retail, “W” for wholesale, “I” for Internal Transfer, etc
    _TX = Text ~ so this will be a large text column, not necessarily a CLOB. ex. NOTE_TX
    _DT = Date ~ so this is hold date info. ex SALE_DT
    _ID = Identifier ~ this is going to be an identifier, usually auto incremented. ex. SALE_ID
    _NM = Name ~ A name field of some kind. ex. COMPANY_NM or CUSTOMER_FIRST_NM
    3. Treat the DDL as source code and check it into some kind of source control system AND tag it against a particular version/build of programming source. If your programming code and database code don’t march together in lock step you could find yourself in a bit of a pickle when you go to build something from scratch. The exciting part of of this though is that DDL, unlike programming code, is transitional, and not declaritive. For example, you start off with a certain database design and check in all your DDL for the entire database. Now as time goes by you have several new ALTERs, CREATEs, DROPs, etc..which mark a transition or change in the database baseline. So to rebuild your database you have to start with the original baseline and apply all those changes to get it current. Not so with programming code. One solution to this is to re-baseline your database at each major release.

    Craig

Comments are closed.