I am trying to develop an application for my localhost on which I can track physical attributes (ht, weight, body water, body fat, etc) over time to compare to caloric intake for the purposes of meal planning, etc. Here's what I have right now:
DB surname
TBL home
memberID (tiny int, 1, auto increment, primary key)
name (varchar, 30)
gender (char, 1) ->value should be m or f, but this isn't defined anywhere
birthdate (datetime)
for storing the physical data over time I have considered having a table with a three-column primary key (date, person, type of measurement) with regular single column indexes on each column so that I can look up all entries for a date OR for a person OR for a measurement type. Like this:
DB surname
TBL stats (or something to that effect)
date (datetime, PK1, index)
memberFK (tiny int, 1, PK2, index)
type (varchar, 3, PK3, index) -> possible values should be ht | wt | fat | wat maybe others will also become necessary
value (decimal, 4/1) -> store all values in nnn.n format in a particular system (ie metric) and do any conversions, add units, etc when the data is called
This still seems to create a lot of excess redundancy, so I thought of maybe storing each users measurements in a table in their own database, like so:
DB user1
TBL stats
date (datetime, PK1, index)
type (varchar, 3, PK2, index)
value (decimal, 4/1)
This seems to clean it slightly by removing a column from the foreign key (and the table altogether), but also prevents me from utilizing foreign keys between this table and other nutrition-related tables that are not user-specific. On the other hand, I plan on developing applications for other household topics in which there is general data and user-specific data, so maybe this is the best way if there are good ways of using data between databases like that.
So neither of these seems quite right to me, but I am at a loss as to how to make them better. I am tending towards the second example, provided I can figure out ways to retain the integrity of my data. Please make any comments of suggestions you think will help. Thank you!
Here's how I would do it
DB healthstats
TABLE user
memberID (int*, auto increment, unsigned zerofill, primary key)
name (varchar, 50)
gender (char, 1) ->value should be m or f, but this isn't defined anywhere
birthdate (datetime)
TABLE reading
readingID (int*, auto increment, unsigned zerofill, primary key)
memberID (int*, FK: TABLE user)
date (datetime)
TABLE stat
statID (int*, auto increment, unsigned zerofill, primary key)
readingID (int*, FK: TABLE reading)
type (varchar, 3)*
value (decimal 4.1)*
* These datatypes are up to you based on what makes sense, just make sure primary and foreign keys match up.
A stat is a measurement (e.g. height, weight, bodyfat, etc.) A reading is defined as one or more stat measurements taken at the same time.
Note this all is within the same database and it contains three tables.