January 21, 2013 at 2:52 pm
what are things one need to always keep in mind when designing a new database for an application
January 21, 2013 at 3:10 pm
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
January 21, 2013 at 3:13 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 22, 2013 at 10:57 am
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.
January 22, 2013 at 11:15 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 22, 2013 at 11:23 am
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:-):-)--------------------------------------------------------------------------------
January 22, 2013 at 1:13 pm
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/
January 22, 2013 at 1:25 pm
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
January 22, 2013 at 2:15 pm
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/
January 22, 2013 at 3:20 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 22, 2013 at 3:50 pm
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/
January 22, 2013 at 4:44 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 22, 2013 at 10:02 pm
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/
January 22, 2013 at 11:28 pm
Don't get me started! :w00t:
I do however wish to hear the further discussion. 😛
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
January 23, 2013 at 8:26 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply