February 9, 2010 at 9:59 am
I have inherited a 100GB database with over 200 tables. All the tables have primary keys defined, but there are no foreign key relationships defined.
Before I embark on the project of building all these foreign keys and cleaning up any invalid data, I thought it might be prudent to ask if there are any strategies or approaches I should keep in mind, perhaps to avoid any common pitfalls.
February 9, 2010 at 2:03 pm
A couple of things.
1-Script out the structure of all tables. Then you can search other tables for columns named the same as your primary keys. The assumption is that the foreign keys are named the same as the primaries.
2-Look at the application that created the data. Hopefully referential integrity was enforced there.
3-When in doubt reality-check your assumptions. The database is a model, understand the model, talk to the business people as needed. Maybe the data says that a contact can only have one address. Really? What if this is not true in reality but incorrectly imposed in the data/application. Just an example.
February 10, 2010 at 5:59 am
Test, test, test. Introducing constraints into a system that had none previously will, in all likelihood, lead to errors. Go slow. Try to identify the areas of data that are most likely to need the constraints, probably the places where there is existing data integrity problems. You'll need to plan for fixing these, and it will probably require manual work.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 10, 2010 at 6:52 am
- Talk to the application support contact person.
- DRI may indeed actually NOT be desired at all.
- Keep in mind, this will need the data feed process to be revised to see if it meets the order of processing matching your DRI hiarchy.
- Also keep in mind to support your DRI with full matching indexes ! (should be the default attitude)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 10, 2010 at 6:53 am
Thanks for the tips Emily- fortunately it looks like most of the naming is consistent, though there are a few exceptions.
Grant, I think "go slow" is great advice. Parts of this model are highly de-normalized - a key from one master table table is used in most other tables - and I think that is a good place to start.
Also, this seems like a perfect time to construct a data dictionary. I know there are a couple of commercial tools out there, but I think since I'll be under the hood creating FK's and constraints, maybe the manual entering of meta-data might be the way to go.
February 10, 2010 at 6:58 am
ALZDBA - This db is primarily used by a web application, and I work close with the front-end team. They will definitely be key players in this project. The DRI is desired by everyone on the team. 🙂
Could you elaborate on "support your DRI with full matching indexes"? There are a good number of indexes in place already... something in particular I should keep in mind?
February 10, 2010 at 7:42 am
What i meanth to mention is that if you have multi column DRI, you should provide a DRI matching index ( multi column, exact column sequence ) to support the dri queries.
Unless it is prooven this index hurts performance. ( on a index per index basis to be prooven !)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply