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?
Design of the table
Moderator: General Moderators
-
d3ad1ysp0rk
- Forum Donator
- Posts: 1661
- Joined: Mon Oct 20, 2003 8:31 pm
- Location: Maine, USA
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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.
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.
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.
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.