Beginner seeking advise - Database Design

scmcscohcl

Hello Gurus,
I would like to know views of PHP gurus on this scenario.

Goal is to track disk suage of netapp box at three sites. Data is collected on disk usage of each netapp box on daily basis for each site. Data has these details :

Month | date | disk space used

How should I organize my database and tables in this case ?

Thanks In Advance,
RAM

eoghain

If these are the only 3 data points that you are gathering I'd go with just 2 tables. The first being a list of the machines, just incase you add more in the future, and the second being a table to store this data.

machines
--------------
machine_id int not null primary key auto_increment
name varchar(50)
location varchar(50)
admin varchar(50)

// The machines table could link to a locations table and
// and admins table if you want/need to store more informaiton
// about these items, like the physical location, or the admins
// email, phone, pager, cell, fax, etc..

disk_usage
---------------
disk_usage_id int not null primary key auto_increment
machine_id int not null foreign key
usage_date date
usage long

// Since month is a part of the date you don't really need to 
// store it separately just let the query do the work.
// Also you'll have to figure out how you want to store your usage
// statistics as bytes/megabytes/gigabytes/blocks whatever.

scmcscohcl

Hi ,
Thanks for your suggestion.

I had planned to use four tables ; one table to store machine and location ; other three to store disk usage data for each machine.

From your reply I observe that second table will store data for all three machines. In this case I think disk usage data will be stored in an mixed fashion i.e first set of x values for x machines ; then this set would be repeated.

And to make data retrieval easier you had suggested using foreign keys. But Mysql version I am working (3.22.32) seems won't accept foreign keys in create table statement.

This is error message from create table statement :

create table disk_usage
-> (
-> disk_usage_id int not null primary key auto_increment,
-> filer_id int not null foreign key,
-> disk_usage_date date,
-> disk_usage long
-> );
ERROR 1064: You have an error in your SQL syntax near 'foreign key,
disk_usage_date date,
disk_usage long
)' at line 4

Do u have any suggestions ?

Thanks
RAM

eoghain

MySQL doesn't support foreign keys as a database construct. All you need to do is code that the primary key from one table resides in the other so that you can get the original record. This is what a foreign key is, but MySQL won't do the management of this key that another database would (i.e. making sure you don't enter a value that doesn't exist in the parent table).

Other than that you seem to understand what I was talking about perfectly. When you need to get data on a specific machine you just query the usage table using the machine id.

jlpierce

I agree, I feel that it should be up to the programmer to verify and validate any data prior to submitting it to the db server. This makes for more readable program code and relieves some of the burden from the db server.