defining relationships and RI in an existing DB

[cpberg1]

Hey all,
I have recently begun to admin a split DB that has been in use for many years. The DB has millions of records in about 70 tables. The creator of the DB has no relationships defined and therefore no RI on any fields.:banghead: When I first discovered the blank relationship window I was hoping that the tables were just hidden but I added all tables and verified that there where no joins anywhere.

As you can imagine there are many child records with no parents and developing queries, forms and reports takes more time than is necessary.

I'm starting along the process with a backup copy of the backend defining the relationships one-by-one and removing orphaned records so that I can get all the relationships developed prior to doing similar on the production version.

Is there any good reason why the previous admin/creator would not have any relationships defined? What pitfalls should I be aware of? When I begin defining relationships on the production database will I need to have all users out of the front ends (no-one using the database)?

I appreciate any thoughts or feedback about what I can expect as I work through this process!

 

[James Dudden]

There is only one reason for not having relationships in a database (that require relationships) and that's bad design.

This sounds like a big job but you're going about it the right way and I think you've highlighted the main potential issue and that's orphaned records.

I don't think you'll be able to create/amend relationships if the database is already in use so probably best to do this with everyon out.

Good luck!

 

[Pat Hartman]

Is there any good reason why the previous admin/creator would not have any relationships defined?

In a word - NO!!!! The only databases where you might not see RI are those with no human interface for updating. All the data is imported from other sources and the database is used only for reporting. Even in this type of database, I would include RI simply to mke sure the data gets imported correctly.

Adding RI to a production database is quite difficult when there are data anomolies that need to be fixed along the way. Doing it once for practice in a test database will show you where the data is bad. You'll need to keep very good records because you'll need to do all the steps again in a compressed timeframe, probably over a weekend if your users don't work weekends.