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?