Many to Many Relationship Question

Question

I have a database, with one main table: Project, and about 10 other tables (Entity1, Entity2, etc.)

Each of the different entities can belong to one or many projects.

I know I can have tables like ProjectEntity1, ProjectEntity2, ProjectEntity3, to store relationships, but that gets old.

Are there any issues with creating a ProjectContents table, which would hold projectid, entityid, and entitytype? (Entitytype would be name of the source table.)

(Currently, this is in SQL Server Compact 4.0, as a standalone solution. It may never evolve past that.)

EDIT: More details - It's yet another world builder type desktop application, where I can dream up/generate stuff - worlds, cultures, languages, religions, alien species, characters, plots - and then assign them to a full blown project if I want. So, a project can have many entities of varying types, and an entity can belong to many projects, or none at all. Right now, all CRUD/Reads are done with C# LINQ, and unless I switch to SQL Express or something else, I don't see the need for stored procedures. At least until I hit some performance bottlenecks.

Answer 1

One problem with your proposed design is that instead of simple JOINs you have to run dynamic SQL every time you want to see the actual value of any entities. Furthermore, you will have complicated procedures (or triggers) for inserting data into ProjectContents.

Stick with the ProjectEntity1, ProjectEntity2, ... solution if you can have more that one EntityX coupled to a certain Project. If this is not the case (that is, a Project can have maximum one of each Entities), you can create a table like this:

ProjectID | Entity1ID | ... | EntityNID

In either case, your JOINs will be relatively numerous (not that 10 JOINs in a query are a lot) but otherwise simple. And, lastly, occasional maintenance (like adding new entities) will also be simple.

Answer 2

I'd use ProjectContents approach with some modifications to avoid polymorphic associations. First, create a common parent table for all Entity. :

CommonEntity(common_entity_id PK, entity_type varchar(20) not null,
CONSTRAINT UQ_COMMON_ENTITY UNIQUE(id,entity_type), 
  -- seems redundant, but now other tables can have FK to (id,entity_type)
CONSTRAINT CHK_COMMON_ENTITY_TYPE CHECK(entity_type IN('entity1','entity2',..etc));

Then modify each Entity tables so they have FK to CommonEntity :

 Entity1 ( ...
 common_entity_id, entity_type varchar(20) NOT NULL DEFAULT 'entity1',
 UNIQUE(common_entity_id, entity_type ),
 CONSTRAINT CHK_ENTITY1_TYPE CHECK (entity_type='entity1'),
      -- to ensure that only entities of proper type are stored in Entity1 table
 CONSTRAINT FK_ENTITY1_COMMON_ENTITY FOREIGN KEY(common_entity_id,entity_type)
   REFERENCES CommonEntity(common_entity_id,entity_type)

Now you can refer to CommonEntity table in ProjectContents :

ProjectContents(common_entity_id, project_id)

Queries are straightforward :

SELECT p.project_id, e1.*, e2.*, .... 
FROM Project p
INNER JOIN ProjectContents pc ON (pc.project_id = p.project_id)
INNER JOIN CommonEntity ce ON (ce.common_entity_id = pc.common_entity_id)
LEFT JOIN Entity1 e1 ON (e1.common_entity_id = ce.common_entity_id)
LEFT JOIN Entity2 e2 ON (e2.common_entity_id = ce.common_entity_id)

You can even omit joining CommonEntity in such queries.

Also, for simplicity sake I use CHECK constraint for entity_type in CommonEntity table. In real world I prefer to have EntityType table and FK to it. This way adding new Entity does not require ddl in existing table. Finally, while left joining entity1,entity2,etc it may make sense to add entity_type to join condition; thus, if optimizer is smart enough to look into appropriate check constraint definition, it can for some queries eliminate accessing table data completely.