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
