Best way to update this table?

[smagdy]

HEllo,

I've table with 80 records, so i want to update the views of each of the items in these records daily... i mean everday should have all the items with number of views of that day so is there a better way than that?

Date item1 item2 item3 item4 item5 ... item80
2007-05-10 43 100 12 555
2007-05-11 354 111 22 21
2007-05-12 546 530 645 44
2007-05-13 4 92 584 99

thanks in advance

[mcibor]

I strongly advise against such usage!
what if new 20 products appear? And 10 got deleted? There is no flexibility in your table.

I suggest a simple table:

id, Date, item, views
1, 2007-05-06, item1, 20
2, 2007-05-06, item2, 4
3, 2007-05-07, item1, 5
4, 2007-05-08, item1, 40
5, 2007-05-08, item2, 30

This way you can easily add or delete any product.
The only problem I see is that you would first need to retrieve the data in order to check if you need an INSERT INTO or UPDATE.
But that goes with your first solution as well
Unless you don't bother and always INSERT INTO and then on SELECT select SUM(views)

Hope this helps you
Michal

[smagdy]

Thanks for ur advice, but what if the items will be fixed, they r not products.. they r Ad categories that users put Ads in like Cars, Electronics, Flats... so they r fixed but possible to add 1 or 2 in future...

ur way is simple but it will grow the table fast, but my way just 1 record per day but i was worried about the 80 Fields...

About the insert & delete i can setup crons to insert the days every week or month then i just update the rows...

Also the whole thing is for statistics just for me... so no users/pages will read from the table....

So what do u say now? Correct me if am seeing things wrong...

Thanks in advance!

[mcibor]

If you are worried about table size, then you can perform archivization - move the data into another, less used table.
However as you said this table is only for statistic purpose, then I wouldn't worry so much - btw table wouldn't be much bigger, but much cleaner and easier to work with.

Hope this helps
Michal

[smagdy]

Thanks and do you suggest...

Check if I need an INSERT or UPDATE
OR
I don't bother and always INSERT then SELECT SUM(views)

knowing that noone will use that table except when i want to SELECT some statistics!

Thanks

[mcibor]

with UPDATE the table will be smaller, but it will take a bit longer to update the table, however then SELECT will be faster.

But with INSERT you can retrieve info when the db was updated.
If you don't want that information, then I would recommend the UPDATE solution - it's a bit harder to code, but you will save table space.

Regards
Michal