Design of the table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ongray
Forum Newbie
Posts: 14
Joined: Wed Sep 01, 2004 1:08 am

Design of the table

Post 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?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

First question: what happens with years that have 366 days?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

what ever you do, make sure you store it in a proper format
ongray
Forum Newbie
Posts: 14
Joined: Wed Sep 01, 2004 1:08 am

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
Post Reply