CODEWITHSUNDEEP
mysqlsqldatabase-design
Michal
Michal

Reputation: 2029

Optimal solution for: Keeping state of entity in database

Problem

When I design structure of a database, I often work with tables that can have a state assigned. For example a response for an offer - this response table can, for instance, have these states:

  1. waiting - response was created and waits for approval by owner of offer
  2. cancelled - response was cancelled by its author
  3. approved - response was approved by the author of offer
  4. rejected - response was rejected by the author of offer
  5. expired - response expired together with associated offer

I am considering these two solutions

1. Solution

Create table response_state and keep its key in the response table as foreign key

2. Solution

Put approved/rejected/cancelled logical value columns into response and create view view_response_state that will contain a column with state name according to values in these columns and the expiration date.

For example, if approved is false, rejected is false, cancelled is false and expiration_date < today, then state is "waiting" etc.

Question

My question is, which approach would you choose? Or is there a better approach?

Upvotes: 1

Views: 889

Answers (1)

O. Jones
O. Jones

Reputation: 108641

In my opinion you need three tables.

One is to be called "response_state". It contains five rows, one for each of your response names. If you need to add a new response name, just INSERT it to this table. It has the column "response_state_id." Little tables like this are often called codelist tables.

Another is to be called "offer". It will have an offer_id and other information as needed about the offer.

The third is "response." It contains the following columns.

response_id          pk, autoincrement
offer_id             fk to offer table
response_state_id    fk to response_state table
response_timestamp
(other columns relating to the response as needed)

This table works as follows: Anytime the state of a response changes, you INSERT a row to this table showing the new state. You never UPDATE these rows. You might DELETE old ones in a purge process for completed transactions.

When you need to find the current state of an offer you give a query like this. It pulls only the most recent response to each offer from the table.

SELECT r.offer_id, r.response_state_id, rs.response_state_name
  FROM response AS r
  JOIN response_state AS rs ON r.response_state_id = rs.response_state_id
  JOIN (
          SELECT MAX(response_id) as latest_id,
                 offer_id
            FROM resp
           GROUP BY offer_id
        ) AS recent ON r.response_id = resp.latest_id

This is a really cool way to handle this because it retains the history of responses to each offer. Because it's an INSERT-only solution it's inherently robust against various kinds of race conditions if lots of responses come in on top of each other.

Upvotes: 3

Related Questions