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.
