WordPress plugin and database data

by | Apr 11, 2012

So I’ve created the tables for my Unit Tracker plugin and created some dummy data for an initial example Tracker. As nice as this is I’m not entirely sure about how I’ve designed the tables (read that as saying “if you think this is a mistake comment“). My assumption is as follows for the pure […]

So I’ve created the tables for my Unit Tracker plugin and created some dummy data for an initial example Tracker. As nice as this is I’m not entirely sure about how I’ve designed the tables (read that as saying “if you think this is a mistake comment“). My assumption is as follows for the pure basics, you will need at least 2 tables;

Table 1 – Trackers

A table to store all the different units being tracked, there can be a number of different units tracked, or maybe even a set of units tracked in tandem for example body-fat % , weight and stomach measures.

  • Name and Description of the tracker
  • Link to a post/page further describing the details about the tracker
  • A display Reference (something like a permalink) that can be used to display the tracker
    • for example [:tracker-weight:]
  • Validation, due to the fact that any unit may be added a validation key will be added here (regex?)
  • Unit Measurement, because people might be measuring lbs, Stone (British measure), Kg, % or Quarks the unit of measurement is left blank and assigned by a user (but there will automatically be a suggested one in the form)

Table 2 – Tracker Values

A table to store all the values recorded by the user. This is so in the future we can use this data to map graphs, allow cvs downloads e.t.c.

  • Value, this I have decided to store as a string, now I may well regret this but it seemed the easiest way to store data of any type including time (hours/days/months/years) weight can be stored in Metric and converted as necessary. The method of extracting and processing the data is based on the validation in the Tracker table. You can’t change the type after the Tracker contains data.
  • Created, the date that new a new data value was added to the tracker
  • Tracker Id the ID (primary key) of the tracker the data belongs to.

Of course this isn’t all, there will be probably options stored in the WordPress options database table to decide on appearance, widget placement etc, additional tracker values that show targets, method of display, change over a period of x, and many more…

First of all I’ll settle with the above as an Alpha 0.0.1 release and when this works I’ll add accordingly.