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

Chris Dempsey evolt at cubeit.co.uk
Thu May 17 04:36:33 CDT 2007


Hi Ron,

> Earlier it looked like your event table had these fields:

	Story_id
	Entity_id
	<snip>

Correct, each event table currently contains those fields.

> Okay, so the 'entity_id' field looks like it ties the event back to the 
> specific golf course / restaurant, etc. 
Also correct.

> and you have their specific details in your 'entity' table.  
Yup.  Although there is a different table for each type of 'entity'. Eg.
golf courses store scorecard details but restaurants do not hence separate
tables.

> I assume that is where you link back to your "location" information to 
> aggregate things by region (or, in the future, by country)?  
Yes, we know what 'entity' owns each 'event' therefore we can establish
which region each event is in using the location info stored for each
'entity'.

> One alternative [TIMTOWTDI, of course] would be to have a location field 
> of some sort in the event table itself.  
This is certainly an option.  I will have a think about whether this is
required though as to add a location to each event we need some code to
figure which location the related entity is in at the time of adding the
event.  On the flip side we could just run the same code as a reverse lookup
on the events table as we always know which 'entity' it belongs to and
therefore which location it's in... I guess the first option may be the
better as it involves running the code once only, not every time.

> 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.)
In this case the 'event' would be owned by either a Region or Country. My
fault this wasn't clear before - there is also a table for events owned by
location [where location is country, region or town].

> I assume the 'story_id' links back to some kind of press release kind of 
> information?  
Nope, story_id is actually the unique id given to each record in the event
tables.  Structure of the database was designed by a previous employee and
his naming conventions could have been slightly better.

> 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?
Yeah, if we were associating multiple stories or reports with each event I'd
put them into a separate table and link them back to the main event entry.

>>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!  ;-)
Uhoh, now I know I'm in over my head - this is a bit different to the
ASP/Access solution driving a site for a friendly little local company I'm
used to ;p

> Well, congrats for getting folks tied in.  Seriously.  That's a good thing

> and a very hard thing to do.
Yeah, there was a lot of work put in before I started here and it's starting
to pay off a little now.  We do have a big fat carrot to entice people in
though.

> 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... Hope you 
> have tech support bandwidth to help these folks.
We currently maintain 100% of the websites involved so the only cost would
hopefully be our time.  One of the products we have allows different domains
to point to the same server and depending on the domain the user comes in on
a different entity is shown as a standalone website.  Other sites use the
database and our code to extract the relevant info.  The code is 90% the
same on each different site ie. core elements plus maybe a couple of extra
pages.

> 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.
Yeah, each website hits directly on the database.  

Each user has their own access, mostly through the central control panel,
which only lets them update their own 'entity' information.  The system
records the last login time for each user.  This wouldn't be much use
determining who is the most active user though as it doesn't count actual
logins, only date/dime of the most recent.

> 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 
<snip>
Yeah, I got some forward planning to do eh?


>>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. ;-)  
Yup, I like central control as well - security, maintenance and upgrades to
only one admin panel etc.


>>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.
At the moment it's another country we want to add which would have all the
same time entity types under it each with the same fields to contain the
information.  Language conversion is something I've not considered yet.

Thanks for helping me think a little deeper about the issues involved.  

-----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 20:23
To: thelist at lists.evolt.org
Subject: Re: [thelist] MySQL Select from multiple tables - have I
gotitright?

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:

	Story_id
	Entity_id
	Title
	Description
	Date_from
	Date_to
	Times
	Contact
	Booking

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.

HTH,
RonL.

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

-- 

* * 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 ! 




More information about the thelist mailing list