February 13, 2008 at 3:56 am
Hi,
I work at a fairly large company.
In order to lessen dataredundancy our new DBA consult have had a great idea.
Which is now being implemented.
Store every person and every company/organization in a single separate database.
Each such object gets a surrogate key, an integer.
All other systems/databases therefore should store no data nor idenification
about a company/person but the surrogate key.
I think this is disaster.
I think you should never never export surrogate keys to other databases.
I think data consistency is not possible in this scenario.
I think this makes DRI almost impossible.
I think you get a high risk of dangling relationships since cross database
synchronized backup is impossible.
What do you think?
I do would appreciate opinions regarding pro's and con's about this.
/m
February 13, 2008 at 5:23 am
I agree, it's a poor idea. It sounds more like the DBA gets confused with seeing thousands of tables, and to make it more "bitesize" he wants to move items to their own database.
by bitesize, I mean so he can understand it better for himself.
He's a really poor DBA if he's suggesting removing DRI. DRI is the backbone of DBMS systems. As you identified, foreign keys accross databases are not possible, so you'd lose/orphan the relationships, and ultimately damage teh company.
Moving things to separate schemas might make some sense, ie instead of dbo you might make an "Administration" schema for somethings, and a "BusinessObjects" for another group of tables, but event hat is stupid, IMHO...naming conventions, or at least Views or Synonyms that have better Naming Convensions would be my first choice....so some table name AC04RS2003 gets an synonym called "AccountingCore" or something to solve any issues understanding the layout.
I'm also confused as to how moving items to separate databases could possibly lessen data redundancy. To do that, he should have had suggestions on normalizing the database, suggesting which columns would benefit as a separate table, with an analysis on how much work would be required to update any applications which reference the data.
Lowell
February 13, 2008 at 5:50 am
You consultant may have taken a wrong turn, or you may be explaining a bit poorly. Here is a scenario we have.
We have a single application for entering customer information. We have a number of other applications that use this customer information that run on different servers (and obviously different databases). To prevent entering data twice, we use replication to send the customer information from our customer database to each of the other OLTP systems. Transactional replication sends new records and changes to each server and the individual systems can view and use this information, but are not allowed to update it. It is a hub-and-spoke MDM solution using just MS and SQL technology.
This has proven to add complication in a couple of areas - particularly foreign keys. Replication does not like foreign keys on the subscriber in some cases. One of note is a deferred update that is actually changed into a delete and an insert. Because of this, relationships need to be managed carefully. Where they matter most, we use constraints and some triggers but take a performance hit where we do.
It is complicated, but manageable and actually low maintenance.
Now, if you are simply trying to reduce the number of tables in a single database by putting them into two databases - it's a terrible idea because of DRI, consistency in backups, etc. If you have a pressing need to spread workload over multiple servers (of different versions of SQL or other platforms because of your vendors) and do not want users to have to key information multiple times - it's ok to be creative as long as you have resources available to manage the issues that come up.
February 13, 2008 at 6:42 am
I agree with m.earl. If you have a large company, then each application/system should not be (re)defninig employees, divisions, departments, etc. A master database is absolutely required. It may be more work but there should be one and only one gospel version of common data elements.
The only thing that seems odd to me, is that no other identifying data is to be stored. Each system should be able to replicate the information that it needs (such as the employee name or organizational structure) and reshape it to suit its usage. Thus declarative referential integrity is possible -- it's just that the app can't write/modify any data in those parent tables.
February 13, 2008 at 7:36 am
The basic idea is to store all humans in one and only one place.
And all companies in one and only one place.
Thus Employees, Customers, etc gets stored in a Person table.
And all other system stores no names, no addressess etc but the surrogate key.
And Companies, TradeUnions, State AGencies goes into an Organization table.
And all other system stores no names, no addressess etc but the surrogate key.
February 13, 2008 at 7:48 am
I tend to agree with Michael. On the surface it sounds like a bad idea, but I woulnd't condemn it without knowing more.
If this database is the central source for getting the PKs, then it might not be a big deal. It's all in how well it's set and how things are managed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply