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]
