sql server replication:
hi,
I have been tasked with restructuring a database. The database is
currently recreated for different periods so for 2005/2006 we have one
database and for 2006/2007 we have another. But clearly some tables do
not change much and so they want these tables hived off to a separately
maintainable database but the data could still change, so for example a
location might have capacity of 400 people in 2005 but because of
building work, might have twice that in 2006 but generally it is
unlikely to change much.
How best to do this?!
a) I am looking at just having the "period databases" converting their
tables to "period specific" views of the reference database. The
trouble is that every attribute might need to be period specific i.e if
capacity changed for 2006 there would be a 2006 location record but
lets then say that we changed "Number of Windows" in 2005 - In reality
I would expect the 2006 record to change because it effectively only
"inherited" its Number of Windows from 2005 but how do I "know" this
unless I make a different record/table for each attribute? It's object
orientation represented in RDBMS tables with one location object
inheriting everything from another but overriding one or more
properties.
b) would replication be a better solution? I am no expert in
replication - but if transactions are enforced i.e if the update cannot
be replicated to all databases then it should be rolled back, I can
imagine some pretty problematic transactions. Transactional replication
just seems to apply the transaction as a whole to a subscriber but does
not fail the publishers transaction if the subscriber replication fails
c) update triggers? - presumably use some form of distributed
transaction but this seems even more complex and likely to result in
lengthy transaction times if not deadlocks etc.
the other complication is that there has to be a solution for SQL
Server and Oracle. Though not necessarily the same solution it would
obviously be easier to implement.
Has anyone else had a similar requirement and how did you implement it?
Big thanks in advance if you have any suggestions.
cheers,
Phil
