Database Boolean and DateTime naming convention












1














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?










share|improve this question




















  • 1




    SQL Server doesn't support boolean, but your naming convention is reasonable.
    – Gordon Linoff
    Nov 12 '18 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
    Nov 12 '18 at 12:35








  • 2




    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
    Nov 12 '18 at 12:37






  • 5




    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
    Nov 12 '18 at 12:38








  • 1




    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
    Nov 12 '18 at 13:15
















1














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?










share|improve this question




















  • 1




    SQL Server doesn't support boolean, but your naming convention is reasonable.
    – Gordon Linoff
    Nov 12 '18 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
    Nov 12 '18 at 12:35








  • 2




    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
    Nov 12 '18 at 12:37






  • 5




    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
    Nov 12 '18 at 12:38








  • 1




    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
    Nov 12 '18 at 13:15














1












1








1







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?










share|improve this 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 tsql naming-conventions






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 12 '18 at 12:47









a_horse_with_no_name

292k46446540




292k46446540










asked Nov 12 '18 at 12:32









Miguel Moura

7,97436105213




7,97436105213








  • 1




    SQL Server doesn't support boolean, but your naming convention is reasonable.
    – Gordon Linoff
    Nov 12 '18 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
    Nov 12 '18 at 12:35








  • 2




    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
    Nov 12 '18 at 12:37






  • 5




    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
    Nov 12 '18 at 12:38








  • 1




    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
    Nov 12 '18 at 13:15














  • 1




    SQL Server doesn't support boolean, but your naming convention is reasonable.
    – Gordon Linoff
    Nov 12 '18 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
    Nov 12 '18 at 12:35








  • 2




    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
    Nov 12 '18 at 12:37






  • 5




    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
    Nov 12 '18 at 12:38








  • 1




    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
    Nov 12 '18 at 13:15








1




1




SQL Server doesn't support boolean, but your naming convention is reasonable.
– Gordon Linoff
Nov 12 '18 at 12:34




SQL Server doesn't support boolean, but your naming convention is reasonable.
– Gordon Linoff
Nov 12 '18 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
Nov 12 '18 at 12:35






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
Nov 12 '18 at 12:35






2




2




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
Nov 12 '18 at 12:37




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
Nov 12 '18 at 12:37




5




5




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
Nov 12 '18 at 12:38






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
Nov 12 '18 at 12:38






1




1




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
Nov 12 '18 at 13:15




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
Nov 12 '18 at 13:15












1 Answer
1






active

oldest

votes


















1














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






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53262297%2fdatabase-boolean-and-datetime-naming-convention%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    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






    share|improve this answer


























      1














      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






      share|improve this answer
























        1












        1








        1






        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






        share|improve this answer












        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







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 14 '18 at 21:50









        SandroMarques

        1,5381523




        1,5381523






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53262297%2fdatabase-boolean-and-datetime-naming-convention%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            Xamarin.form Move up view when keyboard appear

            Post-Redirect-Get with Spring WebFlux and Thymeleaf

            Anylogic : not able to use stopDelay()