We have built a database in SQL Server using two patterns we found in Len Silverston's Data Model Resource Book Vol. 3, one for Types and Categories: Classification and another for Hierarchies, Aggregations, and Peer-to-Peer relationships. Essentially, it is implmented as follows:
Classification
[Entity] (M x M) [EntityType] (M x M) [EntityTypeType]
...where the (M x M) is a many-to-many relationship implemented in the database by a Classification table.
Association
The entity and type tables above also have their own typed Rollups:
...where each (M x M) is a many-to-many relationship implemented in a Rollup table with a foreign key to the type of rollup/association.
The resulting data structure gives us tremendous expressive ability in terms of describing both our entities and their relationships to one another. However, we're having trouble taking advantage of this expressiveness in our applications. The primary issue is that in spite of the advances in EF 4& 5, M-2-M relationships are still tricky and beyond that we're trying to access the M-2-M's in at least 2 directions whenever we hit the database. It is especially complicated by:
Technologies:
Questions about the model itself:
Questions about the typing scheme - bear in mind that the types are pretty static:
Any ideas or experiences are much appreciated!
I tried to answer each question, but I have to admit I'm not sure if you are trying to dynamically create entities on top of a database at run-time - or if you're just trying to create entities dynamically before run-time.
If you're trying to release code that dynamically changes/adjusts when the schema in SQL Server is changed, then I would have some different answers. =)
Is this simply an unworkable data model in .NET?
Some things you mentioned that stood out to me:
Some questions I have after reading:
I don't know your domain, but I know I have a hard time following what you've described. =) In my opinion it has already become somewhat unworkable for two reasons: 1) You're asking on SO about it, b) It's not easy to describe without a lot of text.
Should we first flatten our database into more .NET friendly views that essentially model our business objects?
Yes!
At least from my experience I would say yes. =)
I think it's ok to create entities in the database that have complex relationships. Parent/child, peer to peer, hierarchical, etc. All fine and good and normal.
But the application shouldn't have to interpret all of that to make sense of it. The database is good at doing joins, grouping data, creating views, etc. I would advise creating views or stored procedures that get as close to your business objects as possible - at least for the reads.
Also consider that if you push the complexity of relating objects to the application, you might pay some performance penalties.
Should we scaffold the [EntityType] and [EntityTypeType] tables, their classifications, and their rollups into C# classes?
I would advise against this. The only reason is that now you are doing database work in the application layer. If you want to do joins/rollups/etc. you're managing that data in the application - not the database.
I'm sure you already know this, but you want to avoid bringing back everything in the database into the application and then running queries/building objects.
SQL Server is awesome at relating objects and pulling together different entities. It'll be very fast at that layer.
I would create business objects and populate those from SQL.
If you needed to scaffold EntityType/EntityTypeType, just be careful you aren't running into N+1 issues.
What are some ideas for how to scaffold those files - as static classes? Hard-coded object lists?
Options:
Should we instead cache the typing scheme at start-up (this bothers me, because it adds a lot of overhead to starting up the Console Apps)?
You want to generate the business objects based on your entities at application load? Or another way of phrasing the question ... generate proxy classes at runtime?