July 26, 2012 at 11:42 am
I have to redesign the database based on the new business requirements
Could someone please help me with the steps that needs to be followed
when redesigning a database.
July 26, 2012 at 11:52 am
Start by modeling the data the business needs. Design a database that supports that model. Then work out how to get any useful information from the old database into the new database, and do so. Those are the steps.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2012 at 11:56 am
Lucky9 (7/26/2012)
I have to redesign the database based on the new business requirementsCould someone please help me with the steps that needs to be followed
when redesigning a database.
Wow, where to begin?
Have you ever designed a database before?
Did you get to help with putting together the business requirements?
Can you talk to the people who put together the business requirements?
Can you talk to the end users?
Have you ever used a product like Visio or Erwin?
What version of SQL Server will you be using? Is it the same version as the current database?
Is there an interface you have to connect to? More than one? Will they also be redesigned?
What sources do you have for the data? How frequently does it come in?
Can you do whatever you need to redesign this database or are there restrictions, things you can't change?
What's your time table? How long do you have to get this done?
Redesigning a database can be a lot of work. You have to understand the current database and what the new one needs to be. You have to be able to not only diagram this new database, but understand and map how all the old data will fit and what new data there will be.
If you have the time, when you're doing this, you should analyze the existing data and the estimates for the new data and make sure all the data types are the best fit they can be and if there's a place for calculated columns, sparse columns and other enhancements, depending on the version of SQL Server you're using.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 26, 2012 at 12:17 pm
1. Find out what's wrong with the current database.
2. Change it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 26, 2012 at 12:47 pm
Lucky9 (7/26/2012)
I have to redesign the database based on the new business requirementsCould someone please help me with the steps that needs to be followed
when redesigning a database.
Either the current database was a complete disaster or the business changed drastically (like from being a hospital to be a car manufacturer). No change in business model should trigger a full redesign of the underlying database.
Start by doing a Gap Analysis which means...
a) What you have today,
b) What are the new requirements,
c) What's the gap between today's functionality and required functionality
d) Evaluate changes needed to fill the gap.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 26, 2012 at 12:56 pm
PaulB-TheOneAndOnly (7/26/2012)
Lucky9 (7/26/2012)
I have to redesign the database based on the new business requirementsCould someone please help me with the steps that needs to be followed
when redesigning a database.
Either the current database was a complete disaster or the business changed drastically (like from being a hospital to be a car manufacturer). No change in business model should trigger a full redesign of the underlying database.
Start by doing a Gap Analysis which means...
a) What you have today,
b) What are the new requirements,
c) What's the gap between today's functionality and required functionality
d) Evaluate changes needed to fill the gap.
Which is the same thing I already said, but with different names for the steps.
However, I have seen "simple" business model changes that required almost complete redesigns of the database. For example, it's usual to have a one-to-many relationship between orders and customers, and a one-to-many relationship between orders and shipments. I.e., you don't normally have two or more customers assigned to a single order, and you don't normally have two or more orders on one shipment; but it is normal (salespeople hope anyway) that each customer will have multiple orders, and it's common to break orders up into multiple shipments.
That's how a business I worked for operated for years. Then, because of a new product line, it became necessary to consolidate multiple orders into single shipments that would be for multiple customers, in some cases. So, some orders would be split into multiple shipments, for one customer, some would have one shipment for multiple customers, some would have multiple shipments for multiple customers on one order, and so on. This was necessary to handle certain product lines and certain inter-business ordering practices.
Well, that required a huge revamp of the whole data model, because of a single product line that was introduced, but without any change to the core business.
Rare, but it can happen.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 27, 2012 at 6:15 am
GSquared (7/26/2012)
PaulB-TheOneAndOnly (7/26/2012)
Lucky9 (7/26/2012)
I have to redesign the database based on the new business requirementsCould someone please help me with the steps that needs to be followed
when redesigning a database.
Either the current database was a complete disaster or the business changed drastically (like from being a hospital to be a car manufacturer). No change in business model should trigger a full redesign of the underlying database.
Start by doing a Gap Analysis which means...
a) What you have today,
b) What are the new requirements,
c) What's the gap between today's functionality and required functionality
d) Evaluate changes needed to fill the gap.
Which is the same thing I already said, but with different names for the steps.
I'm sorry but I have to differ on this one GS; I couldn't see in your post anything that remotely resembled Gap Analysis - a very nicely worded and accurate for sure general guideline but not what I posted. I do my best not to be redundant.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 27, 2012 at 6:50 am
PaulB-TheOneAndOnly (7/27/2012)
GSquared (7/26/2012)
PaulB-TheOneAndOnly (7/26/2012)
Lucky9 (7/26/2012)
I have to redesign the database based on the new business requirementsCould someone please help me with the steps that needs to be followed
when redesigning a database.
Either the current database was a complete disaster or the business changed drastically (like from being a hospital to be a car manufacturer). No change in business model should trigger a full redesign of the underlying database.
Start by doing a Gap Analysis which means...
a) What you have today,
b) What are the new requirements,
c) What's the gap between today's functionality and required functionality
d) Evaluate changes needed to fill the gap.
Which is the same thing I already said, but with different names for the steps.
I'm sorry but I have to differ on this one GS; I couldn't see in your post anything that remotely resembled Gap Analysis - a very nicely worded and accurate for sure general guideline but not what I posted. I do my best not to be redundant.
What I said to do is:
Start by modeling the data the business needs. Design a database that supports that model. Then work out how to get any useful information from the old database into the new database, and do so.
We have the same steps, just in a different sequence and worded differently.
You suggest starting with analyzing what you already have. I suggest starting with an analysis of what is needed. The reason I start there is it avoids prejudicing the analysis of needs by assuming that either what you already have "must exist for a reason", or "would be good to hang on to because that will save time". When modeling the needs of the business, you should start with no prejudices based on existing systems, unless you know that existing systems are properly modeled for some subset of what you are modeling the new system to be. This avoids, "What we need is X, but what we have is W, so let's keep what we have and try to kludge it into some Frankensteinian thing that might more closely resemble X."
You suggest gathering new requirements second. My version, you've already done that. Different sequence, same step. Mine has the advantage of being a "fresh look", yours has the advantage of not having to re-analyze things that have previously been correctly modeled and have not changed since that was done. Mine has the disadvantage of having to re-model everything, even things that might already be correctly modeled. Yours has the disadvantage of assuming that prior modeling was correct and that nothing it modeled has changed. If you avoid that flaw in your version, you just did the work twice, in both steps 1 and 2, while mine does the work once.
Your step three is an implicit part of my final step, of working out how to get old data from old sources into the new database. Same step, different words.
Your step four is also part of my final step. Same step, different words.
Yours doesn't have any actual doingness steps, like moving the data (it just has analyzing it), while mine does have that, but it's implied in yours that the purpose of the analysis is to do thing. Same actions, different implicit/explict on the wording.
So, the only material difference is that I suggest figuring out what you need first, and what you have second, and you suggest the inverse sequence on the exact same things.
The only possible advantages/disadvantages of either is that mine assumes less inheritence from existing systems is needed, while yours assumes more will be a good thing. In my experience, different situations require different assumptions on that point. I tend to work from "what do we actually need, regardless of what we have" and then try to integrate existing resources into it, because that helps clarify the actual needs without prejudice. I've found that more useful more often. But it doesn't make it "right" and yours "wrong", so long as anyone using either system understands why it's done the way it is and the pros/cons of each.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 27, 2012 at 4:41 pm
So... reading your detailed defense of your case, you are not suggesting to do Gap Analysis - which is what I do suggest to do, init?
I have to rest my case.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 31, 2012 at 6:49 am
PaulB-TheOneAndOnly (7/27/2012)
So... reading your detailed defense of your case, you are not suggesting to do Gap Analysis - which is what I do suggest to do, init?I have to rest my case.
As I already mentioned, same thing, different wording & sequence. I already laid that out in my analysis. Not "defense", clarification. If you don't get that, you don't get that. Simple as that.
Not sure why you would consider "figure out what you need, compare it to what you have, figure out how to get what you have to match what you need" as "nope, doesn't include Gap Analysis". But you don't consider it such.
So, what do you consider "Gap Analysis"?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 31, 2012 at 9:58 am
Lucky9 (7/26/2012)
redesign database based on the new business requirements ... steps need[ed] ... when redesigning a database.
Spend some more time gathering, detailing and verifying your data requirements. Don't worry initially about what needs to be done to/with the data, but do worry very much about identifying all the data that's needed. Also, don't get caught up in the mechanisms of the current system or db -- go after the core data needs.
Do a logical data model (that is, based on entities not tables). Normalize at least to 3rd normal form if at all possible; 2nd normal form is the dead minimum you can accept. This will take at least a few days, and could be much more, depending on the size of the system.
If it's a large system, a tool like Erwin (Visio is not really a data modeling tool per se) is required to do a thorough job. If you don't have any tool, hopefully it's not a large system or you're willing to accept a less detailed design (not a good idea, but you won't have much choice).
Finally, convert the logical data model into a physical db design. Verify that all data requirements have been met. Denormalize only if you have to, but don't refuse to do it when it's genuinely needed.
[So I come down more on GSquared's view of the proper approach. I, too, don't like doing a "Gap Analysis" first -- it puts too much emphasis on the existing system, which could be a total mess.]
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2012 at 10:04 am
Scott, What does Erwin have that Visio doesn't that causes you to consider it a modeling tool while not considering Visio one?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 31, 2012 at 10:33 am
Stefan Krzywicki (7/31/2012)
Scott, What does Erwin have that Visio doesn't that causes you to consider it a modeling tool while not considering Visio one?
I admit I haven't used Visio that much, but I thought it was just a diagramming tool period, with no inherent data base design handling.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2012 at 10:51 am
ScottPletcher (7/31/2012)
Stefan Krzywicki (7/31/2012)
Scott, What does Erwin have that Visio doesn't that causes you to consider it a modeling tool while not considering Visio one?I admit I haven't used Visio that much, but I thought it was just a diagramming tool period, with no inherent data base design handling.
No, it has a database template, columns, column types, relationships, foreign keys, etc... Even has a pretty good "reverse engineer" feature.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
July 31, 2012 at 12:46 pm
Stefan Krzywicki (7/31/2012)
ScottPletcher (7/31/2012)
Stefan Krzywicki (7/31/2012)
Scott, What does Erwin have that Visio doesn't that causes you to consider it a modeling tool while not considering Visio one?I admit I haven't used Visio that much, but I thought it was just a diagramming tool period, with no inherent data base design handling.
No, it has a database template, columns, column types, relationships, foreign keys, etc... Even has a pretty good "reverse engineer" feature.
Erwin was a really good db design tool when I used it.
Clearly Visio is more than I thought it was, but MS itself still doesn't attempt to market it as a db design tool, as they never mention it:
But if you've already got it, and don't have Erwin or the equivalent, it's certainly going to be much better than nothing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply