database design question

[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 -

Active -> Success or Fail. The status can only change in this order. The

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

 

[Jeff Boyce]

Please don't multipost (post the same question in multiple groups). If you
feel a question is germane in a limited number of groups, crosspost,
selecting relevant newsgroups in your "TO:" field. This way, an answer in
one is posted in all.

Are you sure you're in the right newsgroup space? This series of 'groups
addresses the use of Microsoft Access.

 

[Larry Daugherty]

Which is better is a judgement call on your part based on which result set
meets the goals of your design. If you include a datetime field in the
Event record then you'll be able to generate a complete history of related
event changes. If you only want to know the current status and don't care
about when and how it changed then simply keep the current status in the
parent table and do away with the event table. My bias would be to save
change history, it can be extremely valuable. If you do save change history
you'll find that your Event table grows rapidly. Not to worry, it takes a
long time to get to 2 gig. It is easy to create a purge or archive function
to get rid of the aged data.

HTH