Hi Gordon, hi list! Thank you for your help.
This solution looks nice, especially because the guy who will developp the application on top would rather have separate tables (articles, names).
That would give a schema like:
# # 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 ;
# # 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 `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;
# --------------------------------------------------------
Each time an user wants to display all the information regarding one model, he has to retrieve all the elementsID belonging to that model and for each of that elementID looking in the databaseX table depending on the databaseName in the elements table.
The problem is that they want to reference something like 20 external DBs.
This will be available on the web, so it has to be fast enough to build the webpage for the user.
I'm just worrying about the 20 something joins that may be needed.
The huge advantage is for the search:the user knows in which external DB he wants to look, so the search will be only a query to the dedicated table (database2 for ex if the user wants to retrieve articles)
Do you think this kind of schema will be ok for the display of information?
Thank you very much for your time, Melanie
From: "Gordon" <[EMAIL PROTECTED]> To: "'mel list_php'" <[EMAIL PROTECTED]>, <mysql@lists.mysql.com> Subject: RE: design: table depending on a column Date: Tue, 12 Apr 2005 10:32:17 -0500
As long as articles.annotationID can be made distinct from names.annotationID why not use 2 left joins.
You may have to test annotationType in the select section to map the fields.
Something like the following.
SELECT elements.annotationID, CASE annotationType WHEN 'names' THEN names.name WHEN 'articles' THEN articles.title ELSE '' END AS FIELD1, CASE annotationType WHEN 'names' THEN '' WHEN 'articles' THEN articles.author ELSE '' END AS FIELD2 FROM elements LEFT JOIN articles USING (annotationID) LEFT JOIN names USING (annotationID)
-----Original Message----- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 8:59 AM To: mysql@lists.mysql.com Subject: design: table depending on a column
Hi list,
I have a design problem, I'd like to know if there is a nice way to solve it....
I have elements that can be annotated, an annotation is basic info and a
link on an other database.
For example: my element id 3, called testElement, is annotated.
the annotation depends on the foreign database, sometimes it's articles so
i'd like to have id, title, author,abstract, sometimes it's just a name so
in that case I would have id and name.In both id is the id required to find
the information in the "foreign" db.
The goal is to search for a string in these annotations and retrieve the element id. At the beginning we will know in which foreign database we want to search (articles or name) but these could be extended later on.
So my ideas:
-the trivial approach having everything in one table is not realistic because I have other attributes (elementName,elementOrigin) for each elementID that I don't want to repeat.
- having a table with elementID,annotationID and an other table with annotationID, title, author,name.... what I don't like here is having only one table for all the annotations in all the databases, if I know in which db to search merging everythin will slow down a string search
-having a table with elementID,annotationID,annotationType, and depending on
the annotationType searching in the right table: table articles
(annotationID,title, author) or table names (annotationID,name).
what I don't like in that case is that I have to retrieve the value of the
attribute annotationType and then do the search depending on that value. (is
there a way to join with a table which name would be retrieved?something like select * from elements left join (select annotationType from elements) on annotationID?)
I think the second solution is much slower, but it seems more clear for me.
The right way (one of the irght way!) is probably intermediate between both,
but I can't see it. I have to be careful about the design because the searches will be a lot of text, so I'd like to optimize it.
Thanks for any help,
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]
_________________________________________________________________
Stay in touch with absent friends - get MSN Messenger 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]
