Mysql database structure advise 2

[Dweezel]

I'm designing a site for a friend. His company has monitoring equipment which sends a csv file containing temperature readings once a day to his home computer. Each of these csv files contain 144 readings (one reading every 10 minutes for 24 hours).

Each reading consists of a number correct to 6 decimal places (e.g. 3.245354).

I need to ftp this data to a remote server and get it into a mysql database. The data will then be called from the database and displayed in php graphs using the gd library.

I'm trying to work out the best way to construct the database table to store the data. I was considering this:

year int(4) not null primary key,
month int(2) not null,
day int(2) not null,
hour int(2) not null,
minute int(2) not null

Thereby creating a new row in the table for each reading. The graphs will enable him to view data for a specific hour,day,week,month or year, so lots of sorting and calculations will be needed before passing the data to the graph generating scripts.

Do you think that the table structure above is feasable considering that a single years data will make the table over 52,000 rows long?

Also, this is to be hosted on a shared remote server, would you expect any problems with having to store, retrieve and make calculations on such large amounts of data?

 

[Dweezel]

Edit :- Obviously I'd include a field in the above table for the reading itself.

 

[cyoung]

Is there more than 1 machine that will be monitored? If so, you will need to add a field to id the machine. You may also want to setup additional indexes on the month, day and machine (if used) to make the selection of data faster.

52,000 rows per year per machine is not an issue with MySQL. Plan now for how long you will be maintaining the data and size your storage needs accordingly (I would consider using LVM on a RAID array for security and ease of increasing the storage size)

 

[Dweezel]

Thanks for the reply. Yes there is more than one machine to be monitored, but I was considering doing a seperate table for each machine.

As for the hardware on the server( I'm assuming that's what you mean by 'using LVM on a RAID array' ), it's out of my hands I'm afraid. The site will be on his own hosting account which is a standard shared server hosting package with ftp access only. Do you think this could cause problems?

 

[TonyGroves]

By MySQL standards, 50k records is nothing, there will be no problem processing that amount of data. In any event, you have no choice in the matter - one reading equals one record; there's no other reasonable way to store the data.

Regarding your record structure, MySQL has a native datatype called DATETIME which is specifically designed for storing date/time fields, and that would be the natural primary key for your table. Assuming the hardware is reasonable, you should not encounter performance problems.

If you do decide to keep the date/time elements in separate fields, then you would need to define a compound primary key referencing all those fields. You would also be free to separately index whichever of those fields would result in noticeable performance gains.

If the number of machines being monitored is fixed and unlikely to change in the medium term, then using separate tables would be feasible. However, you would have much greater flexibility and ease of administration if you lumped them all together in the one table.

 

[cyoung]

This should not be an issue if he has a good hosting company.

He will want to check and make sure that he does not go over any limits on data transfer that his host has.

As to the table structure, I would include the machine id in the same table, as it will make queries a little faster not to have to include 2 (or more) tables.

Assumuing 30 bytes per record, you are looking at using about 1.5 meg of space per machine per year, so the storage limits are very small for this application. Of course, indexes will cause this usage to double or triple, but again, this is a very small database.

I would setup the table as follows:

machine_id int(2) not null, primary
year int(4)
month int(2)
day int(2)
hour int(2)
minute int(2)
data float (6,5)

You will need to build a compound index on the year/month/date/hour/minute. You may want to consider using the DATETIME datatype as TonyGroves suggested, as this will speed up access to the data considerably.

 

[Dweezel]

Great information and tips guys, thanks a lot. I'll look into the DATETIME datatype tomorrow.