Oracle Database Discussions

I have a simple table with 3 columns: ID, DML_ID and VALUE. The table was designed to be a metadata table with values for certain DML_IDs. The table has been maintained by IT. However, now the users wants to have control over some of the values. Let's call it User_Maintained values. IT does DML on this table and migrate the change to acceptance and production. At the same time users do DML in production. This causes ID conflict between dev, acc and prod.

My solution is to use 2 sequences, one for IT_maintained IDs the other for User_maintained IDs. One of the sequences starts with a much bigger number like 1000000 to avoid the conflict. Create a before insert trigger on the table to pick the right sequence number depends on if the DML is IT or user maintained.

My question here is, is it ok to use 2 sequences on one table? Is it against best practice of Oracle?


Another idea from my co-worker is, use single sequence instead of 2. The sequence will reside in production database and all the 3 databases (dev, acc and prod) will get ID from the same sequence through dblink. Personally I don't like the idea because I don't think getting sequence from production to be used in development is the "best pratice" of Oracle. Although I don't know where I can find the document to support my opinion.


Any idea is appreciated!!

Sharon