April 26, 2011 at 11:04 am
Hello,
I am in the financial domain and am setting up a staging area between databases in different servers.
My question is: What are the typical constraints practiced in a database and what type of performance hit would they have on the database?
For example, using generic column names:
A trigger with a condition for Effective_Start_Date to always be less than Effective_End_Date
Would this be useful on a frequently updated table? What about a check constraint on a status column between a list of values or a check constraint on a flag column? Can someone suggest any other such common constraints practiced using generic column names?
Thank you.
-Sandeep Kanuri
April 26, 2011 at 1:56 pm
There is no "best practice" or "generic should always have" list of constraints. The application, ddl and business rules as well as db knowledge go into this. There is no easy button to build a list of "all tables with these criteria should have this checklist of constraints". I personally would tend to shy away from constraints like making a value be in a certain set of numbers. That sounds like it would be better suited as a foreign key to a table that holds those values for a status.
_______________________________________________________________
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/
April 26, 2011 at 4:40 pm
Welcome to the wonderful world of data modeling.
The short answer is: there is not silver bullet, it all depends on your business requirements and the way you decide to implement them. By the way, hard coding constraints is not a good idea.
Do you have an ER model already?
If not, that's the first thing you have to put in your to-do list.
If the answer is yes, expand your model until all your "constraints" are refllected in it.
Hope this helps.
_____________________________________
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.April 27, 2011 at 7:18 am
I created the data model using database diagrams feature (due to a lack of tools).
So the final answer is: It Depends. I suppose if it really was that easy, no one would hire DBA's anymore.
Well, it was a long shot anyway. Thank you for your time gentlemen.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply