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.
Best Answer
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:
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.