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.


2 Answers 2


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.


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,
  -- 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
   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.

  • This is an interesting approach, given that all my objects have an autoincrement id, name, and created timestamp. And I could conceivably have the entitytype be a table based value as well. I may have to try this out in a separate application.
    – Leah Hurst
    Commented Aug 29, 2012 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.