Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database Design Question

Re: Database Design Question

From: Van Messner <vmessner_at_netaxis.com>
Date: Tue, 21 Dec 1999 18:37:48 -0500
Message-ID: <X7U74.502$Ji1.33510@tw11.nn.bcandid.com>


In my opinion you are right about the problems with two (or more) tables. You might possibly want to keep a timestamp for the moment when a row was changed from unapproved to approved in case you need to reconstruct historical information at some point in the future.

Van

<sriniv79_at_hotmail.com> wrote in message news: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 Tue Dec 21 1999 - 17:37:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US