I have four tables.
These four tables save information from 4 different user activities...so they save 4 very different data sets. (for example, one activity could be choosing different tracks to make a song, and another activity could be choosing different clipart to make a collage)
However, they have columns in common...such as 'title_of_composition', 'date_saved', 'description', and 'approved'.
I need to first do a query to list these saved records and display their 'title' and the 'date_saved'.
Then when the user clicks on one of these records, I would do another query to display all of the saved data, including activity specific data.
I was wondering which method of querying these saved records would yield the best performance.
METHOD A: I can stay with these 4 tables and use a UNION :
SELECT record_id, title_of_composition, date_saved, 'song' as data_table FROM song WHERE description like '%cat%'
UNION
SELECT record_id, title_of_composition, date_saved , 'collage' as data_table FROM collage WHERE description like '%cat%'
UNION
...etc.
Now I will get a list of all records from the 4 tables that mentions 'cat' in the description. I also have enough information (data_table, and record_id) so that I can grab all the details from the right table later.
METHOD B:
I create another table 'all_compositions' that will hold:
'title_of_composition', 'date_saved', 'description'(...also,'data_table', and 'data_table_record_id')
For the other 4 tables, I remove these columns, so they only hold the specific details of the activity.
Now when I do the first query, all I have to do is:
SELECT record_id, title_of_composition, date_saved, data_table, data_table_record_id WHERE description like '%cat%;
Now I will have the necessary information to access the details of each activity when one of these records is selected.
PERFORMANCE-wise, which is better? METHOD A using the UNION, or METHOD B, with a slight database modification.
DATABASE DESIGN-wise which is better?
Thanks! -- -James
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]