Hi,
I've got a log-table that records events regarding other objects.
Those events have a state that shows the progress of further work on
this event.
They can be open, accepted or rejected.
I don't want to be able to insert addition events regarding an object X
as long there is an open or accepted event.
On the other hand as soon as the current event gets rejected a new event
should be possible.
So there may be several rejected events at any time but no more than 1
open or accepted entry.
Can I do this within the DB so I don't have to trust the client app?
The layout looks like this
Table : objects ( id serial, .... )
Table : event_log ( id serial, oject_id integer references objects.id,
state integer, date_created timestamp, ... )
where state is 0 = open, -1 = reject, 1 = accept
I can't simply move rejected events in an archive table and keep a
unique index on object_id as there are other descriptive tables that
reference the event_log.id.