Structure advice

Databases
1

Hi all.

I recently started a webshop for a vintage store. It has been some time since I last had to do a webshop and I can’t figure out what would be the best approach. My first thought was to use 3 tables:

products, categories and product_categories

I think the first two tables speak for themselves.

Products:

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `price` decimal(10,2) NOT NULL,
  `in_stock` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and categories:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I wanted to use the third table, product_categories, as a link table between products and categories. This would allow a product to be linked to multiple categories

CREATE TABLE IF NOT EXISTS `product_categories` (
  `category_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  PRIMARY KEY (`category_id`,`product_id`),
  KEY `IDX_D2B6356B88C5F785` (`category_id`),
  KEY `IDX_D2B6356B7294869C` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Only with this setup I ran a little too fast. The owner told me that she didn’t consider it necessary to place a product under multiple categories. However, she would like to see a sub category added to the structure as well. This second wish wasn’t difficult to solve by simply adding an extra field, parent_id to the table categories. Only I can’t figure out how I link the various products to the table categories now that there is also a sub category in the game.

I would really appreciate it if someone could nudge me in the right direction or tell me that it might be better to use a completely different structure to solve this.

Thank you in advance

2

everything looks fine

with parent_id in categories nothing has really changed in how you would relate products to categories, just do it the way you would normally do it, without regard to whether the category is a subcategory or not

the bigger challenge is how to show the category/subcategory-to-product structure wherever is this visible, either on category pages or dropdown menus

3

Hi @Rudi. Thank you for the fast reply.

Indeed I use the parent_id for the dropdown menus. But do you mean now that I can actually keep using the table product_categories, which I mentioned earlier? Maybe I just think too difficult. Let me give an example:

There is a category of bags. Under this category there are 3 sub categories (Flower bags, Fifties illustrated, Miscellaneous).

INSERT INTO `categories` (`id`, `parent_id`, `name`) VALUES
(1, NULL, 'Tassen'),
(2, 1, 'Flowerbags'),
(3, 1, 'Fifties illustrated'),
(4, 1, 'Miscellaneous'),

Now if I add one or more Flower Bags to the product table how do I link it to id 2 of the category table without an index in the product table.

Edit: Maybe I’m just asking the wrong question! Let me put it differently. Would you just continue to use that product_categories table to link a product to a category or would you add an extra field (category_id) to the product table and then link a product to a category with that index?

4

It depends :wink:

If it should be possible to have product in multiple categories (i.e. a bag that is both a Flowerbag as well as Fifities illustrated) then go with the product_categories. If not (and make sure to really hammer this point down with the client that they’re a 100% sure and change of mind will cost them $$$ for rework) then just adding category_id to the products table is enough.

5

Hi @rpkamp, thx for the reply

You are absolutely right. I have gone through it with the customer and she absolutely doesn’t sees the need for a product to be placed under multiple categories. So it becomes an extra (index) field (category_id) in the product table. Thanks a lot for the input!

6

You’re welcome. Don’t forget to make it a foreign key :slight_smile:

1 Like
7

I think you need to explain that better. You show what you had before the new requirement but I am not sure what you have already tried for the new requirement.

An important question is if the sub-category is a requirement. Can an item have only a category?

Also ensure that the concept of features are understood. Something like colors are features, not sub-categories. Features are common to multiple categories, maybe all categories.

8

If I understand what you are saying then that is likely to cause other problems.

Perhaps it would work for each product to have both a category and a sub-category. If the sub-category is not relevant then it can simply be null.

9

i think i disagree – i mean, if a product fits into a subcategory of a category, would it not by extension be in that category?

perhaps you could mock up some sample categories and subcategories and show how few products would fit into your structure

10

I think you are implying that I was describing situations where there is a sub-category but no category. I was not suggesting that. I was suggesting that there be a category but the sub-category would be optional (not relevant).

As for sub-categories having one and only category, theoretically that might not always be true. Hopefully this is not relevant here but it could be. I cannot think of an example relevant to a vintage store but for bookmarks we might have the following sub-category names:

  • stores
  • reference material

Those sub-categories names could exist for many categories. Certainly each of those sub-categories are dependent on the category they are in but we must be careful about what we are saying.

11

If that is suggesting that category_id be used for both category (when there is no sub-category) or sub-category then it becomes complicated to determine what category a product is in (such as for a menu or drop-down of categories). Or the categories and sub-categories could be mixed and I am sure not impressed when websites do things like that.

12

@SamuelCalifornia. It is working great with the parent_id in the category table, both for the sub items as well as for linking a product to a (sub) category.

@r937 and @rpkamp Thank you both for the usefull input

13

Well, they could be mixed in the database while the UI enforces products to only be placed in sub-categories. Best of both worlds :slight_smile:

1 Like
14

nope, i wasn’t

if a subcategory has no parent category, it’s not a subcategory, it’s a category :smiley:

3 Likes
15

I completely aggree @rpkamp and @r937 :grin:

@ SamuelCalifornia

I have been thinking a while about what you ment with the above statement, but I couldn’t come up with a possible explanation. Am I missing something?

16

Yes, you are missing the first part of the sentence. Read the entire sentence.

17

@SamuelCalifornia. I just did, but I think I must disaggree. A sub category is part of a certain category. Can you give an example of why I should determine what category a product is in? You mention menu and/or drop-downs but I don’t experience any problems using the parent_id in the category table.

18

I get the impression you are making more effort to be critical than understand what I am saying.

19

Sorry to give that impression because that is absolutely not the intention but besides of that I don’t run into any of the things you describe. I got a working structure, which is important to me.

21

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.