Re: Database Design Question

One has to keep in mind that the customer is always right. But it is your job to give the customer enough information to make the proper decision. Part of that information is the fact that the customer's solution is difficult for more than two states (as you suggested). You are also adding unnecessary complexity by doing it the customers way cuz you need a (unnecessary) trigger. I agree with another poster that this can be easily accomplised with only one table. Using one table still gives you scalability while reducing complexity.

HTH,
Brian

sriniv79_at_hotmail.com wrote:
>
> 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 - 09:18:07 CST