Hi all.
I have this kind of problem setup:
1. I have a bunch of records and I need to store some record specific data
about each of them (date, owner etc).
2. I have a list of categories (=groups) in which a record may belong to and
I have to be able to store some group specific data (name, code, is group
shown etc) for each group.
3. One record can be in multiple groups and a single group can contain
multiple records (n..n relation)
The question is:
Which is the best database structure for this kind of situation that enables
me to _efficiently_ find records by groups using various conditions and
MySQL 4.0.15?
For example I should be able to find all records that belong to group 1 and
2 and to one of the groups 3,4 and 5. (1 AND 2 AND (3 OR 4 OR 5)).
Currently I have this kind of structure:
Table record:
+------------+---------+---------------------+
| archive_id | creator | created_time |
+------------+---------+---------------------+
| 1 | 1 | 2003-10-10 21:47:35 |
| 2 | 2 | 2003-10-10 21:47:35 |
| 3 | 2 | 2003-10-10 21:47:35 |
| 4 | 3 | 2003-10-10 21:47:35 |
+------------+---------+---------------------+
Table group:
+----+----------------------------------+-------+
| id | name | shown |
+----+----------------------------------+-------+
| 1 | Group name 1 | 1 |
| 2 | Group name 2 | 1 |
| 3 | Group name 3 | 1 |
| 4 | Group name 4 | 1 |
| 5 | Group name 5 | 1 |
+----+----------------------------------+-------+
Table link:
+------------+----------+
| archive_id | group_id |
+------------+----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 3 | 3 |
+------------+----------+
This seems logical structure for me. The problem arises when I try to make
queries to the structure.
The best way I have figured out to find records by groups is to perform a
query like this:
SELECT link1.archive_id FROM link AS link1, link AS link2 WHERE
link1.group_id=1 AND link2.group_id=2 AND link1.archive_id=link2.archive_id;
The query above gives me the archive_id 1 as it should. (Query means that I
want to find all records that belong to groups 1 and 2).
As you can easily imagine, when there is a lot of both records and groups
and when searching conditions are a bit more complicated than in my example
query, the join that is performed is pretty enormous. And the query string
is huge too (and even worse hard to construct programmatically)
So I'm asking is there any better way to either construct the database
tables or to perform searching queries?
Sincerely,
Jouni Hartikainen
kalle.kukkanen@stripped
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
