What is the best design for dynamic assignment of locations for variable sized grid boxes to occupy?












2















I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays.



Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I want to return one location for me to put boxes down.



My approach is to have every slot named and stored in database. And have a flag "occupied" and name for the box to keep track of the box location. The tricky part is there is different sizes of boxes. Some will occupied 3 slots, some will occupy 2 slots and some will occupy 1 slot.



How can I design the database structure so that it's easier for me to query the next available slot based on the size?





Responses to comments




Is this some resource scheduling application?




It's more like a dynamic space allocation application.




Are the boxes to be allocated are mono- or bi-dimensional? In other words, do you allocate a space of 2x2 boxes or 4 boxes? In the second case, can you allocate both "horizontally" and "vertically"?




You can only go one way, neither, entire locations is either they go all horizontally or all vertically, they can never be both. Just the size is different, it can occupy 1 slot, 2 slots or 3 slots, but once it is occupied, it can never be used for another box, unless the previous box removed.



Boxes can be placed in the grid horizontally for an entire grid, you can have another grid that can put boxes down vertically. Once a grid is set for this property, it will stay like that.




Do you have some kind of classification regarding the boxes sizes? Do you have, as stated in comments, only three different sizes of boxes? In case you do not have it, I think that such classification is necessary in order to ensure compatibility between the boxes and the grids (via the “slots”), and it is also necessary to take into account the common dimensions: width, height and depth.




The boxes can allocate 1 slot, or 2 slots or 3 slots, it depends on the size.




Are the grids fixed to 4x4 exclusively?




Actually 4X4 is a simplified example I gave. The actual size could be 31X8, imagine OOOOOOOO X31 and 8 lanes of those.




Are you planning to include different types of grids (for instance, 5x3, 10x10, 6x4, etc.) in the future?




The size won't be changed very often. It's pretty set, if there is space increase, I can always add them.




What is the meaning of the terms bays and lanes in your business context?




Bays and lanes are just terms to distinguish the X, and Y. On a 31 slots per lane, and 8 lanes in each grid. OOOOOOOO X 31 and 8 lanes of these next to each other vertically, bay means each of the "O", and lane is 8 lanes for each row of 31 "O".










share|improve this question
















