wwwmaster wrote:
Quote:
|
There must be a better way, because It is never easy or simple for me to add or remove matchups because I always have to check both fields.
|
I am confused here. Why must you check both fields? If you name the columns of the table from the original:
Code:
PRODUCTMATCH Table
Product_ID_1 PFK
Product_ID_2 PFK
to:
Code:
ProductMatch Table
product_id PFK
matching_product_id PFK
Now, you only have to get the second column when the first one is given.
Of course, if Product A matches Product B, and this always
implies that Product B matches Product A, then you must have two rows in the current version. One row will be (ProductA, ProductB) and the other row will be (ProductB,ProductA).
If you want to avoid having two rows, you can create a view on this table that uses only one row to create the illusion of two.
The view could be based on something like this:
Code:
Select product_id, matching_product_id from Product_Match
Union
Select matching_product_id, product_id from Product_Match
This way, your queries are simple, there is no need to have two rows for matched product pairs.
The cost you pay for this is in defining the view, and slightly slower times querying the view.
Depending on the number of products and the number of matched products, this may not even prove to be a problem.
Hope that helps.
Ravi
dBforums
>
Entity relationship with itself (match-up)
12-30-04, 13:02
Linear Mode
