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
_________________________________________________________________ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
