Category entity relationship / subtype relationships design












1















I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to different entities.



Whilst looking for a suitable solution I came across this answer and I wanted to check I am fully understanding the implementation of this approach and understand the potential pitfalls.



Using a schema similar to the one below the ContractId, ContractLineId and VariationId values, along with any future entities, would be generated using a SEQUENCE (using SQL Server 2012) and each generated value would be stored in the Entity table along with its EntityType.



The use of the SEQUENCE across multiple entities would allow for a single Comment and Attachment table and therefore a Comment or Attachment could be created against a Contract, ContractLine and Variation and any future entities.



The Entity table is used as a sort of junction table (or some other term) and would allow queries to return all Attachments against particular entity type(s).



Though things get a little messy if attachments against multiple entities need to be returned along with some information about the entity. For example, if all comments made by a user were to be queried along with the name of the entity the comment was against.



Am I going down the right path or should this be avoided.



enter image description here










share|improve this question
















bumped to the homepage by Community 1 min ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.




















    1















    I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to different entities.



    Whilst looking for a suitable solution I came across this answer and I wanted to check I am fully understanding the implementation of this approach and understand the potential pitfalls.



    Using a schema similar to the one below the ContractId, ContractLineId and VariationId values, along with any future entities, would be generated using a SEQUENCE (using SQL Server 2012) and each generated value would be stored in the Entity table along with its EntityType.



    The use of the SEQUENCE across multiple entities would allow for a single Comment and Attachment table and therefore a Comment or Attachment could be created against a Contract, ContractLine and Variation and any future entities.



    The Entity table is used as a sort of junction table (or some other term) and would allow queries to return all Attachments against particular entity type(s).



    Though things get a little messy if attachments against multiple entities need to be returned along with some information about the entity. For example, if all comments made by a user were to be queried along with the name of the entity the comment was against.



    Am I going down the right path or should this be avoided.



    enter image description here










    share|improve this question
















    bumped to the homepage by Community 1 min ago


    This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.


















      1












      1








      1








      I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to different entities.



      Whilst looking for a suitable solution I came across this answer and I wanted to check I am fully understanding the implementation of this approach and understand the potential pitfalls.



      Using a schema similar to the one below the ContractId, ContractLineId and VariationId values, along with any future entities, would be generated using a SEQUENCE (using SQL Server 2012) and each generated value would be stored in the Entity table along with its EntityType.



      The use of the SEQUENCE across multiple entities would allow for a single Comment and Attachment table and therefore a Comment or Attachment could be created against a Contract, ContractLine and Variation and any future entities.



      The Entity table is used as a sort of junction table (or some other term) and would allow queries to return all Attachments against particular entity type(s).



      Though things get a little messy if attachments against multiple entities need to be returned along with some information about the entity. For example, if all comments made by a user were to be queried along with the name of the entity the comment was against.



      Am I going down the right path or should this be avoided.



      enter image description here










      share|improve this question
















      I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to different entities.



      Whilst looking for a suitable solution I came across this answer and I wanted to check I am fully understanding the implementation of this approach and understand the potential pitfalls.



      Using a schema similar to the one below the ContractId, ContractLineId and VariationId values, along with any future entities, would be generated using a SEQUENCE (using SQL Server 2012) and each generated value would be stored in the Entity table along with its EntityType.



      The use of the SEQUENCE across multiple entities would allow for a single Comment and Attachment table and therefore a Comment or Attachment could be created against a Contract, ContractLine and Variation and any future entities.



      The Entity table is used as a sort of junction table (or some other term) and would allow queries to return all Attachments against particular entity type(s).



      Though things get a little messy if attachments against multiple entities need to be returned along with some information about the entity. For example, if all comments made by a user were to be queried along with the name of the entity the comment was against.



      Am I going down the right path or should this be avoided.



      enter image description here







      sql-server database-design subtypes






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited May 23 '17 at 12:40









      Community

      1




      1










      asked Jul 23 '15 at 13:43









      mheptinstallmheptinstall

      437414




      437414





      bumped to the homepage by Community 1 min ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







      bumped to the homepage by Community 1 min ago


      This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
























          1 Answer
          1






          active

          oldest

          votes


















          0














          I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract.



          Since you asked for potential shortcomings or pitfalls, here are a few you might consider:




          • Without knowing much about your system, it seems strange that a variation must apply to only one contract line. Is there ever a case where a variation might apply to multiple lines? And then a user might want to comment on the multi-line variation in its entirety? If so, this case doesn't appear to be handled right now.


          • It's surprising that there aren't any columns other than EntityType that are common across all entities. Perhaps there should be an EntityName column rather than separate columns ContractName, ContractLineName, and VariationName? This would make the example query you describe in your question far simpler.


          • The data model does not strictly enforce that the Variation contains logically consistent data. It stores both a ContractId and a ContractLineId. The foreign keys for these columns enforce that each is valid independently, but allow for a ContractLineId to be inserted that comes from the incorrect ContractId (that is, different than the ContractId for that ContractLineId in the ContractLine table). One option to address this is to remove the ContractId column from the Variation table.







          share|improve this answer























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "182"
            };
            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: false,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            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%2fdba.stackexchange.com%2fquestions%2f107890%2fcategory-entity-relationship-subtype-relationships-design%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









            0














            I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract.



            Since you asked for potential shortcomings or pitfalls, here are a few you might consider:




            • Without knowing much about your system, it seems strange that a variation must apply to only one contract line. Is there ever a case where a variation might apply to multiple lines? And then a user might want to comment on the multi-line variation in its entirety? If so, this case doesn't appear to be handled right now.


            • It's surprising that there aren't any columns other than EntityType that are common across all entities. Perhaps there should be an EntityName column rather than separate columns ContractName, ContractLineName, and VariationName? This would make the example query you describe in your question far simpler.


            • The data model does not strictly enforce that the Variation contains logically consistent data. It stores both a ContractId and a ContractLineId. The foreign keys for these columns enforce that each is valid independently, but allow for a ContractLineId to be inserted that comes from the incorrect ContractId (that is, different than the ContractId for that ContractLineId in the ContractLine table). One option to address this is to remove the ContractId column from the Variation table.







            share|improve this answer




























              0














              I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract.



              Since you asked for potential shortcomings or pitfalls, here are a few you might consider:




              • Without knowing much about your system, it seems strange that a variation must apply to only one contract line. Is there ever a case where a variation might apply to multiple lines? And then a user might want to comment on the multi-line variation in its entirety? If so, this case doesn't appear to be handled right now.


              • It's surprising that there aren't any columns other than EntityType that are common across all entities. Perhaps there should be an EntityName column rather than separate columns ContractName, ContractLineName, and VariationName? This would make the example query you describe in your question far simpler.


              • The data model does not strictly enforce that the Variation contains logically consistent data. It stores both a ContractId and a ContractLineId. The foreign keys for these columns enforce that each is valid independently, but allow for a ContractLineId to be inserted that comes from the incorrect ContractId (that is, different than the ContractId for that ContractLineId in the ContractLine table). One option to address this is to remove the ContractId column from the Variation table.







              share|improve this answer


























                0












                0








                0







                I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract.



                Since you asked for potential shortcomings or pitfalls, here are a few you might consider:




                • Without knowing much about your system, it seems strange that a variation must apply to only one contract line. Is there ever a case where a variation might apply to multiple lines? And then a user might want to comment on the multi-line variation in its entirety? If so, this case doesn't appear to be handled right now.


                • It's surprising that there aren't any columns other than EntityType that are common across all entities. Perhaps there should be an EntityName column rather than separate columns ContractName, ContractLineName, and VariationName? This would make the example query you describe in your question far simpler.


                • The data model does not strictly enforce that the Variation contains logically consistent data. It stores both a ContractId and a ContractLineId. The foreign keys for these columns enforce that each is valid independently, but allow for a ContractLineId to be inserted that comes from the incorrect ContractId (that is, different than the ContractId for that ContractLineId in the ContractLine table). One option to address this is to remove the ContractId column from the Variation table.







                share|improve this answer













                I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract.



                Since you asked for potential shortcomings or pitfalls, here are a few you might consider:




                • Without knowing much about your system, it seems strange that a variation must apply to only one contract line. Is there ever a case where a variation might apply to multiple lines? And then a user might want to comment on the multi-line variation in its entirety? If so, this case doesn't appear to be handled right now.


                • It's surprising that there aren't any columns other than EntityType that are common across all entities. Perhaps there should be an EntityName column rather than separate columns ContractName, ContractLineName, and VariationName? This would make the example query you describe in your question far simpler.


                • The data model does not strictly enforce that the Variation contains logically consistent data. It stores both a ContractId and a ContractLineId. The foreign keys for these columns enforce that each is valid independently, but allow for a ContractLineId to be inserted that comes from the incorrect ContractId (that is, different than the ContractId for that ContractLineId in the ContractLine table). One option to address this is to remove the ContractId column from the Variation table.








                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jul 23 '15 at 21:39









                Geoff PattersonGeoff Patterson

                6,43711948




                6,43711948






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • 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%2fdba.stackexchange.com%2fquestions%2f107890%2fcategory-entity-relationship-subtype-relationships-design%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







                    Popular posts from this blog

                    Liste der Baudenkmale in Friedland (Mecklenburg)

                    Single-Malt-Whisky

                    Czorneboh