Search code examples
mysqldatabase-designmany-to-many

Is it possible to design a generic many-to-many junction table?

Let's say I want to design a naive application, which has the following three tables:

basic

There are two many-to-many relationship:

  1. A customer may has many favourite avatars, an avatar may be used by many customers;
  2. A product may has many product images, a product image may be used by many products;

So we could add two junction tables to accomplish the relationship above:

junction-table

The customer_image table closely resembles the product_image table, is it possible to create a generic junction table like the following?

enter image description here

As the generic_map table will be used by different models (customer, product and etc), I removed the foreign key constraints.

Any suggestion? thanks.

Solution

  • It's not clear why you would want to do this. What benefit does it give you over creating a separate table for each many-to-many relationship?

    It also creates a lot of unnecessary complexity.

    Constraints

    The fact that you must remove the foreign key constraints to make your table work should be a strong clue that this is a bad strategy.

    Duplicates

    So if you do a join from the image table to the generic_map.to_id and find the image maps to a given value for from_id, such as 1234, how would you know if this value references a customer with id 1234, or a product with id 1234?

    Normally a many-to-many mapping table has a UNIQUE constraint on the pair of columns that reference each entity. But in your generic_map can you have such a constraint?

    What if both customer 1234 and product 1234 each want to map to the same image? Do you store the mapping row twice in the generic_map table? If so, what happens when you join from customer through generic_map to image? You'd get duplicates from the join.

    Also, what if just the customer 1234 wants to reference a specific image, but the product with the same id 1234 does not want that image? How would you make it clear that the product 1234 should not join to that row in the generic_map table?

    So you commented:

    I forgot to declare a type column in the generic_table, the type column is of type ENUM('customer', 'product'). Via this column, I could know a generic.from_id value of 1234 means customer id or product id.

    But this leads into another problem...

    Queries

    You must remember to put conditions on the extra type column on every query you run:

    SELECT ...
FROM customer AS c
JOIN generic_map AS m ON c.id=m.from_id AND m.type='customer'
JOIN image AS i ON m.to_id

    If you (or someone else on your team who maintains this code) forgets to include this condition, then you will get the strange effects I described above.

    Performance

    Queries against smaller tables are usually faster. But now you are guaranteed to have a large table.

    Suppose if you had separate tables, your customer_image table had 10,000,000 rows. Your product_image table had 1,000 rows. You should be concerned that your query for product image needs to search through 10,001,000 rows instead of 1,000 rows. Indexes can help, but it would be even better for performance if the tables were separate.

    The above are just a few problems. They keep unraveling like a loose thread.

    Do yourself a favor — just use two tables, one for each many-to-many relationship. It will make everything easier.