Database Boolean and DateTime naming convention

Question

I am creating a Posts SQL table where I need to save:
1. If the post is approved;
2. When the post was approved.

I am considering naming these columns IsApproved and ApprovedAt:

create table dbo.Posts ( Id int identity not null, IsApproved bit null, ApprovedAt datetime null )

Is there any convention for such a case?

SQL Server doesn't support boolean, but your naming convention is reasonable. — Gordon Linoff
– Gordon Linoff, Commented Nov 12, 2018 at 12:34
That was a typo. I corrected to bit. I could also just have only one column named "Approved datetime" which when Null would mean that is not approved. Not sure if it would be worse for queries and confusing in some other cases. What do you think? — Miguel Moura
– Miguel Moura, Commented Nov 12, 2018 at 12:35
Having two columns does allow you to get into contradictory scenarios, an Approved date with no IsApproved set or visa versa. I would implement the IsApproved as a computed column, or logic within your DAO — Andrew
– Andrew, Commented Nov 12, 2018 at 12:37
Never use a nullable BIT column unless there's a really good reason to, because you raise the specter of what it means for a binary condition to be "unknown". Prefer NOT NULL and a sensible default (in this case, 0). Or, as you mentioned, if it can never be the case that IsApproved = 1 and ApprovedAt IS NULL, then make IsApproved a computed column (IsApproved = CONVERT(BIT, CASE WHEN ApprovedAt IS NULL THEN 0 ELSE 1 END)). — Jeroen Mostert
– Jeroen Mostert, Commented Nov 12, 2018 at 12:38
I would argue against having two separate columns in this case - for the same reason Jeroen Mostert wrote in his comment - A post that is approved must have an approval date, and a post that has an approval date is obviously approved - so there really is no point of keeping the IsApproved column. As for the naming conventions, Is... and ...Date are very reasonable for bit and date. — Zohar Peled
– Zohar Peled, Commented Nov 12, 2018 at 13:15

SandroMarques · Accepted Answer · 2018-11-14 21:50:02Z

Is there any convention for such a case? No.

But I like your choice (is also my convention for booleans and dates) because:

IsApproved is a boolean and starts with Is
ApprovedAt is a date and ends with At

I share some "conventions":

https://www.periscopedata.com/blog/better-sql-schema

https://github.com/ghowland/Dasonic

Collectives™ on Stack Overflow

Database Boolean and DateTime naming convention

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related