Imagine I've an Orders table with posts OrderID (PK), CustomerID, CustomerOrderN and so forth. Now I have to give a possible ways to "close" orders with indicating grounds why order is closed (for instance "offered cost is simply too high for customer", "unavailableInch, "customer requested to shut an orderInch).
Question 1. What will be the best and proper method to implement this in database design?
I believe the easiest way would be to create Closed column which may be null (if order is open) and when not null (i.e. if order is closed) then your value indicates another table OrderCloseReasons.
Question 2. Let's say I curently have :) a boolean column Closed in Orders table, and today I have to implement possible ways to specify reasons of closing. I can not refactor much since the product is not too small already, therefore it is difficult to refactor the database plan. An amount be the easiest method to add possible ways to specify reasons of closing in this situation?
I believe when I simply add CloseReasonID column to Orders table it won't be good. But I don't know.
Thanks ahead of time.
For those who have a lot of specific close reasons that you would like to use, and when you have to have the ability to perform queries with different specific kind of close reason (say get simply by reason X), then that which you suggest may be beneficial - null, or perhaps a close reason ID.
However, if you do not need searching etc., you can only have a column closed, and the other column which describes why it had been closed.
I would suggest a StatusCode column (most likely int datatype) along with a seperate table that contains a StatusCode (int) and StatusCodeDescription (varchar). That provides you more versatility if you and your clients think about another possible status later.
Personally, I'd perform the research table while you suggest, but refer to it as Status. I'd result in the foreign answer to the Status table within the Orders table be an int, not null having a default of just one.
Then your records within the Status table could be (1)Open, (2)Closed reason one, (3)Closed reason two, etc. That method for you to map for an enum inside a greater layer without needing to do anything whatsoever special inside your saved methods. That's, whatever you do is range from the StatusID inside your Choose rather than needing to fool around with handling the null as meaning one factor and also the research values as the second.
Merging null and reason into one nullable text column isn't wise decision because it might not easily be readable by other developers as well as on your part after couple of days.
Getting two posts one either boolean or status code as per David, and seperate column for reason. This provides more readability for your design.
However I goes a measure ahead and finest practice in finance related software programs are to possess built-in audit trail, because I'd certainly prefer to know..
"Who closed an order?Inch "What time that it was closed?" "Maybe it was reopened?"
The audit trail usually includes another table for instance,
OrderAudit -> AuditID -> OrderID -> ChangeMadeBy -> ChangeDateTime -> ChangeColumn -> ChangeValue
That will give full treatments for who did with this order so when.
One more table holding the close reason behind just individuals orders which are closed.
It does not violate 1NF since you don't introduce nulls within the database schema, and you've got probably the most absolute guarantee that the changes will not affect existing stuff (that you simply clearly indicated is really a major concern within this situation).
See e.g. "What First Normal Form Really Means" in Date on Database: Documents 2000-2006 (Springer-Verlag, 2006). Possibly also findable like a stand alone paper on the web.
And from "Summary of database systems", 8ed. : "A relvar is within 1NF if and only when, in each and every legal worth of that relvar, every tuple consists of exactly one value for every attribute." (and null cannot often be something, since it is not comparable to itself).