james tu <[EMAIL PROTECTED]> wrote on 04/26/2005 12:06:34 PM: > I have four different activities. Each has its own set of data that > I want to save. So, I made four different tables to hold the saved > data. Each record also has 'keywords' field (essentially this is the > only field that all tables have in common.) > > Later on, I want to search all the keywords in these tables...and > then retrieve the saved information from the four different tables. > > Question: > Should I just search each of the tables individually? > > > Or should I create another table that will hold the keywords, the > tablename, and the ID of the saved record in that particular > table...and then perform my search on this NEW table? > > Thanks. > -- > -James >
I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', <column list> FROM running WHERE <keywords search condition> UNION SELECT 'swimming', <column list> FROM swimming WHERE <keywords search condition> UNION SELECT 'jumping', <column list> FROM jumping WHERE <keywords search condition> UNION SELECT 'walking', <column list> FROM walking WHERE <keywords search condition>; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your <column list> columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine
