====== My Table Field Naming Tips ======

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:

<code sql>
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'
</code>

====== Historical tables =====

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:


^addresslist_rid^addresslist_id^addresslist_begin^addresslist_end^other fileds...^
|  565  |  123  |  20080923  |  20100712  |  ...  |
|  1022  |  123  |  20100713  |  99999999  |  ...  |

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~~