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

Luther, Ron Ron.Luther at hp.com
Wed May 16 14:22:45 CDT 2007

Chris Dempsey provided details!!

>>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.

Hi Chris,

Earlier it looked like your event table had these fields:


Okay, so the 'entity_id' field looks like it ties the event back to the
specific golf course / restaurant, etc. and you have their specific
details in your 'entity' table.  I assume that is where you link back to
your "location" information to aggregate things by region (or, in the
future, by country)?  So if the Tucson Museum of Natural History were
organizing an archealogical 'dig' vacation in Egypt, then that would
show up as an 'Arizona' event, right?  One alternative [TIMTOWTDI, of
course] would be to have a location field of some sort in the event
table itself.  

Another idea might be to set up 'generic / dummy' entity entries to get
things aligned the way you want.  E.g. You might want to show an event
record for something like the Houston Grand Prix - which isn't sponsored
by one of the local golf courses using your service now.  (Not clear how
you handle tourism events not sponsored by your local 'entity' folks.)

I assume the 'story_id' links back to some kind of press release kind of
information?  Without knowing more I think I might have been tempted to
put an 'event id' here and link back to that from multiple stories in
the press release table.  I'm not sure what you do now if you have
several "stories" each relating back to some really big event ... Say 10
of your restaurants are each planning to have a presence at the State
Fair - who gets the 'story_id' link on the event record?

>>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.  

;-)  Oh Dude!  Wonderful!  This should be HUGE fun!  ;-)

Well, congrats for getting folks tied in.  Seriously.  That's a good
thing and a very hard thing to do.

Unfortunately, if you are really letting these folks hit your db
directly ... then I suspect you are in for a very bumpy ride!  Grandma's
B&B site has no full time dba.  If you change that table structure and
break grandma's site it's going to cost her cold hard cash to get it
working again.  *That* should play well in the media.  Hope you have
tech support bandwidth to help these folks.

You get to coordinate and negotiate the timing of _your_ table changes
with 100 different folks with 100 different agendas and ideas on the
right way and time to do this? ... WooHoo!  Does it get better than
that?  [1]

Oh man.  The database itself is used?  The websites aren't using
'include' statements to pull in text/html sitting on your site that you
generate from your db?  Hmmmmm.  Does each user have their own profile
and id - or do you have one 'user' id that all of these folks use? ...
Cuz it would be nice if you could mine the logs to see who is using what
and get an early handle on who is gonna need the most help.  A chance to
be a bit proactive.

You may end up needing to run two parallel versions of the db while you
wean folks from one to the other.  [Or run a script to populate a
duplicate of your current design from the new tables.  Set up the 100%
centralized admin panel to run the new version and have a batch job
populate the old tables.  Start a PR campaign to get people moved to the
new stuff?  Yeah baby, Big Fun!]

>>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.

I generally prefer centralized designs for this kind of thing, but I may
just have control issues. ;-)  Two things I would mention quickly here;
(1) I think centralization would improve your overall security ... One
14 yo compromising one site with it's own admin panel could change all
event contact names to "Harry Butts" or worse to give you some PR
headaches you probably don't need.  (2) As long as sites are pulling
directly from the db itself, any structural changes impacting tables
they have access to (which need not be _all_ of the db tables) [2] would
need to be carefully 'managed'.

>>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.

All depends on what you want them to be able to do -- and in what
language you want to let them do it.

Oooops, gotta get back to work.


[1] Actually that seems vaguely reminiscent of a lot of meetings I sit
through now on the 'new' data warehouse.

[2] Crossing my fingers here but I'm pretty sure you could use a
'staged' design; let an entity input to table "event_unapproved" (for
non-centralized admin panel entries), then use work-flow or scripts or
manual review before moving those records to the *real* database tables
that actually get used in processing.  Have some triggers or processes
copy records from the *real* database to the "user_[table]" tablespace
that the users have read-only access to.  Its very quick and off the top
of my head but I think that kind of approach may give you maximum
flexibility - you could change the *real* database design while holding
the output tables constant.  You could also add fields to the end of the
output tables w/o impacting their current code (unless they are using
the evil "select *" of course).

More information about the thelist mailing list