Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Database Redesign Expand / Collapse
Author
Message
Posted Thursday, July 26, 2012 11:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, February 16, 2014 9:35 PM
Points: 98, Visits: 297
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.
Post #1336028
Posted Thursday, July 26, 2012 11:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1336033
Posted Thursday, July 26, 2012 11:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 2,751, Visits: 7,161
Lucky9 (7/26/2012)
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.


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
Post #1336036
Posted Thursday, July 26, 2012 12:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 PM
Points: 6,582, Visits: 8,860
1. Find out what's wrong with the current database.
2. Change it.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1336044
Posted Thursday, July 26, 2012 12:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
Lucky9 (7/26/2012)
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.


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.
Post #1336064
Posted Thursday, July 26, 2012 12:56 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
PaulB-TheOneAndOnly (7/26/2012)
Lucky9 (7/26/2012)
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.


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
Post #1336073
Posted Friday, July 27, 2012 6:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
GSquared (7/26/2012)
PaulB-TheOneAndOnly (7/26/2012)
Lucky9 (7/26/2012)
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.


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.
Post #1336433
Posted Friday, July 27, 2012 6:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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 requirements
Could 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
Post #1336453
Posted Friday, July 27, 2012 4:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #1336839
Posted Tuesday, July 31, 2012 6:49 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1337861
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse