[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

web app

Posted on 2008-06-11
17
Medium Priority
?
173 Views
Last Modified: 2010-03-19
to make a long story short
i have been asked if i can build a shopping cart where the products have a hierarchy that is virtually unlimited.
to explain a bit
normally I'd do Categories table > SubCategories table > Items Table
even if I used 1 table this is still the concept I'd use
What they want is to be able to have some items have cats > subcats > items
others might be cats > subcats > subsubcats > items
other items might be cats > subcats > subsubcats > subsubsubcats > items
for example

I'd like some suggestions on the concept to use for this
0
Comment
Question by:dgrafx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21759193
I would imagine that you'd have some sort of parent/child relationship in the table....

CategoryID
ParentCategoryID

It would be a self referencing table
0
 
LVL 25

Author Comment

by:dgrafx
ID: 21759450
can you expand a bit on what a table might look like that has the ability to have an "unlimited" hierarchy?
and what would some basic sql look like that asks what subcat or subsubcat or whatever on a product page?

thanks ...
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21759485
Sure...so, every category (record) in the table either belongs to the base category (parent), or is related to the parent...for example:

create table categories
(
categoryid int not null primary key,
parentcategoryid int null,
categoryname varchar(100)
)
go

insert into categories values (1, null, 'parent')
insert into categories values (2, 1, 'child1')
insert into categories values (3, 2, 'grandchild1')
insert into categories values (4, 1, 'child2')

in this case, the first category is the parent, and the others are related to the parent either by being a child or a grandchild, etc.  Does that make sense?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 25

Author Comment

by:dgrafx
ID: 21759696
so in querying
if parentcategoryid is null then this is a top level category
if parentcategoryid is 1 then it is next level down (from null) subcat
if parentcategoryid is 2 then it is next level down (from 1) subcat

question
what would the table look like if
you have
categoryname of "tester" with catid of 1
categoryname of "testing" with catid of 2
these are both top level categories
tester has a "subcat" named "blue" and a subsubcat named "green"
testing has a subcat named "red"

just to help me get the concept

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21759770
you want to see the data, right?

insert into categories values (1, null, 'tester')
insert into categories values (2, null, 'testing')
insert into categories values (3, 1, 'blue')
insert into categories values (4, 1, 'green')
insert into categories values (5, 2, 'red')
0
 
LVL 25

Author Comment

by:dgrafx
ID: 21760056
ok - great - so far so good
and to create a subsubcat under blue named yellow
insert into categories values (6, 3, 'yellow')

so then this particular hierarchy would be tester > blue > yellow
is that correct?

what sql would one use for a display page?
I'm guessing you have some slick method for saying is this a toplevel category or a child or a grandchild
so you know what type of "Stuff" to display
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21760079
You are, in fact, using 2005, right?
0
 
LVL 25

Author Comment

by:dgrafx
ID: 21760108
yes - but i believe the live app will run on 2000
if there is a difference - please post both as this is the first 2000 app i've done in a bit and may not end up doing another
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21760135
Well, it is a LOT easier in 2005 to write this as it supports recursive queries.  2000 really doesn't do a good job of it.
0
 
LVL 25

Author Comment

by:dgrafx
ID: 21760162
i imagine so
unfortunately the decision is out of my hands ...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21760185
OK.

So, what kind of SQL results are you looking to see?
0
 
LVL 25

Author Comment

by:dgrafx
ID: 21760286
i guess in general something like
now on a page where one is expecting to see categories
i query for categories and display catname - description (if any) - image (if any) etc
then when that is clicked I go to another page that queries for subcats where the same type of thing is done and when clicked I go to an items page
so I use 3 different display pages
but it occurs to me that with this new method one may still have an items page that has the typical "add to cart" functionality but the cats & subcats & subsubcats etc would probably be on the same page so am just looking for some pointers as to how do i tell at what level is the data at that I'm currently looking at?

thanks ...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21760386
OK...this will get you the first level (parent) and any direct child levels...


create table categories
(
categoryid int not null primary key,
parentcategoryid int null,
categoryname varchar(100)
)
go
 
insert into categories values (1, null, 'tester')
insert into categories values (2, null, 'testing')
insert into categories values (3, 1, 'blue')
insert into categories values (4, 1, 'green')
insert into categories values (5, 2, 'red')
 
create function udf_recurse
(
@catid int, 
@parentid int = null
)
returns @test table(
categoryid int ,
parentcategoryid int ,
categoryname varchar(100))
as
begin
insert into @test
select * from categories where categoryid = @catid
union
select * from categories where parentcategoryid = @catid
 
return
end
 
 
select * from udf_recurse(1,1)

Open in new window

0
 
LVL 25

Author Comment

by:dgrafx
ID: 21760486
ok
is this the 2000 method?

do you have a 2005 method for my own future use?

thanks ...
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21760575
sure...one second.  You're going to have the tweak the 2000 version a bit for your own purposes....
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21760626
with catrecurse(catid, parentcatid, catname, rowno, levelrow)
as
(
select categoryid, parentcategoryid, categoryname,        ROW_NUMBER() OVER(ORDER BY categoryid),
       0 AS LevelRow
from categories
where parentcategoryid is null
union all
select c.categoryid, c.parentcategoryid, c.categoryname, r.rowno, r.levelrow + 1
from categories c
join catrecurse r on c.parentcategoryid = r.catid
where parentcategoryid is not null
)

select * from catrecurse
0
 
LVL 25

Author Comment

by:dgrafx
ID: 21760802
pretty cool ...
thank you very much
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question