CODEWITHSUNDEEP
phpmysqlrelational-database
taseenb
taseenb

Reputation: 1443

Best MySql db design for multiple Many To Many relations

I have 6 tables in a MySql database that I need to join to each other (the 6 items must be associated in any possible way). I also need to add a "sort_order" column somehow to these relations. Using "classical ManyToMany" join tables would lead to a messy db, with tens of join tables. That would be impossible to maintain. The best solution I could find was to use a single join table with 7 columns (6 for the 6 items ids + 1 for sort_order), where every row identifies a single association between 2 tables.

Here is the join table i did:

mysql> desc relations;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| artist_id  | int(11) | YES  | MUL | NULL    |       |
| art_id     | int(11) | YES  | MUL | NULL    |       |
| edition_id | int(11) | YES  | MUL | NULL    |       |
| expo_id    | int(11) | YES  | MUL | NULL    |       |
| news_id    | int(11) | YES  | MUL | NULL    |       |
| press_id   | int(11) | YES  | MUL | NULL    |       |
| sort_order | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
7 rows in set (0,00 sec)

Since I am not an expert of database designs, I guess there may be better designs to achieve the same goal. What would be the best design?

Note 1: Please do not tell me that I should avoid too many association: this is a particular and precise need of the project and I just have to find the best solution to make it possible.

Note 2: By the way, this is for a Php/MySql web application and I am using Yii as framework.

Upvotes: 3

Views: 1913

Answers (2)

Halil Özgür
Halil Özgür

Reputation: 15945

  1. Joins are not bad if done correctly (e.g. normalization) in performance and maintenance-wise.
  2. Only de-normalize when the needs actually arise.
  3. Follow the most possibly correct (and working!) path in the beginning, don't worry about performance or other things.
  4. Premature Optimization is said to be the mother of all evil.
  5. Add appropriate indexes to foreign keys (MySQL automatically covers this) and to the fields that are used in WHERE, ON, ORDER BY etc.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108676

Please don't do what you propose. You are correct: You'll have lots of join tables if you have lots of many-to-many relationships between your entities. But that's a far better solution than the one you propose. Seriously.

It will perform much better because you won't have any null id values in your join tables, which means that you'll get the full benefit of indexing.

Your entity relationships will be easier to update and troubleshoot.

Whoever maintains your code after you've moved on to something else won't curse your name. In other words, you will have used conventional dbms design techniques to solve a conventional problem, and your design will be transparent.

There are lots of schema design tools that can help you generate the DDL for all these join tables. Hint: make sure your id columns are named uniformly throughout. That is, call it artist_id in the artist table, and in the artist_art table and in the artist_edition table. If you do this, the schema design tools can automatically sort out what you're doing.

Upvotes: 2

Related Questions