Page 1 of 1

Design of the table

Posted: Sun Sep 05, 2004 11:12 pm
by ongray
hi,
does anyone know if the size of column in a table will affect the database performance?

for example, if i need to keep track of the status of the machines for 365 days every year. The table can be
+-------+--------+--------+
|machin| day| year|
+-------+--------+--------+
And, I have 365 rows for each machines yearly. If i have 500 machines to manage, i will have 183k rows every year.

Should i create the table in another like this...
+-------+--------+--------+-------+--------+-- ------+-------+--------+
|machin| year| day1| day2| day3| ... ...|day364|day365|
+-------+--------+--------+-------+--------+-- ------+-------+--------+
A single row to represent a machine yearly. So, I will only have 500 rows to manage every year.

What are the pros and cons of each of the tables?

Posted: Mon Sep 06, 2004 5:07 am
by timvw
First question: what happens with years that have 366 days?

Posted: Mon Sep 06, 2004 5:12 am
by feyd
I'm pretty sure that smaller rows have less of an overall hit on the server, both memory and processing. I prefer the smaller chunks approach here.

Posted: Mon Sep 06, 2004 11:16 am
by d3ad1ysp0rk
Not sure if thats what feyd meant, but I'd go with the first one.

But we don't know WHAT this is for, so we can't help you that much.

Posted: Mon Sep 06, 2004 11:21 am
by John Cartwright
what ever you do, make sure you store it in a proper format

Posted: Tue Sep 07, 2004 8:32 pm
by ongray
Thanks for the comment and ideas.

i need to keep track a set of status of the photocopy machine daily.
the status include:-
number of paper, toner,... loaded
number of pages copied,
number of person served,...

the colunm will have 366days so that year with 366days is included.

i notice when the status of an item need to be recorded daily, it can really generate a lot of rows in the database. such as the inventory of booking for flights, hotels, car rental, ... every item will have generated 1 row
Anyone have experience designing the database like this before? your feedback will be very much appreciated.

Posted: Tue Sep 07, 2004 10:02 pm
by timvw
This week a saw a table clicks(partnercode, timestamp) grow from 0 to 54k rows. I group those rows by day/week/month to generate a graph.. And untill now i haven't noticed a performance problem.


Anyway, this is the table layout i'd suggest:

TABLE copymacines
machine_id,
date,
number of paper,
number of services ppl,
...,
PRIMARY KEY (machine_id),
INDEX (date)

If it appears that the database has problems with the 500*366 rows, you could move at anytime to tables [copymachines-year-month] This would reduce the tables to 500*31 rows.