Differences

This shows you the differences between two versions of the page.

@@ Line 1: / Line 1: @@
+====== 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~~

blog/2010_07/07-10_my_table_field_naming_tips.txt · Last modified: 2010/07/10 23:47 by larry

Back to top