A
AS
Hello,
I have a very simple DB containing PRODUCT and CUSTOMER tables. I also have
another table called EVENT which is used to stored events relating to
Customers and Products e.g. Customer wants to purchase a certain product.
When the event occurs it is stored in the event table along with a status
code and a note. The status code can have one of 3 states e.g Created -
Also, I have been asked to keep a history of the events so I wont delete an
event when a new state is set.
My problem is table design. Would it be better if the status code and note
was moved to another (new) table or simply each time a new status change is
made just add a new entry into the EVENT table?
Im writing my program in C#. When inserting a new EVENT status I do the
following in my program:
1. Get new event (from user).
2. If the event is newly created then simply insert it into the
database.
2. Else SELECT from the EVENT table the current status of the event.
3. Check to see if the new event status is a valid NEXT state e.g. an
Active state MUST follow a Created state.
4. If the state is valid, insert the new event.
The majority of this code is placed in my C# application. I would prefer to
do it in a Stored Proc, but im using Access so i cant. Is there a more
efficient way I can I go about implementing a state transition column into
the database and how can I write a transaction which will change this
transition more efficiently without me writing lots of C# code.
Thanks,
AS
I have a very simple DB containing PRODUCT and CUSTOMER tables. I also have
another table called EVENT which is used to stored events relating to
Customers and Products e.g. Customer wants to purchase a certain product.
When the event occurs it is stored in the event table along with a status
code and a note. The status code can have one of 3 states e.g Created -
status note is used to store addition information about the status code.
Also, I have been asked to keep a history of the events so I wont delete an
event when a new state is set.
My problem is table design. Would it be better if the status code and note
was moved to another (new) table or simply each time a new status change is
made just add a new entry into the EVENT table?
Im writing my program in C#. When inserting a new EVENT status I do the
following in my program:
1. Get new event (from user).
2. If the event is newly created then simply insert it into the
database.
2. Else SELECT from the EVENT table the current status of the event.
3. Check to see if the new event status is a valid NEXT state e.g. an
Active state MUST follow a Created state.
4. If the state is valid, insert the new event.
The majority of this code is placed in my C# application. I would prefer to
do it in a Stored Proc, but im using Access so i cant. Is there a more
efficient way I can I go about implementing a state transition column into
the database and how can I write a transaction which will change this
transition more efficiently without me writing lots of C# code.
Thanks,
AS