Create products with color variants












0















I'm working on an e-commerce project to sell clothes, to store these products I'm using a table tb_product. These clothes will have the attributes color and size, where I also need to keep track of the stock for each produtct, size and color.



If I use a second table to control the attributes, as I already asked here, I think it's going to add unecessary complexity, because I need to keep track of the stock for the each shirt, each size and each color, all related.



One solution I've seen many stores doing is to add each color as a single product and only control the size attribute. The ony doubt I have is how to link all colors of the some product to show under an option "More colors", like this image example. If I click on any of those options on the tab "Mais cores" it will open a new product page.



This is where my doubt begins, because every other aspect of the item, such as name, price, description, etc.. Is all the same, only the color is changing.



How can I create a link between these products to show them as related colors? The simple solution I could think of is to add a new column to the table, called, for example, group, and all the products would contain within this group. For example:



id | active | group |      name     | description | price | ...
1 | 1 | A | T-Shirt white | This is a.. | 9.90 | ...
2 | 1 | A | T-Shirt Black | This is a.. | 9.90 | ...
3 | 1 | B | Hat yellow | This is a.. | 9.90 | ...
4 | 1 | B | Hat red | This is a.. | 9.90 | ...
5 | 1 | null | Custom dress | This is a.. | 9.90 | ...
6 | 1 | null | Belt | This is a.. | 9.90 | ...

