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:list_php@stripped]
Sent: Tuesday, April 12, 2005 8:59 AM
To: mysql@stripped
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/mysql?unsub=1
