December 15, 2003 at 1:06 am
I have a 4Gb database, consisting of 250 user tables and almost 500 stored procedures. The info in this 1 database refers to 20 different business units. I have been asked normalize the database (i.e 20 different databases etc.).
Does anyone have any ideas on this? I will appreciate any feedback and suggestions.
Rgds - George
December 15, 2003 at 1:40 am
Sorry, but why do you think that your db isn't normalized right now or would be 'more' normalized when split into 20 different dbs?
Have you read this http://www.sqlservercentral.com/columnists/bkelley/normalization.asp ?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 15, 2003 at 7:59 am
Why end up with a database for each business unit? That seems to be your strategy. Is there not any information that they would share? Address? Customers?
Making it so different might also increase the complexity of ALL of your queries. Would you gain so much from turning a simple join into a complex join?
It sounds as if someone higher up read the word "normalization" in a memo and decided they needed it without research.
Bonne chance!
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
December 16, 2003 at 9:42 am
Mmmmm....I have to agree.
Unless there are severe performance issues resulting from poor design, I can't see any reason for doing this.
I'd put money on the scenario where, having completed your task, the person(s) asking you to normalize into 20 databases would then ask for a query comparing all 20 databases.
Seriously, I would question the reasoning for this as it is likely to be quite a lot of work.
Regards
Graeme
December 16, 2003 at 10:37 am
Tend to agree with above unless you have good reasons (like privacy) for separating the BU data.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 16, 2003 at 1:13 pm
Reminds me of some old Dilbert comic strip.
In year 0 management decided to decentralize;
in year 1 they decided centralization is the way to go.
Comment was: 'He's a blessed manager'.
...well, Steve mentioned a (the only?) good reason, that is privacy. But this could also be achieved with good database model and a good permission concept.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 16, 2003 at 1:21 pm
Normalization is used to reduce redundancy in the database. Redundancy is when the same information is kept in several different places in the same database. The problem is that an update or an insert can put inconsistent data into the database and you find yourself in trouble.
A consideration against normalization is the complexity of the queries. I have worked with very normalized databases that needed four joins to return a company name and address. That might be an extreme, but they do exist.
My professor said "Normalize til it hurts, denormalize until it works."
If the database is performing well, then normalization is a long process that may or may not give you any gains and may cause more problems.
Bonne chance!
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
December 23, 2003 at 11:32 am
It seems like this 20G database has been used as the "Default" database for any new information. It didn't start out like this did it? Somewhere along the line a new database could have/should have been created. How many apps are accessing this database?
I think you need to exercise some research on the growth history of this database. Some busines units may need to share data, some not.
Whats the secutiry requirements for this one database? Do these requirements dictate "Normalizing" this database? That doesnt sound right to me.
-Isaiah
-Isaiah
January 13, 2004 at 1:10 am
20 Business units one DB. 20 business units -> 20 dbs. Is it about normalisation really, or is it about decentralisation of information? We do lack information of course over the schema. Seems to me that I have to agree with the colleague that said that this company had a db that they kept adding to it for all their unit, and now they try to tidy up a bit. However, it also seems to me that they go from one end to the other. Means, that they were adding to one db making chaos, now (to my thinking) they could probably share information in one db and probably make some "smaller" ones, but they prefer to make distinct databases per units. I don't think I 'd go for the 20 units scenario, if my guts instict is right, they will end up with data redundancy hense the 'normalisation' scenario is going to be going down the drain...
January 15, 2004 at 6:14 pm
My 2 cents worth....
He's asked how to go about doing it...not whether it's a good idea or not. The questions should be "Who asked you to do it?" and more importantly "Who is going to rewrite the front end code to access it?"
I've yet to see (in my limited career) a database schema change that didn't screw up the application intended to use it.
To answer the original question for suggestions on how to change it:
If it were me I would:
1. DOCUMENT the security requirements as DETAILED as possible.
2. DOCUMENT/DIAGRAM a logical seperation of tables.
3. EVALUATE the role of each stored procedure.
4. EVALUATE the front end application's dependency on the database as it is.
5. ESTIMATE the finacial/time cost of 'normalizing'.
6. PRESENT to management/client the cost of this massive change.
7. WAIT for their decision.
8. BEGIN creating databases/tables according to the DIAGRAM (#2 above).
9. BEGIN working with app team (if other than DBA) to recreate stored procedures needed for inevitably new front end app.
10. THANK GOD I became a DBA.
bb
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply