There are lots of guidelines for naming functions, objects and variables in PHP and other languages but when I started with MySQL I didnt find one that applied to databases. Fortunately I did one I saw once in an example (I don't remember where) and have added to it.

So here it is:

tablename_fieldname[type]

So, for a simple mailing address list I would create these fields:

table name: addresslist

Fields:
  addresslist_id  ((all my tables have an 'id' field*))
  addresslist_firstname
  addresslist_lastname
  addresslist_mailaddr1
  addresslist_mailaddr2
  addresslist_mailcity
  addresslist_mailst
  addresslist_mailzip
  addresslist_taglist ((the 'list' at the end indicates an imploded list))
  addresslist_update  (('date' at the end indicates a date field))

Then say I had another table subscription, that relates to current subscriptions of folk in the address list

table name: subscription

Fields:
  subscription_id
  addresslist_id
  subscription_publication
  subscription_startdate
  subscription_enddate

As you can see, it's pretty apparent what fields are related to what tables.

With these guidelines I can do some really cool stuff without having to hard code tons of specific field/table logic in my scripts, (which again, is another post.)

Think about it - it is a bit more typing, but besides the benefits in scripting it also adds much more readability into your code such as this query:

SELECT
    *
FROM
    addresslist a
    LEFT JOIN subscriptions s
        ON s.addresslist_id = a.addresslist_id
WHERE
    subscription_startdate <= 20100501
    AND subscription_enddate >= 20100501
    AND subscription_publication = 'News About Me'

Now I have an alternative table style for data that gets updated but never gets deleted… the records are datestamped (could be timestamped but I felt date was fine grained enough for my purposes) So those tables have more common fields:

Table: addresslist

Fields:
  addresslist_rid
  addressllist_id
  addresslist_begin
  addresslist_end
  .... rest of field set
  

Each change would be a new record with a new record id (rid) but the same general data id (id). On the old record, the “end” date to be made to be the day before the change. On the current record, the begin date to be the date of the change. And for “current” data records I put a max value in the end column.

Example: (for external compatibility I record my dates in a YYYYMMDD format, though the MySQL date format is good too) Here is one historic record, created 9/23/2008, and then updated 7/13/2010:

If you are willing to take the time this could be optimized, where all you need is a begin field, and that could be the rid, but partly for time and also readability sake, I chose to leave my structure a bit more verbose.

(Note I don't normally use numeric ids, but currently a 15 byte varbinary fields… and that is a great subject for another post )

Thats it for now, hope that gives you some guidance and/or inspiration.

~~LINKBACK~~ ~~DISCUSSION~~