Database Design Question

I am currently working on an RDBMS (Oracle) based web work-flow application.

The workflow rules are very simple. When a record is created, it is initially unapproved, the data is called Temporary. When a Manager approves, it becomes approved, and called Production data.

I designed the database with two tables - the first table (Main Table) contains all business data. Each record in this table has a unique primary key. The second table (State Table) just maintains the state of the record, with a ForeignKey pointing to the record in the Main Table. For each row in the Main Table, absence of a corresponding row in State Table, or a row with state not equal to 'Approved' indicates that the record is not in Production State. A Left Outer Join will give me all data, while a simple Join gives me only Production Data.

My customer has a different model in his mind. He says, I must create two identical tables, call one Temporary, and other Production. When the Manager approves a record in temporary, I copy the record over physically using a Trigger.

My intuition says, my customer is not right. Or is he right? I am not able to provide enough rationale. The only valid reason I could come up with so far, is scalability. If he has a third state, he needs to have a third table and so on.. I feel that there are many issues like the risk of maintaining two sets of data, problems during rollbacks etc. with this two table model.

I will be interested in listening your valued opinion.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 21 1999 - 16:30:27 CST