MySQL Database - Related Results from same table / Many to Many database design problem

Question

Hello, I am designing a relational database of products where there are products that are copies/bootlegs of each other, and I'd like to be able to show that through the system.

So initially during my first draft, I had a field in products called " isacopyof " thinking to just list a comma delimited list of productIDs that are copies of the current product.

Obviously once I started implementing, that wasn't going to work out.

So far, most many-to-many relationship solutions revolve around an associative table listing related id from table A and related id from table B. That works, but my situation involves related items from the SAME table of products...

How can I build a solution around that ? Or maybe I am thinking in the wrong direction ?

Answer 1

Because something is a copy, that means you have a parent and child relationship... Hierarchical data.

You're on the right track for the data you want to model. Rather than have a separate table to hold the relationship, you can add a column to the existing table to hold the parent_id value--the primary key value indicating the parent to the current record. This is an excellent read about handling hierarchical data in MySQL...

Sadly, MySQL doesn't have hierarchical query syntax, which for things like these I highly recommend looking at those that do:

• PostgreSQL (free)
• SQL Server (Express is free)
• Oracle (Express is also free)

Answer 2

You're overthinking.

If you have a products table with a productid key, you can have a clones table with productid1 and productid2 fields mapping from products to products and a multi-key on both fields. No issue, and it's still 3NF.

Answer 3

There's no reason you can't have links to the same product table in your 'links' table.

There are a few ways to do this, but a basic design might simply be 2 columns:

ProductID1, ProductID2

Where both these columns link back to ProductID in your product table. If you know which is the 'real' product and which is the copy, you might have logic/constraints which place the 'real' productID in ProductID1 and the 'copy' productID in ProductID2.