Dave Goodchild wrote: > Hi all, I am in the process of creating a national events directory where > people can enter their events (car boot sales, evening classes etc) and > specify whether those events are one-ff events or repeating (daily, weekly > etc) affairs and people can search for those events by postcode, date > range, > category etc. > > I have a table containing all dates between Oct 1 2006 and 2030, including Dave, if you are starting a calendar/event DB with a table full of dates something is probably wrong. you only need to store dates for actual events (in theory). > leap years etc. I am pretty new to relational design so here's my question: > > I have an events table and a dates table. As an event can happen on many > dates and a date can hold many events, I created an intermediary table > called dates_events to express that many-to-many relationship. The data > entry works like a dream as does the search. > > However, the dates_events table is growing quite large (200,000 mapped > relationships for 300+ test events), but the logic I used seemed clear. > When > the system goes live, allowing for a sweeper script that removes outdated > mappings from all three tables, I estimate that this mapping table may grow > to 2-3 million records at least. asuming you tables are correctly indexes and the relations are correctly defined record count is not the limiting factor at all. it does sound like you have one table too many. a one to many relationship between an events table and a dates table should suffice. then again your denormalized design may allow for much faster data retrieval, in which case stick with it :-) only one way to find out though. > > Does this sound flawed, and will mysql handle this kind of data volume? yup :-) use InnoDB as the storage format and even the 4Gig limit is history. > Anyone have any experience building a similar system. I am happy to use > this > method for the initial test run but may re-engineer it before we go > national > if suggestions lead me that way. > > I am unable to release the URL as yet. > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
