Many-To-Many Relationship: Use Associative Table or Delimited Values in a Column

Many-to-many relationship: use associative table or delimited values in a column?

The Document_Category table in your design is certainly the correct way to approach the problem. If it's possible, I would suggest that you educate the developers instead of coming up with a suboptimal solution (and taking a performance hit, and not having referential integrity).

Your other options may depend on the database you're using. For example, in SQL Server you can have an XML column that would allow you to store your array in a pre-defined schema and then do joins based on the contents of that field. Other database systems may have something similar.

Many-to-many relationship database, which to pick with a lot of users

Option 1. You are correct.

If you use option 2 you'll be sorry. It's denormalized. Updating those comma-separated lists of itemID values is ridiculously difficult. You'll have to use transactions to read the value string, change it, and write it back.

Also, option 1 can exploit database indexes for much more efficient searching. Ask your friend how you will find all users with itemId = 15. Ask him to write that query for you. With Option 1 you can use

  SELECT UserId
    FROM tbl
   WHERE ItemId = 15 

You can use a query to generate option 2 for display. Display is all it's good for.

     SELECT UserId, GROUP_CONCAT(DISTINCT ItemId ORDER BY ItemID) ItemId
       FROM linktable
      GROUP BY UserId

Many-to-many relationship between same table with extra column in EF

With Database-First you would create three tables, for example:

• Table Applications

  ApplicationId           int  NOT NULL PK
  ...Other columns...
  

• Table Rules

  RuleId                  int  NOT NULL PK
  ...Other columns...
  

• Table MatchingApplications

  PrimaryApplicationId    int  NOT NULL PK, FK to Applications table
  SecondaryApplicationId  int  NOT NULL PK, FK to Applications table
  MatchedRuleId           int  NOT NULL FK to Rules table
  

The double-PK in MatchingApplications is meant to be a composite key. The double-FK is meant as two separate foreign key relationships to the same table Applications. You cannot enable cascading delete for both relationships because SQL Server won't allow more than one cascading delete path between the same tables.

EF should create three entities from this database schema:

• Application entity with two collections with element type MatchingApplication
• Rule entity with one collection with element type MatchingApplication
• MatchingApplication entity with two navigation references of type Application and one navigation reference of type Rule.

Many-to-Many relationships needing an associate table

You can't make many to many relations any other way using relational databases. Ie, if you have a table called "person", you can't create a column "friends" and expect to put many friends' user ids in there. You have to make a separate table to hold the relation itself.

Storing many to many relationship objects in a column on database

Traditional database design recommends the model you describe, with the "many to many" relationship extracted into a separate table.

There are many reasons to do this - not just the "deletion" anomaly, but also performance - parsing a varchar column to extract the pattern "(*)" is significantly slower than a join between integers. Imagine the query you'd need to find all listings with "view of downtown Ankh Morpork" and "walking distance to Unseen University", but without "direct access to Fool's Guild".

Which schema does this associative table belong to?

Why are Production and Sales are in different schemas?

On the assumption that they just are, and you can't change that -- put it in whatever schema is responsible for the bigger perspective share. Meaning, if your question is usually "who is responsible for selling product x?" -- it should go into Production. If it's more of the "what product does salesperson x sell?" -- it should go into Sales.

This is kind of a screwy design imho, for the very reason of cross-schema relationships not having an obvious home (but if there are good reasons for setting things up this way, I would appreciate being enlightened).

---

Related Topics