Putting dates in a generalized data field in a CMS?
Posted: Tue May 17, 2005 4:48 pm
I'm in the midst of creating my own kind-of-CMS to make a site for the village where I live, and hope to then make similar sites for neighboring villages. The site has sections for shops, hotels, non-profit orgs, sites and monuments, etc.
I have decided to generalize the data to a max so that I have just a few tables in my mysql database, and so it's easiest to handle modifications to the structure of the data. I basically have one table of types of items (restaurants, shops, touristic sites, etc.), one table of items (Joe's restaurant, Acme hardware, local museum, etc), and then one table of all of the data. In that last table, I have just three fields - the item id, the name of the data field (address, telephone, opening hours, description, etc.) and the value. This final field is text format, since some of the values are long texts.
I have this working fine, and it's now really easy to display my site sections, pages, and admin forms; queries are also quite simple. But I just hit a potential snag.
I now want to add an events calendar. Here I have of course dates, and will then want to query those dates to display, say, just last month's events, etc. But since my data value field in my 3rd table above is a text field, I'm limited in the date queries I can run since MySQL doesn't "know" that they are dates.
What's the best strategy here?
- add another table for just the date data?
- put the dates in my main data table and just try to run queries on those strings?
- do some fancy trick that I don't know about or aren't thinking of?
Anyone have any ideas to push me in the right direction?
Help?!
I have decided to generalize the data to a max so that I have just a few tables in my mysql database, and so it's easiest to handle modifications to the structure of the data. I basically have one table of types of items (restaurants, shops, touristic sites, etc.), one table of items (Joe's restaurant, Acme hardware, local museum, etc), and then one table of all of the data. In that last table, I have just three fields - the item id, the name of the data field (address, telephone, opening hours, description, etc.) and the value. This final field is text format, since some of the values are long texts.
I have this working fine, and it's now really easy to display my site sections, pages, and admin forms; queries are also quite simple. But I just hit a potential snag.
I now want to add an events calendar. Here I have of course dates, and will then want to query those dates to display, say, just last month's events, etc. But since my data value field in my 3rd table above is a text field, I'm limited in the date queries I can run since MySQL doesn't "know" that they are dates.
What's the best strategy here?
- add another table for just the date data?
- put the dates in my main data table and just try to run queries on those strings?
- do some fancy trick that I don't know about or aren't thinking of?
Anyone have any ideas to push me in the right direction?
Help?!