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
Yes, all the information will be stored in mysql.
You are right for the db structure, except that in my case my elements have only the annotation property so I merged them into one table.
Here are the tables:
#
# Table structure for table `database1`
#
CREATE TABLE `database1` ( `databaseId` int(11) NOT NULL default '0', `name` varchar(250) NOT NULL default '', PRIMARY KEY (`databaseId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# --------------------------------------------------------
# # Table structure for table `database2` #
CREATE TABLE `database2` (
`databaseId` mediumint(11) NOT NULL default '0',
`title` varchar(250) NOT NULL default '',
`author` varchar(250) NOT NULL default '',
`date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`databaseId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
# --------------------------------------------------------
# # Table structure for table `element` #
CREATE TABLE `element` ( `elementId` int(11) NOT NULL auto_increment, `modelId` int(11) NOT NULL default '0', `databaseName` varchar(50) NOT NULL default '', `annotationID` int(11) NOT NULL default '0', PRIMARY KEY (`elementId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
# --------------------------------------------------------
# # Table structure for table `model` #
CREATE TABLE `model` ( `modelId` int(11) NOT NULL auto_increment, `modelName` varchar(250) NOT NULL default '', PRIMARY KEY (`modelId`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
I don't have any data sample sorry....
I think having one table for the external databases won't be a good idea because the data are heterogeneous and if we decide to add a completly different db we will have to modify the structure.
And my programmer definitly prefer the "exploded" version, because one of the main purpose will be to retrieve a model depending on his annotation, for example search all in the table database1 where name like '%name%', and he will know in which table he wants to search.
I was just wondering if there was a way to improve the join, because some of the guys working on one of the external db had the same problem and they arranged to link directly what is the equivalent of my modelId with annotationID in some kind of index...but as they are using oracle this is maybe a specificity of oracle.
I will keep the above structure and test it, and I think it will be the definitive one unless big surprise!
Thank you very much, you're always very helpful (and active on the list!!)
Melanie
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
