I've got a calendar with 50 users. When a user logs on, they see their own calendar. Each day of the month queries a MySQL table to see if a row exists for that day.
I was asked to modify my code to allow people to have multiple rows for each day. This will allow them to tick each entry when completed. What’s the best way to do this?
I started off by having multiple rows in the table for each day. So if I queried 2009-5-05, it retrieved all the rows with a matching date. Bearing in mind that there are 50 users, each with maybe 3 entries per day, there could be 150 rows for every day of the year. This could mean 54,750 rows per year,
That’s a very big query so instead I tried a different approach; I now have two tables instead of one. I have one row per person in the first table. Each row has an ID. I then query this ID with another table to retrieve all the rows for that day. However, it’s occurred to me that this second table could still have 54,750 rows per year so its actually less efficient than my first idea.
Which method do you guys think is best?
