eCommerce database design (multilevel categories)

Question

I would like to build a small eCommerce site, nothing special, no add to cart, ratings and stuff like that but i do have one dilemma. I need to have multiple levels of categories.

So for examples if i would maybe have something like this:

Samsung
  - Tv
    - Smart Tv
      - Samsuns Smart Tv s45
      - Samsung Smart Tv k7x
    - 3D Tv
      - some product
  - Laptops
    - Intel Core 7
       - Some product
    - Inter Core 5
       - Some product

LG
  - same stuff

The only problem is that the levels of categories are not predefined so i don't know how mane levels there will be.

Could someone please show me some examples how would a mysql database look for this kind of structure?

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ — , Jun 08 '14 at 20:01
Similar problem solved [here](http://stackoverflow.com/questions/21010251/inventory-management-with-stock-options/21113170#21113170). may come in handy. — Mohsen Heydari, Jun 09 '14 at 09:59

score 1 · Answer 1 · answered Nov 22 '16 at 05:39

1

For the multiple level of categories

id | category_name | slug | parent_id

consider this design

answered Nov 22 '16 at 05:39

ujwal dhakal

2,289
2
30
50

score 0 · Answer 2 · answered Oct 03 '18 at 12:38

I have written this code in coldfusion you can modify this as per your requirement...

<cfquery name="Stuff" datasource="xyz">
    SELECT CategoryId, CategoryName, ParentId
    FROM Category
    ORDER BY CategoryName
</cfquery>

<cfset RootItems=ArrayNew(1)>
<cfset Depth=ArrayNew(1)>
<cfset RowFromID=StructNew()>
<cfset ChildrenFromID=StructNew()>

<cfloop query="Stuff">
    <cfset RowFromID[CategoryId]=CurrentRow>
    <cfif NOT StructKeyExists(RowFromID, ParentId)>
        <cfset ArrayAppend(RootItems, CategoryId)>
        <cfset ArrayAppend(Depth, 0)>
    </cfif>
    <cfif NOT StructKeyExists(ChildrenFromID, ParentId)>
        <cfset ChildrenFromID[ParentId]=ArrayNew(1)>
    </cfif>
    <cfset ArrayAppend(ChildrenFromID[ParentId], CategoryId)>
</cfloop>

<cfloop condition="ArrayLen(RootItems) GT 0">   
    <cfset ThisID=RootItems[1]>
    <cfset ArrayDeleteAt(RootItems, 1)>
    <cfset ThisDepth=Depth[1]>
    <cfset ArrayDeleteAt(Depth, 1)>

    <cfif StructKeyExists(RowFromID, ThisID)>
        <cfset RowID=RowFromID[ThisID]>
        <cfoutput>#RepeatString("--",ThisDepth)# #Stuff.CategoryName[RowID]#</cfoutput>
    </cfif>
    <cfif StructKeyExists(ChildrenFromID, ThisID)>
        <cfset ChildrenIDs=ChildrenFromID[ThisID]>
        <cfloop from="#ArrayLen(ChildrenIDs)#" to="1" step="-1" index="i">
            <cfset ArrayPrepend(RootItems, ChildrenIDs[i])>
            <cfset ArrayPrepend(Depth, ThisDepth + 1)>
        </cfloop>
    </cfif>
</cfloop>

eCommerce database design (multilevel categories)

2 Answers2