"mel list_php" <[EMAIL PROTECTED]> wrote on 04/13/2005 11:07:44 AM:
>
>
> >
> >When you mention external databses, I got curious. Do you mean
"external"
> >as in "not on that MySQL server"? Or, do you mean "external" as in
"same
> >server, different database"?
> >
> >If you are designing your site correctly, the user should know nothing
> >about your data storage. That means that the "user" never knows which
> >database to look in for details but your "application" will. I would
not
> >try to JOIN 20 tables together just to avoid writing a SQL statement in
my
> >application code. Since you say you have 20 separate classes of
> >additional (external) information, it would make better sense to me to
> >query the primary record then query the appropriate source of your
> >external information and merge the two recordsets in the applicaiton
layer
> >to produce the appropriate output. No co-mingling of data is required
> >except on the finished page. That way your "external" data can actually
> >come from ANY source (not just the same MySQL server).
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
>
>
> external means different server, different databases (actually most of
them
> are oracle).
>
> the user knows nothing about the storage, it's currently xindice and
we'd
> like to migrate to mysql.
>
> We have complex model, a model is constituted of several elements, each
of
> them can have one or several annotation. These annotation are part of
the
> model, something like externalDB=articles, accession=1234.
>
> We want to display the model and allow the user to download the xml. In
> xindice we are storing the xml directly, the queries trhough xpath are
easy
> and the display is just a call of the xml file.
> At the moment this is ok because we only have a few models, but we will
soon
> be limitated.
>
>
> If I understand you well, you suggest something like retrieving all the
> elements ID and then having a loop looking for each of them for the
complete
> info rather than joining all the tables at the beginning.
>
> The external information won't be always available/accessible (it may be
a
> database to which we have no direct access, or soe of them agreed for us
to
> interanlly retrieve the data but don't want external access, or some who
> agreed for us to have one access one time and not several ones because
their
> server wouldn't stand the charge.....in summary we can't trust the
> availability of the sources)
>
> I have to store a minimal information (the one that is part of the
model) to
> allow my user to download the model and provide a link to the complete
> ressource.(available or not, the model is still complete)
>
> So I can't get rid of the tables database1 to database20.
>
> The last solution is to display only the minimal information to the user
> (annotationID and databaseName) and if he asks for more querying the
> dedicated table.
> But I think there should be a way to arrange it to display the complete
> information from the beginning?
>
> Thanks for your help!
> Melanie
>
OK, just to make sure: ALL of the data actually resides within MySQL and
on the same server (regardless of it's original source)? That is good as
MySQL does not permit retrieving data from other servers in a query (yet).
So you have a table for the primary Model information, a second table for
the Elements information and a table of the Annotations of an Element and
a bunch of other tables that the Annotations information actually points
to, right?
That means your database schema looks something like this, right?
Model
|
+-Elements External Info tables
| |
+-Annotations-+
On the Annotations table are the ID of the Element it belongs to a field
that identifies what kind of annotation it is (which you can use to
identify which table of outside information you need to link to) and the
PK of the row in the correct table that contains the information in the
Annotation. Have I grasped the problem correctly?
My first thought would be to homogenize your external data into the fewest
number of tables possible (one is preferred). That means that you do more
work importing the data from your external source but it makes internal
maintenance and the queries you are trying to write much easier.
The problem is that each "kind" of annotation potentially has a different
record structure. That means you literally have up to 20 different column
formats to accommodate. Can you not keep the "raw" Annotation information
in one (or more) table(s) and put a summarized version of each annotation
into just one combined table?(In my picture above, Annotations would be a
good candidate for the summarized info table) If you need the additional
information available from the "raw" or "original" annotations, you can
make another trip to the database to get it. If you can get by with just
the summary info, so much the better.
To get a full (raw-info) results, you will need to somehow combine the
results of querying the 20 separate source tables. You can't do that
within a single UNION query unless you can make them all appear to have
the same column structure. And if you can do that, you can achieve the
single homogenized (not summarized) Annotations table I mentioned before.
Otherwise you will have to run up to 20 separate joins and use your
application's code to make the separate results appear unified to the
user.
You can look for ways to save trips to the server by consolidating several
queries to the same source table into one. If you consolidate correctly,
you will need to combine only 20 resultsets (at most). Usually you will
get away with fewer queries.
Can you provide actual table structures (SHOW CREATE TABLE xxx\G)and some
sample data for a complete record? You don't need to but it may make
things a lot easier to understand. Because this list only accepts posts up
to 30000 bytes, you may need to start a new thread to make it all fit.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
