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.