Hi,
Since I am new to the DB world, here is my Ojective:

I have to design a database, that is up 27 x 7 x 365(pretty much!)
Every hour it logs data which accumulates to about 27K rows.
So, my question is, If I have just a Table ( with 5 coloumns), will I overflow very soon ?
Is there a better way, to design the tables? Say, create a Table @ day?
Currenty, in my design I have 3 tables, each having about 5 columns.
Atleast 1 table will size about to 27K rows of data at the end of one day.

I have here with me a Windows 2K mcahine ( 1.5GHz Pentium), 1GB Ram, 40GB HardDisk. I am thinking about RAID too. and also possibly SCSI disks to improve performance (if necessary ).
Database to Use: mySql (since its FREE!!!!!!!)

Pls feel free to comment and add to the above,
Thanks,

Deepak Ram

Deepak,

I'd keep all of the data in one table. Splitting the data into different tables on a periodic basis is a headache when it comes time to report on the data, unless the system you're using supports partitioning - I don't think MySQL does. Still, you're only talking about less than 10 million records per year which isn't a heck of a lot.

27,000 records a day isn't much in terms of transaction volume. One record every 3.2 seconds won't put a significant strain on the computer hardware you've described. I use MySQL at home and I've seen it handle inserts and updates of 3,000 records per second without any noticeable performance problems. RAID is probably overkill unless you've got multiple users accessing the data in the table on a frequent basis. What kind of reporting will be done off of the table?

The precise storage space required depends on a number of factors: the page size used by your OS, record overhead (if any), index overhead, fill percentages of each page are a few that come to mind.

In order to determine the general storage requirements for the table you need to estimate the width of each record and any associated indexes. What datatypes are you using for the columns in the table? Date, integer, string, etc. If you know those you can add them up. The record could be narrow, could be wide, might have a VARCHAR field, in which case you'd have to estimate the average size of each VARCHAR column, etc. Multiply the expected record width times the number of records and you'll have a rough idea of your table size in bytes. Divide by 1,048,576 for an answer in MB.

Also, how many indexes will be built on this table? You can use the same method to estimate index size: number of bytes in the columns being indexed times number of records. Overhead and storage requirements differ for clustered vs. non-clustered indexes and are noted in the MySQL documentation.

Hope this help.

--
TCHM

cool [cool] explanation Thomas!