SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


things to know when designing a new database


things to know when designing a new database

Author
Message
DBA24
DBA24
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 28
what are things one need to always keep in mind when designing a new database for an application
GilaMonster
GilaMonster
SSC Guru
SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)

Group: General Forum Members
Points: 549307 Visits: 47743
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
ScottPletcher
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46455 Visits: 8046
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
DiverKas
DiverKas
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1071 Visits: 460
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
ScottPletcher
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46455 Visits: 8046
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
prathibha_aviator
prathibha_aviator
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 271
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
Sean Lange
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148459 Visits: 18575
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)SSC Guru (549K reputation)

Group: General Forum Members
Points: 549307 Visits: 47743
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
Sean Lange
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: General Forum Members
Points: 148459 Visits: 18575
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
ScottPletcher
ScottPletcher
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46455 Visits: 8046
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search