Development to QA to PROD migration

Hi...

Thanks in advance for any responses.

First some background:

We have identical databases in our development, QA and Production environments. As part of our application development we actually generate data. As a result, we port the data from several tables generated in our development environment to our QA enviroment for testing and then to our Production environment for deployment along with the application changes.

Since the involved tables have a foreign key to another table which is *not* migrated, part of the process includes updating that foreign key from environment to environment. The primary key involved is a unique 1 up number that is inserted by trigger and drawn from a sequence.

The question:

The perception from my colleagues is that updating the foreign key value is an unnecessary step that is prone to error (I personally disagree - we use TOAD, so we can replace the value when generating the export script-no real issue imho).

Their proposed solution is to remove the sequences and triggers from the QA and Production environment (leaving the Dev environment untouched). When we create a record in the Dev environment the Primary key would be generated. We would then port that value to the QA and then Prod. Then we can move the database records from environment to environment without changing the foreign key.

I have a few problems with this solution including: Having the integrity of one database dependent on values generated in another database and also having a development environment not match the production environment. I'm also concerned about the unintended consequences of this approach (is there greater risk of undermining the integrity of the environments, will addressing the database in QA and PROD become more cumbersome, etc.)

In general it just seems like a wacky solution to a very minor inconvenience but I need to articulate specific problems in order to avoid this happening (also, if it *is* a legitimate approach I'd like to know that too, of course...)

Can any comment on the type of approach, either pro or con, both from experience and from a theoretical perspective?

Thanks...

Quote:

---

Since the involved tables have a foreign key to another
table which is *not* migrated, part of the process includes
updating that foreign key from environment to environment.
The primary key involved is a unique 1 up number that is inserted
by trigger and drawn from a sequence.

---

Both are wrong!
What the above implies is that those "parent" tables which are *not* migrated have different data (keys) from production?

To avoid this, it should be the other way around -- disable triggers in development and clone data from production.

All foreign keys to these "parent" tables from the tables to be migrated should then be compatible with the production values.
:rolleyes:

Could you:
1 - put the data that is common to all db's in a file, declared as an external table
2 - read that with some PL/SQL that retrieves the FK values from the current db to make the inserts.

You should be able to "package" that as part of your release management.

Hi LKBrwn...
"..What the above implies is that those "parent" tables which are *not* migrated have different data (keys) from production?"
That is correct - since more records may be added/deleted in Development, etc. the primary keys delivered by the sequence are different.
I think I mis-spoke though - the parent table that houses the primary key is technically also 'migrated' -- since it's only a single record we add we just manually recreate it in QA and Prod. So we can't clone that data from Production since it not there yet (smile)

Quote:

---

Originally Posted by DaPi

Could you:
1 - put the data that is common to all db's in a file, declared as an external table
2 - read that with some PL/SQL that retrieves the FK values from the current db to make the inserts.

You should be able to "package" that as part of your release management.

---

The only problem is that's more complicated than the current process of just replacing the foreign key value in our import file. FYI, TOAD, the tool we use, allows you to globally substite a column value for an import through a GUI. Very straightforward - just put the value in a text field and the value is used in place of the existing table values in the imprt script...

We have a similar issue at our site for a type of reference data but in our case the 'parent' and 'child' tables are the same (in a hierachial relationship).
What we do is generate the new record in Production to get the 'real' sequence number, then create the record manually in the Dev/Test areas. The child records can then be created in Dev/Test and populated forward (with the advantage that we can, where necessary, include in our code the primary key value of the parent knowing that it will be the same in all environments).
The underlying problem, for us, is that it represents a poor data model where a table has an artificial primary key and there is no combination of 'real' columns (as in ones that can be readily understood in business terms) that uniquely identifies a row.
Try examining the business model behind your design and see whether the sequence number of the parent table is really an appropriate primary key. Since you suggest that you create what is essentially the same record in production but that it has a different primary key there, there may be other fields that would make a better primary key.

Hi Gamyers...

Thanks for the feedback. It seems like you're doing what was proposed by my colleagues but in reverse. If we do go down that road I'd definitely be more comfortable removing the primary key from our development database rather than our Production DB. Also, I'm actually a fan of 'meaningless' primary keys, so the use of the sequenced column for that purpose isn't an issue for me...