Objection versioning database for enterprise apps -

many enterprise apps have relational database back-end storage objects. objects immutable, not.

when immutable object depends on mutable should provide appropriate version.

see: have order object , customer object. order depends on customer. order never changes. after order completed , shipped customer changes address. how can know address used shipping? not want store information in order because unnormalize database.

the answer versioning: order should depend on customer with specific version.

that idea used shared libraries (gac, sxs, different .so versions , on).

but how in database? add "version" field tables , relation field? increase database x2 , make entangled.

separate info , store somewhere?

i believe there has nice solution. know any?

i'm not quite sure understand problem. taking example of customers , orders, example, have

create table address (   address_id number primary key,   street     varchar2(100),   ... );  create table customer (   customer_id number primary key,   first_name  varchar2(100),   ...   address_id  number references address( address_id ) );  create table orders (   order_id   number primary key,   ...   customer_id number references customer( customer_id ),   address_id  number references address( address_id ) ); 

if address first-class table, both orders , customer can reference particular address. customer can reference customer's current address, orders can reference address order chipped may have been customer's then-current address or may have been address of person placing order (i.e. place order flowers have sent mother's house on mother's day). address becomes immutable rather updating address row, insert new address_id , update row in customer table point new address.

if want track history of customer's addresses, you'd move address_id out of customer table , new customer_address mapping table has sort of version-related information. commonly, have specify range of dates mapping valid.

create table customer_address (   customer_address_id number primary key,   customer_id         number references customer( customer_id ),   address_id          number references address( address_id ),   valid_from          date,   valid_to            date );