CREATE TABLE IF NOT EXISTS `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`active` tinyint(1) NOT NULL DEFAULT '0',
`group` varchar(10) DEFAULT NULL,
`name` varchar(250) NOT NULL,
`description` longtext NOT NULL,
`price` decimal(10,2) NOT NULL,
PRIMARY KEY (`id`),
);


Is there a better, or any other, approach to this solution?










share|improve this question














bumped to the homepage by Community 19 mins ago


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




















    0















    I'm working on an e-commerce project to sell clothes, to store these products I'm using a table tb_product. These clothes will have the attributes color and size, where I also need to keep track of the stock for each produtct, size and color.



    If I use a second table to control the attributes, as I already asked here, I think it's going to add unecessary complexity, because I need to keep track of the stock for the each shirt, each size and each color, all related.



    One solution I've seen many stores doing is to add each color as a single product and only control the size attribute. The ony doubt I have is how to link all colors of the some product to show under an option "More colors", like this image example. If I click on any of those options on the tab "Mais cores" it will open a new product page.



    This is where my doubt begins, because every other aspect of the item, such as name, price, description, etc.. Is all the same, only the color is changing.



    How can I create a link between these products to show them as related colors? The simple solution I could think of is to add a new column to the table, called, for example, group, and all the products would contain within this group. For example:



    id | active | group |      name     | description | price | ...
    1 | 1 | A | T-Shirt white | This is a.. | 9.90 | ...
    2 | 1 | A | T-Shirt Black | This is a.. | 9.90 | ...
    3 | 1 | B | Hat yellow | This is a.. | 9.90 | ...
    4 | 1 | B | Hat red | This is a.. | 9.90 | ...
    5 | 1 | null | Custom dress | This is a.. | 9.90 | ...
    6 | 1 | null | Belt | This is a.. | 9.90 | ...

    CREATE TABLE IF NOT EXISTS `product` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `active` tinyint(1) NOT NULL DEFAULT '0',
    `group` varchar(10) DEFAULT NULL,
    `name` varchar(250) NOT NULL,
    `description` longtext NOT NULL,
    `price` decimal(10,2) NOT NULL,
    PRIMARY KEY (`id`),
    );


    Is there a better, or any other, approach to this solution?










    share|improve this question














    bumped to the homepage by Community 19 mins ago


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


















      0












      0








      0








      I'm working on an e-commerce project to sell clothes, to store these products I'm using a table tb_product. These clothes will have the attributes color and size, where I also need to keep track of the stock for each produtct, size and color.



      If I use a second table to control the attributes, as I already asked here, I think it's going to add unecessary complexity, because I need to keep track of the stock for the each shirt, each size and each color, all related.



      One solution I've seen many stores doing is to add each color as a single product and only control the size attribute. The ony doubt I have is how to link all colors of the some product to show under an option "More colors", like this image example. If I click on any of those options on the tab "Mais cores" it will open a new product page.



      This is where my doubt begins, because every other aspect of the item, such as name, price, description, etc.. Is all the same, only the color is changing.



      How can I create a link between these products to show them as related colors? The simple solution I could think of is to add a new column to the table, called, for example, group, and all the products would contain within this group. For example:



      id | active | group |      name     | description | price | ...
      1 | 1 | A | T-Shirt white | This is a.. | 9.90 | ...
      2 | 1 | A | T-Shirt Black | This is a.. | 9.90 | ...
      3 | 1 | B | Hat yellow | This is a.. | 9.90 | ...
      4 | 1 | B | Hat red | This is a.. | 9.90 | ...
      5 | 1 | null | Custom dress | This is a.. | 9.90 | ...
      6 | 1 | null | Belt | This is a.. | 9.90 | ...

      CREATE TABLE IF NOT EXISTS `product` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `active` tinyint(1) NOT NULL DEFAULT '0',
      `group` varchar(10) DEFAULT NULL,
      `name` varchar(250) NOT NULL,
      `description` longtext NOT NULL,
      `price` decimal(10,2) NOT NULL,
      PRIMARY KEY (`id`),
      );


      Is there a better, or any other, approach to this solution?










      share|improve this question














      I'm working on an e-commerce project to sell clothes, to store these products I'm using a table tb_product. These clothes will have the attributes color and size, where I also need to keep track of the stock for each produtct, size and color.



      If I use a second table to control the attributes, as I already asked here, I think it's going to add unecessary complexity, because I need to keep track of the stock for the each shirt, each size and each color, all related.



      One solution I've seen many stores doing is to add each color as a single product and only control the size attribute. The ony doubt I have is how to link all colors of the some product to show under an option "More colors", like this image example. If I click on any of those options on the tab "Mais cores" it will open a new product page.



      This is where my doubt begins, because every other aspect of the item, such as name, price, description, etc.. Is all the same, only the color is changing.



      How can I create a link between these products to show them as related colors? The simple solution I could think of is to add a new column to the table, called, for example, group, and all the products would contain within this group. For example:



      id | active | group |      name     | description | price | ...
      1 | 1 | A | T-Shirt white | This is a.. | 9.90 | ...
      2 | 1 | A | T-Shirt Black | This is a.. | 9.90 | ...
      3 | 1 | B | Hat yellow | This is a.. | 9.90 | ...
      4 | 1 | B | Hat red | This is a.. | 9.90 | ...
      5 | 1 | null | Custom dress | This is a.. | 9.90 | ...
      6 | 1 | null | Belt | This is a.. | 9.90 | ...

      CREATE TABLE IF NOT EXISTS `product` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `active` tinyint(1) NOT NULL DEFAULT '0',
      `group` varchar(10) DEFAULT NULL,
      `name` varchar(250) NOT NULL,
      `description` longtext NOT NULL,
      `price` decimal(10,2) NOT NULL,
      PRIMARY KEY (`id`),
      );


      Is there a better, or any other, approach to this solution?







      mysql database-design






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Apr 7 '18 at 11:35









      CelsomTrindadeCelsomTrindade

      1508




      1508





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














          You have Table called 'product'
          To find out the unique product among the one product, u need to maintain these structure.
          1.Product table
          2.Characteristics table. EX(size, color etc)
          3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose)
          4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.



          Then you can get one valuable structure for eCommerce products.
          For stock you have to maintain from product price 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%2f203296%2fcreate-products-with-color-variants%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














            You have Table called 'product'
            To find out the unique product among the one product, u need to maintain these structure.
            1.Product table
            2.Characteristics table. EX(size, color etc)
            3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose)
            4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.



            Then you can get one valuable structure for eCommerce products.
            For stock you have to maintain from product price table.






            share|improve this answer




























              0














              You have Table called 'product'
              To find out the unique product among the one product, u need to maintain these structure.
              1.Product table
              2.Characteristics table. EX(size, color etc)
              3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose)
              4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.



              Then you can get one valuable structure for eCommerce products.
              For stock you have to maintain from product price table.






              share|improve this answer


























                0












                0








                0







                You have Table called 'product'
                To find out the unique product among the one product, u need to maintain these structure.
                1.Product table
                2.Characteristics table. EX(size, color etc)
                3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose)
                4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.



                Then you can get one valuable structure for eCommerce products.
                For stock you have to maintain from product price table.






                share|improve this answer













                You have Table called 'product'
                To find out the unique product among the one product, u need to maintain these structure.
                1.Product table
                2.Characteristics table. EX(size, color etc)
                3.Product_Price table (A product price may vary based on characteristics. EX: Pendrive is a product. But their characteristics may change the price. 2GB pendrive,, 4GB Pendrive, 8GB pendrive. So, Based on characteristics the price also varying. This table for that purpose)
                4. Product_Characteristics table. This is need to map the product and characteristics. Primary key from product table and primary key from characteristics table.



                Then you can get one valuable structure for eCommerce products.
                For stock you have to maintain from product price table.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Apr 7 '18 at 12:56









                ANDO LAWRENCEANDO LAWRENCE

                1




                1






























                    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%2f203296%2fcreate-products-with-color-variants%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