[thelist] MySQL Select from multiple tables - have I gotitright?

[Chris Dempsey]

Hi Ron,

[snip]
we really don't know very much about what Chris is trying to do 
[/snip]

I'll try to summarise the objective as the reason for asking about a single
table option was an effort to future proof the system.

The database stores information on different types of tourism related
entities [hotels, restaurants, regions, attractions, golf courses etc.].  

Each entity has a table of its own to store specific details eg. restaurants
have an 'opening hours' field but this field is not required by a region.

Each entity [or most entities] can have events associated with them eg. a
restaurant can have a 'Mother's Day Dinner' and a Golf Course could have an
'Annual Tournament'.

Each event entry at this time stores the same details [Title, Description,
Date_from, Date_to, Times, Contact, Booking].

I know we need to add an 'Event Category' field [Golfing, Food & Drink,
Walking, Museums & The Arts] but I can't see the events table expanding much
further as we only need to store an overview of the event.

The database is used by around 100 websites which draw different bits of
information that are specific to what they require - anything from a
regional portal displaying every item from each entity located in that
region to a small website displaying only the information for an individual
hotel.  

The website is 95% administered from a central control panel but a few
websites have their own admin panel.  This is something I intend to
centralize 100% as any structural changes to the database would then impact
on every admin panel.

There is a requirement for the database to handle entities in different
countries now but I mention this only to demonstrate the expansion of the
system.

Is there any further information you need to be able to comment further on
whether the single table for events looks like the correct solution?

Thanks,

Chris.


-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org] On Behalf Of Luther, Ron
Sent: 16 May 2007 16:51
To: thelist at lists.evolt.org
Subject: Re: [thelist] MySQL Select from multiple tables - have I
gotitright?

Joel D Canfield noted:

> single table and added a field for 'event_category' rather than having
5 individual tables.

>>me too :) - at least then, if you add a new event category, you're
just 
>>adding data to that table, not an entirely new table.


Hi Joel,

I'm kind of waffling.

I agree that if Chris has five tables with identical structures he
should be able to collapse those into a single table and that the single
table would be easier to work with.

However ... we really don't know very much about what Chris is trying to
do.  Nor do we know what these 'event categories' are.  If the events
are disparate enough then perhaps they ought to have different table
structures. [1]

With the limited scope of information provided I think the answer is
good.  Just noting that what we are suggesting here may turn out to be a
temporary or a suboptimal solution that eventually paints Chris into a
corner.  'Quick & Dirty' database design often leads to considerable
time and pain later.

Cautiously,
RonL.

[1] Being the 'imaginative' sort, I imagined Chris's "events" to be
various fundraisers all supporting a common charity.  A band plays a pro
bono dance.  A multi-site softball tournament with entry fees is held.
A magician gives numerous performances during a community bazaar.  A
multi-week 'football pool' event is also held.  Etc.  A single table (or
a single table structure) may not be the best way to
capture/report/present information for each or all of these individual
events.
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !