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 [EMAIL PROTECTED]
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
