PHP MySQL tips

Monday, August 6th, 2007

Interesting PHP MySQL tips.

Some comments:

2. Always prepend the table name to the field. E.g. ProductName, SupplierPostCode.

This refers to field name aliasing, not to actual field names. He doesn’t suggest that you actually name your fields like that in the table, but only when you select them: SELECT product.name AS product_name instead of SELECT product.product_name. The only real reason I can think of why you’d want to do this is for easier selecting the values in the calling code. I don’t see why it would make joins easier, as it’s just as easy to type JOIN order ON order.product_id = product.id, IMHO.

3. Always create a primary id field with the name of the table followed by the id. e.g. ProductID

Two things I don’t agree with. First, it’s not always necessary to create a primary id field. You should only add an ID field if there’s no unique key for that table. Second, I don’t think prepending the tablename to the ID field is a good idea for primary id fields. It’s not really bad either, but I like a plain ‘product.id’ field better, because you can instantly recognise the unique identifier field for that table. Foreign key fields, on the other hand, do feel better with their tablename prepended: order.product_id refers to product.id.

5. Use a separate logging table or transactions for logs of table updates, deletes etc.

We usually create a duplicate table for every table in the database, and name it ‘tableHistory’ or something like that. Then we add some metadata fields to the history table (time, user, mutationtype, etc) and place triggers on each table so that any mutation will insert the old row into the history table for that table, along with some metadata. That way, you always have a complete history of every mutation in your database. Great for auditing, plus you can create generic ‘Last changed by foo on bar’ functionality very easily.