The best DB strategy in my case

The best DB strategy in my case

am 21.02.2006 17:53:24 von cyprian.pl

Hello,

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
fields.
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

and

[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
records.

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
(MATCH AGAINST)?
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,
right?
What is the best solution?

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

best regards, cyprian