How many tables? Opinions please!

[Conteur]

Hi,
I am administering a web site for my wife's church organization. The site displays a calendar listing the names of volunteers for each day of the month. I started by creating each calendar page in HTML. After a while that got old, so I wrote a C++ program to generate the HTML code automatically reading the data from a flat file. A far better solution would be to set up a database and host the site using PHP. One table would contain the list of volunteers and their pertinent information. Another table would be created listing the volunteers and the dates that they work on. Here is the meat of the question. I still have to create the HTML for the calendar, so I thought that I could create a table for each month of the year. Okay, after 5 years I will have 62 tables, 10 years - 122 tables. I know that I can keep all the data in a single table, but it seems more logical to me to keep each month separately. Is there a practical limit to the number of tables that a database should contain? Any ideas or suggestions would be greatly appreciated.
Thanks,
Conteur

[php_brian]

What exactly are you doing with all those tables? What is being stored in them? You already have the one table that contains the volunteers and the dates they work on. That table should be all you need to organize the volunteers on specific dates (month, day and year).

[gish]

"table for each month of the year"

Exactly what php_brian said, you dont even need one calander table.

tblvolunteers
--------------------
ID
FName
LName


tblVolSchedule
-------------------
ID
VolID
Day
Month


Now create a script that "creates" the look of the calendar and in a SQL query display the names on the appropriate dates.

[Conteur]

I guess I didn't make myself clear. Of course I can write code to create the calendar. I have already done that. All I have to do is convert my C++ to PHP. My question is what is the practicality of storing calendar information in multiple tables? I was soliciting ideas, not flames. If you think about it, a calendar is a tabular representation of the days of a month.

[php_brian]

To answer your exact question: it is not practical. Now, to help you develop this calendar program, which I have done with XML, but easier with a database. Simply have PHP generate the calendar and as it loops over each day of the month, query the database that contains the events and select out the events for that day.

[stuka]

The theory behind this is simple: a database should have a fixed number of tables (as long as the basic schema of the db isn't changing). What IS supposed to change is the number of ROWS in each table. The schema gish proposed, while minimalist, is definitely the best place to start your setup. Also, just as an aside, is there any reason you couldn't just convert your C++ code to access the database and output the HTML? Would save conversion to PHP, and the program doesn't have to be highly real-time, I'm guessing (and even if it was, the C++ could be made into a CGI app and be quicker than PHP anyway).

[Conteur]

Thanks Stuka. In fact, that is my plan. This whole thing was meant to be a learning experience. First converting from the flat file to the database, then creating a dynamic web site using PHP. This way I could create an interface where my wife could just update the database to keep the caledar up to date.

[darelf]

Umm.... I believe iCalendar would be a good format rather than an actual database.

Then you could use php to parse the iCalendar and display whatever date/month/whatever that was interesting to the person looking at the site.

iCalendar is designed specifically to store... get this... calendar/event information.... yeah, I know, a shocker. And it is a standard. ( http://www.ietf.org/rfc/rfc2445.txt )

And there are many calendar tools that can export iCalendar format. iCal by Apple, Mozilla calendar, Outlook, etc.

I like Firefox (Mozilla) with Mozilla Calendar myself, but there are lots of tools.

And there are one or two already written PHP code blocks to view iCalendar data. ( http://phpicalendar.sourceforge.net/nuke/ )

Check it out.