"mel list_php" <[EMAIL PROTECTED]> wrote on 04/13/2005 05:43:07 AM:
> 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 > > 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
