Re: Database Design Question

I think you're both wrong, but you are closer than the client. The concept of your "Main Table" is correct as long as you don't need to keep this information over time. The "State Table", however, needs some additional information if you want to keep a record of what changes over time. When a transaction changes "state" from temporary to approved, it sounds like you want to track when that state changed and what it changed to. I would design it with:

MAIN_TABLE
transaction_id number(10) PK
other_business_data whatever data type

STATE_TABLE

transaction_id      number(10) PK
transition_dt         date              PK
state_cd                varchar2(10) PK

When the record in MAIN_TABLE gets created, a record gets put in STATE_TABLE of temporary with sysdate timestamp and the transaction_id from the MAIN_TABLE. When the state changes from temporary to approved, another record gets put in the STATE_TABLE with the transaction_id, the sysdate, and a state_cd of approved.

There are three advantages of this design over the original author's design. First, the current status of the transaction is ALWAYS retrieved by the same query (An equijoin between MAIN_TABLE and STATE_TABLE with the transaction_id and max(transition_dt)). Second, you track the complete history over time of the transaction's approval history. Lastly, if you add another approval state, you just pop another value in for state_cd (of course you also have state_cd defined in another table with an fk to STATE_TABLE). The client's design, on the other hand violates the basic rules of normalized data. Data belongs in one place. You shouldn't need two queries to figure out where transaction number xyz is hiding. I frequently encounter this problem when dealing with clipper programmers...

sriniv79_at_hotmail.com wrote in message <83ov1t$f7g$1_at_nnrp1.deja.com>...
>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 Wed Dec 22 1999 - 08:05:30 CST