I'm bumping this one, if the description is not clear, is there anything I can do to help your understanding of the problem?
On Mar 16, 10:30 am, Gauthier Segay <[email protected]> wrote: > I'm working on an application which has no ORM and a rather special data > model. > > The model is made of several entities that fits a relational model on > the logical side, one entity may be related to another, etc. But the > physical implementation is relying on a specific mechanism targeting > issue with copying of all the data in a specific use case. > > The application is managing large sets of data composing a case. Each > case (or "top case") references a number of subcases. > The user is allowed to create a new case from an existing one; > physically, creating a new case is just adding a row to the case > table, this row referencing each set of entities (i.e. the row holds > the subcase identifiers), the application starts duplication of > a subset of data only if any data is changed in subcase data that is > shared among multiple cases. > > So, what is special, is that the model is not using strong foreign > keys to maintain the relations, it's somewhat handled by the > application code and what I would call loose coupled relations. > > The relation is made of the subcase identfier (coming from the case > row for a given entity) and an arbitrary identifier. > > here is a sample schema script (mssql) > > ---------------------- > create table cases ( > [key] int identity > , shop_case_key int > , product_case_key int > , product_by_shop_case_key int > , name nvarchar(100) > ); > > create table shop_cases ( > [key] int identity > , subcase_name nvarchar(100) > ); > > create table product_cases ( > [key] int identity > , subcase_name nvarchar(100) > ); > > create table product_by_shop_cases ( > [key] int identity > , subcase_name nvarchar(100) > ); > > create table shops ( > shop_case_key int > , shop_id int > , name nvarchar(100) > , product_space int > ); > > create table products ( > product_case_key int > , product_id int > , name nvarchar(100) > , price decimal > ); > > create table product_by_shop ( > product_by_shop_case_key int > , product_id int > , shop_id int > ); > > insert into shop_cases (subcase_name) values ('shopcase1'); > insert into shop_cases (subcase_name) values ('shopcase2'); > > insert into product_cases (subcase_name) values ('productcase1'); > insert into product_cases (subcase_name) values ('productcase2'); > > insert into product_by_shop_cases (subcase_name) values > ('productbyshopcase1'); > > insert into cases (shop_case_key, product_case_key, > product_by_shop_case_key, name) > select 1,1,1,'case 1' > union all > select 1,2,1,'case 2' > union all > select 2,1,1,'case 3' > > insert into shops (shop_case_key, shop_id, name, product_space) > values(1, 1, 'shop 1', 100); > insert into shops (shop_case_key, shop_id, name, product_space) > values(1, 2, 'shop 2', 200); > insert into shops (shop_case_key, shop_id, name, product_space) > select 2, shop_id, name, product_space*2 from shops > > insert into products (product_case_key, product_id, name, price) > values (1, 1, 'product 1', 1); > insert into products (product_case_key, product_id, name, price) > values (1, 2, 'product 2', 10); > insert into products (product_case_key, product_id, name, price) > select 2, product_id, name, price * 3 from products; > > insert into product_by_shop (product_by_shop_case_key , product_id, > shop_id) values (1, 1, 1); > insert into product_by_shop (product_by_shop_case_key , product_id, > shop_id) values (1, 2, 1); > insert into product_by_shop (product_by_shop_case_key , product_id, > shop_id) values (1, 1, 2); > ---------------------- > > for a given case key and shop id, looking at the product list for a > given shop, the SQL whould look like that > > select > c.*, s.shop_id, p.* > from > cases c > inner join shop_cases sc on c.shop_case_key = sc.[key] > inner join product_cases pc on c.product_case_key = pc.[key] > inner join product_by_shop_cases pbsc on c.product_by_shop_case_key = > pbsc.[key] > inner join product_by_shop pbs on pbs.product_by_shop_case_key > = pbsc.[key] > inner join shops s on > sc.[key] = s.shop_case_key > and pbs.shop_id = s.shop_id > inner join products p on > pc.[key] = p.product_case_key > and pbs.product_id = p.product_id > where > c.[key] = @caseKey > and s.shop_id = @shopId > > as you see this is turning pretty complicated with a simple model, the > application is quite larger than this. > > I'm investigating possibilities to fit this in a normal NH model where > I could use such things: > > using(var session = CraftSessionForCaseKey(1) /* some black magic in > setting the session or session factory */){ > var shop = session.GetByKey<Shop>(1); > var products = shop.Products; // will issue the previous select > statement (with product columns) > > } > > Assuming I can't change the way we put the model, is there something > that you think is remotely feasible, even putting some amount on > effort to override some key components involved in the way ISession or > ISessionFactory works? > > I currently haven't come up with lots of ideas on how to overcome this > issue (session filter aren't enough to manipulate entity > relationships). Things are done this way to allow the end user to > create new cases at will and only incurring the performance hit of > duplicating data to a subset each time a tweak is made in an entity > set which is used across multiple top cases. > > I've thought about the following: > > - dynamically crafting views with triggers > - storing changes as diff > > but these seem to be seriously complex to me, NH could be of help with > the first one, but for the second it involves a lot of > application / model logic. > > Alternatively, did anyone encountered such a situation? what > alternative to such model would you find applicable? > > Thanks a bunch for your insight. -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