bumped to the homepage by Community 5 mins ago


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




















    2















    I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays.



    Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I want to return one location for me to put boxes down.



    My approach is to have every slot named and stored in database. And have a flag "occupied" and name for the box to keep track of the box location. The tricky part is there is different sizes of boxes. Some will occupied 3 slots, some will occupy 2 slots and some will occupy 1 slot.



    How can I design the database structure so that it's easier for me to query the next available slot based on the size?





    Responses to comments




    Is this some resource scheduling application?




    It's more like a dynamic space allocation application.




    Are the boxes to be allocated are mono- or bi-dimensional? In other words, do you allocate a space of 2x2 boxes or 4 boxes? In the second case, can you allocate both "horizontally" and "vertically"?




    You can only go one way, neither, entire locations is either they go all horizontally or all vertically, they can never be both. Just the size is different, it can occupy 1 slot, 2 slots or 3 slots, but once it is occupied, it can never be used for another box, unless the previous box removed.



    Boxes can be placed in the grid horizontally for an entire grid, you can have another grid that can put boxes down vertically. Once a grid is set for this property, it will stay like that.




    Do you have some kind of classification regarding the boxes sizes? Do you have, as stated in comments, only three different sizes of boxes? In case you do not have it, I think that such classification is necessary in order to ensure compatibility between the boxes and the grids (via the “slots”), and it is also necessary to take into account the common dimensions: width, height and depth.




    The boxes can allocate 1 slot, or 2 slots or 3 slots, it depends on the size.




    Are the grids fixed to 4x4 exclusively?




    Actually 4X4 is a simplified example I gave. The actual size could be 31X8, imagine OOOOOOOO X31 and 8 lanes of those.




    Are you planning to include different types of grids (for instance, 5x3, 10x10, 6x4, etc.) in the future?




    The size won't be changed very often. It's pretty set, if there is space increase, I can always add them.




    What is the meaning of the terms bays and lanes in your business context?




    Bays and lanes are just terms to distinguish the X, and Y. On a 31 slots per lane, and 8 lanes in each grid. OOOOOOOO X 31 and 8 lanes of these next to each other vertically, bay means each of the "O", and lane is 8 lanes for each row of 31 "O".










    share|improve this question
















    bumped to the homepage by Community 5 mins ago


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


















      2












      2








      2








      I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays.



      Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I want to return one location for me to put boxes down.



      My approach is to have every slot named and stored in database. And have a flag "occupied" and name for the box to keep track of the box location. The tricky part is there is different sizes of boxes. Some will occupied 3 slots, some will occupy 2 slots and some will occupy 1 slot.



      How can I design the database structure so that it's easier for me to query the next available slot based on the size?





      Responses to comments




      Is this some resource scheduling application?




      It's more like a dynamic space allocation application.




      Are the boxes to be allocated are mono- or bi-dimensional? In other words, do you allocate a space of 2x2 boxes or 4 boxes? In the second case, can you allocate both "horizontally" and "vertically"?




      You can only go one way, neither, entire locations is either they go all horizontally or all vertically, they can never be both. Just the size is different, it can occupy 1 slot, 2 slots or 3 slots, but once it is occupied, it can never be used for another box, unless the previous box removed.



      Boxes can be placed in the grid horizontally for an entire grid, you can have another grid that can put boxes down vertically. Once a grid is set for this property, it will stay like that.




      Do you have some kind of classification regarding the boxes sizes? Do you have, as stated in comments, only three different sizes of boxes? In case you do not have it, I think that such classification is necessary in order to ensure compatibility between the boxes and the grids (via the “slots”), and it is also necessary to take into account the common dimensions: width, height and depth.




      The boxes can allocate 1 slot, or 2 slots or 3 slots, it depends on the size.




      Are the grids fixed to 4x4 exclusively?




      Actually 4X4 is a simplified example I gave. The actual size could be 31X8, imagine OOOOOOOO X31 and 8 lanes of those.




      Are you planning to include different types of grids (for instance, 5x3, 10x10, 6x4, etc.) in the future?




      The size won't be changed very often. It's pretty set, if there is space increase, I can always add them.




      What is the meaning of the terms bays and lanes in your business context?




      Bays and lanes are just terms to distinguish the X, and Y. On a 31 slots per lane, and 8 lanes in each grid. OOOOOOOO X 31 and 8 lanes of these next to each other vertically, bay means each of the "O", and lane is 8 lanes for each row of 31 "O".










      share|improve this question
















      I want to build a database that will have limited single locations to put boxes. It's divided by lanes and bays.



      Imagine a 4x4 grid, with a total of 16 slots, and every time I query the tables, I want to return one location for me to put boxes down.



      My approach is to have every slot named and stored in database. And have a flag "occupied" and name for the box to keep track of the box location. The tricky part is there is different sizes of boxes. Some will occupied 3 slots, some will occupy 2 slots and some will occupy 1 slot.



      How can I design the database structure so that it's easier for me to query the next available slot based on the size?





      Responses to comments




      Is this some resource scheduling application?




      It's more like a dynamic space allocation application.




      Are the boxes to be allocated are mono- or bi-dimensional? In other words, do you allocate a space of 2x2 boxes or 4 boxes? In the second case, can you allocate both "horizontally" and "vertically"?




      You can only go one way, neither, entire locations is either they go all horizontally or all vertically, they can never be both. Just the size is different, it can occupy 1 slot, 2 slots or 3 slots, but once it is occupied, it can never be used for another box, unless the previous box removed.



      Boxes can be placed in the grid horizontally for an entire grid, you can have another grid that can put boxes down vertically. Once a grid is set for this property, it will stay like that.




      Do you have some kind of classification regarding the boxes sizes? Do you have, as stated in comments, only three different sizes of boxes? In case you do not have it, I think that such classification is necessary in order to ensure compatibility between the boxes and the grids (via the “slots”), and it is also necessary to take into account the common dimensions: width, height and depth.




      The boxes can allocate 1 slot, or 2 slots or 3 slots, it depends on the size.




      Are the grids fixed to 4x4 exclusively?




      Actually 4X4 is a simplified example I gave. The actual size could be 31X8, imagine OOOOOOOO X31 and 8 lanes of those.




      Are you planning to include different types of grids (for instance, 5x3, 10x10, 6x4, etc.) in the future?




      The size won't be changed very often. It's pretty set, if there is space increase, I can always add them.




      What is the meaning of the terms bays and lanes in your business context?




      Bays and lanes are just terms to distinguish the X, and Y. On a 31 slots per lane, and 8 lanes in each grid. OOOOOOOO X 31 and 8 lanes of these next to each other vertically, bay means each of the "O", and lane is 8 lanes for each row of 31 "O".







      sql-server database-design sql-server-2008-r2






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Apr 12 '17 at 17:32









      MDCCL

      6,85331745




      6,85331745










      asked Jun 22 '15 at 9:17









      mslugxmslugx

      112




      112





      bumped to the homepage by Community 5 mins 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 5 mins 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














          This is actually an NP-complete problem



          You won't find an optimal way to pack your bins, but you can find a good way



          Here's Joe Celko's take on it:



          https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/






          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%2f104729%2fwhat-is-the-best-design-for-dynamic-assignment-of-locations-for-variable-sized-g%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














            This is actually an NP-complete problem



            You won't find an optimal way to pack your bins, but you can find a good way



            Here's Joe Celko's take on it:



            https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/






            share|improve this answer




























              0














              This is actually an NP-complete problem



              You won't find an optimal way to pack your bins, but you can find a good way



              Here's Joe Celko's take on it:



              https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/






              share|improve this answer


























                0












                0








                0







                This is actually an NP-complete problem



                You won't find an optimal way to pack your bins, but you can find a good way



                Here's Joe Celko's take on it:



                https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/






                share|improve this answer













                This is actually an NP-complete problem



                You won't find an optimal way to pack your bins, but you can find a good way



                Here's Joe Celko's take on it:



                https://www.simple-talk.com/sql/t-sql-programming/bin-packing-problems-the-sql/







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Apr 12 '17 at 22:25









                Neil McGuiganNeil McGuigan

                5,55432444




                5,55432444






























                    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%2f104729%2fwhat-is-the-best-design-for-dynamic-assignment-of-locations-for-variable-sized-g%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