One problem with your proposed design is be 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.

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.

One problem with your proposed design is be 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, occassional maintenance (like adding new entities) will also be simple.

One problem with your proposed design is be 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.