Is this correct database design?

[code green]

I wasn't sure what title to give this, or whether I can explain my dilemma, but here goes.
My company sells products that are inter-compatible with each other, mainly four ranges I will call A,B,C,D.
A key table in the database is `product_compat ability` with two columns listing product IDs that are compatible with each other.. At the moment it lists the compatibility between A-B with A in col 1 and B in col 2. Straightforward .
This must now be expanded, with possible combinations of A-B; A-C; A-D; B-C; B-B; B-D; but no possible match C-D.
The dilemma now is the rule of A in column 1 and B in column 2 breaks down, as B can be in either and BOTH. Thereby complicating queries SELECT INSERT and UPDATE.
Our answer to avoid UNION the table with itself in every query is to double the size of the table and mirror each record with itself so it does not matter about the column order. The table goes from 30000 records to 60000 but queries are less complicated.
I sit uncomfortable with this. In effect every record is duplicated but in reverse order. However queries should be faster, I think!
Is this correct database design or is a rethink in order?

[Atli]

Ok, Im trying to understand this but there is one thing that's bothering me. You list all possible combinations of compatible products, but you include 'B-B'.
How does that make sence?

[code green]

I admit this is confusing.
The letters represent a class of products, not individual products..
And the combination B-B means the two parts are interchangeable rather than compatible with each other.

[Atli]

Ok,
So, assuming that each product cluster has a auto_increment primary key, you could create a rule that states that the smaller ID should always be placed in the first column, and the larger in the second one.
(This rule could be adjusted to any primary key, like say in alphabetic order if that would fit your primary keys better.)

But lets assume for now, that I have a table, called 'ordTest' that was created like this:

CREATE TABLE ordTest
(
  val1 BIGINT NOT NULL,
  val2 BIGINT NOT NULL,
  PRIMARY KEY(val1, val2)
)
 

Then you could create a procedure that would insert into that table, putting the smaller ID into the first column and the bigger into the second.

delimiter $
DROP PROCEDURE IF EXISTS InsertCompatible$
CREATE PROCEDURE InsertCompatible(x BIGINT, y BIGINT)
BEGIN
  IF x < y THEN
    INSERT INTO ordTest(val1, val2) VALUES(x, y);
  ELSE
    INSERT INTO ordTest(val1, val2) VALUES(y, x);
  END IF;
END $
delimiter ;
 

Then you can create another procedure to check wheter any given ID numbers are compatible

delimiter $
DROP PROCEDURE IF EXISTS IsCompatible$
CREATE PROCEDURE IsCompatible(xID BIGINT, yID BIGINT)
BEGIN
  DECLARE x INT;
  IF xID < yID THEN
    SELECT COUNT(*) INTO x FROM ordTest WHERE val1 = xID AND val2 = yID;
  ELSE
    SELECT COUNT(*) INTO x FROM ordTest WHERE val1 = yID AND val2 = xID;
  END IF;
  SELECT x != 0 AS 'RESULT';
END $
delimiter ;
 

Now, all you have to do to insert compatible products classes into the table is call the first procedure, like this:

CALL InsertCompatible(1, 2);
 

Note, that because of the two columns both acting as Primary Key's, you can not insert duplicate products using this procedure.

And to check if any two ID's are compatible you can call the second procedure:

CALL IsCompatible(1, 2);
 

Which would output a table with a singe col called 'RESULT' and contain a single row with a boolean value, indicating whether the ID's match.

[code green]

Thanks Atli,
I'll study this and reply next couple of days.
But if I am doing a select,update or delete on a particular ID,
then will both fields be included in every query with a self join or a union?.

[Atli]

Thanks Atli,
I'll study this and reply next couple of days.
But if I am doing a select,update or delete on a particular ID,
then will both fields be included in every query with a self join or a union?.

I'm not entirly sure what you mean.
The table works just as any other table, except both fields are joined to form a primary key.
You can select or modify them seperately as you wish, as long as neither of them ends up empty and when they are combined, they must be unique.

[code green]

It is not uniqueness of table entries that concern me, as both fields are set up as primary keys. It is a balance of performance and memory usage. Do I go for this structure

product_id    compatible_product_id
    101                  303
    101                  307
    102                  305
    102                  303
    303                  307

Or this structure

product_id    compatible_product_id
    101                  303
    101                  307
    102                  305
    102                  303
    303                  102                  
    303                  101                  
    307                  101                  
    305                  102 
    303                  307
    307                  303

The first LOOKS more efficient until you start to write queries, for example to SELECT all products compatible with id 303. The current queries will still work with the second design but need rewriting to check both fields in the first.
The second design does not need any stored procedure or knowledge of any order of INSERT rules. But INSERTs and DELETEs have to be executed twice with the second. I am simply looking for somebody's opinion

[thodos]

Since A is compatibile with B implies B is compatible with A then it doesn't matter which column holds A or B. A single row (A,B) or (B,A) or both rows implies A is compatible with B.

Atli's insert and delete procedures would eliminate duplicate entries in the table.

Since it doesn't matter which column A is in, you need to select values from either column, so the select would be something like:
select distinct ("product id" (if not :A), else "compatible product id")
from table
where "product id" = :A or "compatible product id" = :A

This should give you the other product when one is A.

[Atli]

It is not uniqueness of table entries that concern me, as both fields are set up as primary keys. It is a balance of performance and memory usage.

Ok, I see what you mean now.

I'm not sure, but I would think a more complicated query executed on x ammount of rows would run faster that a simple one on x*2 rows.

Thats mainly because queries are most often just collections of boolean expressions that MySQL executes using the data it has, and I'm pretty sure it takes more time for MySQL to fetch extra rows that it takes to execute extra boolean expressions.

It would probbly depend on you queries, so you should really test both methods using random data and compair the results.

[code green]

Thanks for the replies.
This is just one table of about 50 in the whole database.
This query looks simple

select distinct ("product id" (if not :A), else "compatible product id")
from table
where "product id" = :A or "compatible product id" = :A

but becomes quite complicated when other tables are JOINed to pull in a product_name or product_descrip tion for example.
And UPDATEs would be even more troublesome.
As I said, I will need little modification of my current queries if I go with the second design with the mirror data..
To be honest I was hoping somebody would tell me off for even considering one of these designs.
Oh well, i will go with the mirrored data idea, if only to save extra work on my part. Thanks again.