CODEWITHSUNDEEP
phpmysqldatabase-design
Mr. Sam
Mr. Sam

Reputation: 790

eCommerce database design (multilevel categories)

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?

Upvotes: 0

Views: 2054

Answers (2)

Subhan Raza
Subhan Raza

Reputation: 67

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>

Category levels

Upvotes: 0

ujwal dhakal
ujwal dhakal

Reputation: 2459

For the multiple level of categories 

id | category_name | slug | parent_id

consider this design

Upvotes: 1

Related Questions