The best DB strategy in my case

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!


 I would like to ask some opinions on the best DB strategy for the
following problem:
 I have a table containing information about artists (names, dates,
descriptions in three languages etc). The full-text index is put on 16
 A user can select records to have access to (like favorite artists).
 There are two possible full-text searches: through the whole table and
only within user's records.

 The queries are like:

 [1] SELECT some_fields, MATCH(index_fields) AGAINST (search_phrase) as
score FROM artists ORDER BY score LIMIT 1000


 [2] SELECT some_fields, MATCH(index_fields) AGAINST (search_phrase) as
score FROM artists WHERE artist_id IN (users_artists_ids) ORDER BY
score LIMIT 1000

 Now I would like to have a versioning capability in my system - that
is every record may have different versions.
 The main search (first query [1]) should be done only through the
versions marked as current.
 User's favorite records may be ones of current version and some that
are outdated.
 The user search (second query [2]) should go trough user's favorite

 Here are my questions (about the DB strategy):
 1) Should I keep all records (current and outdated ones) in one table.
Doesn't it have influence one the indices and therefore the score
 2) If I keep current versions in one table and other ones in a second
table I assume the main search [1] will work as it works now and I
would have to modify the second query [2] to use the JOIN statement,
 What is the best solution?

 Thank you for hints. I hope I'm clear enough.

 best regards, cyprian

Site Timeline