Table help. Dates and Times

[gott]

Hello. I have a question for your expertise.

I currently have a mysql database with two tables, one with static information about tanks that will always stay the same (height, diameter, capacity, etc.) and another table with dynamic information that will change regularly (levels, products inside), approximately 3 times a day. at 7, 3 and 11. My question is how do I make the dynamic table work with dates and times? Is it better to have one giant table or generate a new one for every date and time. Also, what would be the best PHP functions to use to do the dates and times.

Also, I am using the tank number (varchar) as the primary key for each database as of right now.

Thank you for your help.

[pnj]

Do you need to record all the changing information about the tanks, i.e. if it is 11 now, do you care what the values were at 3pm?  If you don't care, one table will suffice and will always hold the latest values.  If you need to record everything, you should have a second table that looks something like:

[tank_num] [levels] [products] etc.

in a one to many relationship with the main tanks table.

If the tank number is a physical thing linked to an actual number, you might consider using a separate internal autoincrement primary key - in my experience when I've used real information as a primary key, I've gotten into trouble, for instance if the format of tank numbers changes, or if you need to change numbers around.  But others may disagree.

For php date functions: [url=http://php.net/date]http://php.net/date[/url].

Also see mysql's date handling, which can save some programming work: [url=http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html]http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html[/url]

-pnj

[fenway]

IMHO, a surrogate PK that is an integer always works better (at least for MyISAM tables were it doesn't matter).  MySQL does an excellent job of handling date/times, and there's nothing wrong with another table of "old" information that is timestamped.