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]



Reply via email to