things to know when designing a new database

Question

Post reply

things to know when designing a new database

DBA24

SSC Journeyman

Points: 94
More actions
January 21, 2013 at 2:52 pm

#287835

what are things one need to always keep in mind when designing a new database for an application

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Maybe start with this:

http://www.amazon.com/Server-Relational-Database-Implementation-Experts/dp/143020866X/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101100 More actions · Answer 2

What you really need is someone that has experience doing it, period. This is not something that you can just "wing it".

If you have to do it with no experience, just expect up front that you'll have to re-do things, sometimes the same thing multiple times.

The next most important thing is to separate the logical and physical design phases -- do NOT allow anyone to skip the logical phase and immediately start designing "tables" or "indexes".

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".

DiverKas SSCrazy Points: 2049 More actions · Answer 3

1) Do NOT let your C#/VB/ASP/Pascal guys design the database.

2) Do NOT let your DBA's design the database.

3) Hire a Data Architect.

Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.

ScottPletcher SSC Guru Points: 101100 More actions · Answer 4

DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.

#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.

Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.

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".

WonderPra SSCommitted Points: 1624 More actions · Answer 5

I believe u should also have thorough understanding on relationships and length, size of the variables. They cost u a lot when it comes to the performance.

--Pra:-):-)--------------------------------------------------------------------------------

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

ScottPletcher (1/22/2013)
DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.
#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.
Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.

Neither is #1. There is no rule that says a developer is unable to design a proper database because they are a developer.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.
#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.
Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.
Neither is #1. There is no rule that says a developer is unable to design a proper database because they are a developer.

I'm a developer (SQL mostly, not front end). I'd argue that I can design at least a passably good database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

GilaMonster (1/22/2013)
Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.
#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.
Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.
Neither is #1. There is no rule that says a developer is unable to design a proper database because they are a developer.
I'm a developer (SQL mostly, not front end). I'd argue that I can design at least a passably good database.

Gail we are saying the same thing. I think anytime someone starts segregating abilities based on job title they are destined to get snowballed. I too am a developer and would like to think I could cobble something usable together.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101100 More actions · Answer 9

Sean Lange (1/22/2013)
GilaMonster (1/22/2013)
Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.
#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.
Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.
Neither is #1. There is no rule that says a developer is unable to design a proper database because they are a developer.
I'm a developer (SQL mostly, not front end). I'd argue that I can design at least a passably good database.
Gail we are saying the same thing. I think anytime someone starts segregating abilities based on job title they are destined to get snowballed. I too am a developer and would like to think I could cobble something usable together.

Perhaps. But I would not want someone who had not been a full-time database designer or DBA at some point to head a db design. Based on my past experiences, (almost) pure developers can't seem to forget physical details long enough to do a proper logical design. Any who could do it properly would be the rare exceptions, not the normal rule.

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".

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

ScottPletcher (1/22/2013)
Sean Lange (1/22/2013)
GilaMonster (1/22/2013)
Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.
#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.
Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.
Neither is #1. There is no rule that says a developer is unable to design a proper database because they are a developer.
I'm a developer (SQL mostly, not front end). I'd argue that I can design at least a passably good database.
Gail we are saying the same thing. I think anytime someone starts segregating abilities based on job title they are destined to get snowballed. I too am a developer and would like to think I could cobble something usable together.
Perhaps. But I would not want someone who had not been a full-time database designer or DBA at some point to head a db design. Based on my past experiences, (almost) pure developers can't seem to forget physical details long enough to do a proper logical design. Any who could do it properly would be the rare exceptions, not the normal rule.

I agree it would be the exception but the "rules" as posted sounded like absolutes and I disagree with that sentiment.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 101100 More actions · Answer 11

Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
Sean Lange (1/22/2013)
GilaMonster (1/22/2013)
Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.
#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.
Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.
Neither is #1. There is no rule that says a developer is unable to design a proper database because they are a developer.
I'm a developer (SQL mostly, not front end). I'd argue that I can design at least a passably good database.
Gail we are saying the same thing. I think anytime someone starts segregating abilities based on job title they are destined to get snowballed. I too am a developer and would like to think I could cobble something usable together.
Perhaps. But I would not want someone who had not been a full-time database designer or DBA at some point to head a db design. Based on my past experiences, (almost) pure developers can't seem to forget physical details long enough to do a proper logical design. Any who could do it properly would be the rare exceptions, not the normal rule.
I agree it would be the exception but the "rules" as posted sounded like absolutes and I disagree with that sentiment.

Again, my exception is only for someone who had 1+ years full-time experience doing database design.

Almost every developer I've ever met believes they can properly design a db; 99.9% of them have no real clue about how to do it properly, however.

Ask them one thing, like:: Give a sample "supertype" with "subtypes" :: and they're completely lost.

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".

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

ScottPletcher (1/22/2013)
Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
Sean Lange (1/22/2013)
GilaMonster (1/22/2013)
Sean Lange (1/22/2013)
ScottPletcher (1/22/2013)
DiverKas (1/22/2013)
1) Do NOT let your C#/VB/ASP/Pascal guys design the database.
2) Do NOT let your DBA's design the database.
3) Hire a Data Architect.
Just saying. The first 2 groups have very biased views of how data should be structured for VERY different reasons, neither of which usually solves the entire problem.
#2 is NOT necessarily true -- it depends on the DBA. I have long-term experience in all phases of the design process.
Also, if you hire someone who's solely a Data Architect, then you have to hire an additional person to convert the logical model into a physical model.
Neither is #1. There is no rule that says a developer is unable to design a proper database because they are a developer.
I'm a developer (SQL mostly, not front end). I'd argue that I can design at least a passably good database.
Gail we are saying the same thing. I think anytime someone starts segregating abilities based on job title they are destined to get snowballed. I too am a developer and would like to think I could cobble something usable together.
Perhaps. But I would not want someone who had not been a full-time database designer or DBA at some point to head a db design. Based on my past experiences, (almost) pure developers can't seem to forget physical details long enough to do a proper logical design. Any who could do it properly would be the rare exceptions, not the normal rule.
I agree it would be the exception but the "rules" as posted sounded like absolutes and I disagree with that sentiment.
Again, my exception is only for someone who had 1+ years full-time experience doing database design.
Almost every developer I've ever met believes they can properly design a db; 99.9% of them have no real clue about how to do it properly, however.
Ask them one thing, like:: Give a sample "supertype" with "subtypes" :: and they're completely lost.

Sadly it is not simply because they don't understand data modeling. Those same people could not identify a super class and subclass example in OOP either. Let's face it, there are far too many totally incompetent people in this field.

By your definition I have no real clue about how to go about data modeling properly. I would disagree with you there. Am I the best? Absolutely not. But to simply discount my ability because it was not my primary job focus for at least a year comes across as arrogant and misguided. A person and the knowledge they possess go beyond the job title.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dwain Camps SSC Guru Points: 86948 More actions · Answer 13

Don't get me started! :w00t:

I do however wish to hear the further discussion. 😛

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

ScottPletcher SSC Guru Points: 101100 More actions · Answer 14

I think what's arrogant is for a developer to assume that they have the professional skills of another a DA/DBA job when they've never done. Not sure why everyone assumes they can do what a DA/DBA does even w/ NO job experience; rather interesting phenomenon really.

Are there exceptions? I'm sure there are. But you can't set up rules that handle the 0.1% of exceptions.

I was a developer for several years, including OO (went to some OO design classes too). But I wouldn't presume to claim I'm currently qualified to head up a development project, OO or not. Or even a code design project; although I believe I could provide valuable input on code design, I'm not qualified to lead the process.

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".