How to model Open/Closed statuses in a database?

Question

Hello,

Imagine I have an Orders table with columns OrderID (PK), CustomerID, CustomerOrderN and so on. Now I need to add a possibility to "close" orders with specifying a reason why order is closed (for example "offered price is too high for customer", "not available", "customer asked to close the order").

Question 1. What would be the best and correct way to implement this in database design?

I think the best way is to create Closed column which can be null (if order is open) and if not null (i.e. if order is closed) then the value points to another table OrderCloseReasons.

Question 2. What if I already have :) a boolean column Closed in Orders table, and now I need to implement possibility to specify reasons of closing. I can't refactor much because the system is not so small already, so it's hard to refactor the database scheme. What would be the best way to add possibility to specify reasons of closing in such a case?

I think that if I just add CloseReasonID column to Orders table it will not be good. But I am not sure.

Thank you in advance.

Answer 1

If you have a bunch of specific close reasons that you would want to use, and if you need to be able to perform queries based on a specific type of close reason (say get all by reason X), then what you suggest is a good idea - null, or a close reason ID.

On the other hand, if you don't need searching etc., you could simply have a column closed, and another column which describes why it was closed.

Answer 2

I would recommend a StatusCode column (probably int datatype) and a seperate table containing a StatusCode (int) and StatusCodeDescription (varchar). That gives you more flexibility if you or your end users think of another possible status later.

Answer 3

Personally, I would do the lookup table as you suggest, but call it Status. I would make the foreign key to the Status table in the Orders table be an int, not null with a default of 1.

Then the records in the Status table would be (1)Open, (2)Closed reason one, (3)Closed reason two, etc. That way you can map to an enum in a higher layer without having to do anything special in your stored procedures. That is, all you do is include the StatusID in your SELECT instead of having to mess around with handling the null as meaning one thing and the lookup values as another.

Answer 4

Merging null and reason into one nullable text column is not good idea because it may not be easily readable by other programmers or even by you after few days.

Having two columns one either boolean or status code as specified by David, and seperate column for reason. This gives more readability to your design.

But I will go one step ahead and best practice in finance related software is to have built in audit trail, because I would certainly like to know..

"Who closed the order?" "What time it was closed?" "Was it reopened?"

The audit trail usually consists of another table for example,

OrderAudit -> AuditID -> OrderID -> ChangeMadeBy -> ChangeDateTime -> ChangeColumn -> ChangeValue

Which will give full control over who did what with this order and when.

Answer 5

An additional table holding the close reason for just those orders that are closed.

It doesn't violate 1NF because you don't introduce nulls in the database schema, and you have the most absolute guarantee that your changes won't affect existing stuff (which you clearly indicated is a major concern in this case).

EDIT

See e.g. "What First Normal Form Really Means" in Date on Database: Writings 2000-2006 (Springer-Verlag, 2006). Possibly also findable as a standalone paper on the internet.

And from "Introduction to database systems", 8ed. : "A relvar is in 1NF if and only if, in every legal value of that relvar, every tuple contains exactly one value for each attribute." (and null cannot possibly be a value, because it is not equal to itself).