July 31, 2017 at 7:36 am
I was thinking about how to title this for a few milliseconds and I couldn't come up with anything better. I'm building a new database and while creating a table to store invoices I noticed I did something I've done several times in the past. Check out this digest of the structure:
InvoiceId INT
DateAdded DATE
IsPaid BIT
DatePaid DATE
I stopped and thought "wait a second... why would I add a BIT if I could assume the value from the fact that DatePaid is not NULL".
I know I've done this in the past and dont know why I'm not really fuzzed about it, all my DALs work with this as it is and my reports all work fine.. In turn, this works, but is it as useless and antipatternish as my epiphany seems to point out? or is there anything else there that might salvage this practice?
July 31, 2017 at 7:46 am
Nelson Casanova - Monday, July 31, 2017 7:36 AMI was thinking about how to title this for a few milliseconds and I couldn't come up with anything better. I'm building a new database and while creating a table to store invoices I noticed I did something I've done several times in the past. Check out this digest of the structure:InvoiceId INT
DateAdded DATE
IsPaid BIT
DatePaid DATEI stopped and thought "wait a second... why would I add a BIT if I could assume the value from the fact that DatePaid is not NULL".
I know I've done this in the past and dont know why I'm not really fuzzed about it, all my DALs work with this as it is and my reports all work fine.. In turn, this works, but is it as useless and antipatternish as my epiphany seems to point out? or is there anything else there that might salvage this practice?
IsPaid as an independent, physical column is redundant. If you need it for any reason (filtered index, perhaps) consider making it computed, persisted or creating a view with it – depending on how you intend to use it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 31, 2017 at 7:47 am
This is more of a "know your data" scenario. NULL values can themselves represent data, as such, but might not necessarily represent what the column represents.In this case, you have a column DatePaid, and yes in this case it would (in my opinion) be quite all right for the value NULL in this column to represent "Not paid" (Mr Celko might disagree with me there).
On the other hand, if you really want to have data derived from columns like that, then you could also use a VIEW or a computed column. Then you can derive your value from there, you're not storing a bit value with every record (not that bits use a lot), and others who use your data don't have to assume that NULL = "Not paid". As it's computed as well, it avoids someone updating the DatePaid column and not the IsPaid column (therefore possibly resulting in a scenario where IsPaid = 0 but Datepaid = '2017-07-31').
EDIT: Phil beat me to it :P.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 31, 2017 at 8:18 am
Nelson Casanova - Monday, July 31, 2017 7:36 AM... why would I add a BIT if I could assume the value from the fact that DatePaid is not NULL".
The only situation I can think of where you would need both, is if there was some kind of transaction where the invoice could be paid but the date that it was paid was unknown. Without the bit column in your table, DatePaid IS NULL represents both "invoice is not paid" and "the paid date is not known". If you don't have a situation where the invoice is paid but the date is unknown, then yes this is redundant as the others have said.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply