Database Design

[Mailmanny]

I have a small furniture company. we manufacture about 500 different stock
peices of furniture. I am building an ASP WEB site and need to design abd
build a database to store product information.
Each product will need - Part Number, Title, Description, Price, Category
and and unknown number of sub-categories, group or room (office, living,
bedroom etc.), sytle(contemporary, mission, traditional) three size colums
for H x W x D, i am wondering how to layout my table or tables and
relationships. so i have them normalized and it is easily searchable with
search words and some type of drill down filter. something like they use on
e-comerce sites like circuit city or guitarcenter. can anyone help me with
this or do you need more information.

 

[Mailmanny]

I ahve built a soluiton which i think will store the data the way i want and
be searchable and filterable the wat i want but I guess my biggest problem is
the data input interface.

my current soultion is:

tblProducts
PK - ProductID
Title
Description
FK - StyleID
FK - GroupID
FK - CategoryID
SizeW
SizeH
SizeD
Price
ImageURL
AltImageText

tblCategory
PK - CategoryID
CAtegoryName
FK - ParentCategoryID - Recursive key to CategoryID

tblSytle
PK - StyleID
StyleName

tblGroup
PK - GroupID
GroupName

The difficult part is entering a new product, how can i select a category if
it is a child or sleect a top level category and then have it give me the
choices of that categories children to select from and then do i need to
store the child and parent or just the child. any thoughts?

 

[Fred]

I don't think hat "more info" is the question. But I think that we need to
separate your post in order to deal with it.

While your post is mainly about data, structure etc., one sentence implies
that any answer must apparently imply/deal with using Access as a back end
for a web site. This is a whole other ball game; let's set that aside for a
moment.

I think that your structure as shown looks good for what you are trying to
do.

As a side note, your recursive structure for your categories is fine for
defining an upside down "tree", I.E. multiple levels of one-to-many
relationships. It does place a constraint that each lower level category can
only be under one higher level category. Depending on your situation, a
small sacrifice woth making to avoid a big leap in complexity.

Regarding "what to store" (regarding categories) that's a question of what
you want/need to store, but the likely answer is just the lowest level
category; all higher level categories can be determined from that.

I'm not a good enough of an Access developer to pick and describe the best
way to show sub-categories when you pick a category when entering a record.


Sincerely,

Fred

 

[Barry A&P]

Mailmany
I am deffinately a access beginner but i have done two of the things you are
talking about
i have a table
T_Categories
CategoryID
Categoryname

and a table
T_Subcategory
SubCatID
CategoryID
SubCategoryName
every sub category is linked to a main category by categoryID
on my form i have a lookup combo box for category and a lookup combo box for
sub category and in my record source for the sub category combo i have the
following criteria under CategoryID [Forms]![F_Main]![category_Combo] so that
once a main category is selected the sub category combo only shows related
sub categories.. also click microsoft access help on accesses toolbar and
search "Issues database Template" and download the sample. it has a a drill
down search form that works really slick to filter results on the form i had
a little trouble with all the "'s and ''s in the code but i got it with a
little forum help.. there is also a "inventory Management Template" that has
good stuff..

Good luck
Merry Christmas
Barry

 

[Mailmanny]

I understand the constraint of the recursive structure and the web site side
is a peice of cake. I can retrive ant use the info for my web site fine. my
weakness is the database side.

if this design will work like i think it will my only remaining question is
how to use access or c# or any language to drill down through the recursive
table to a lowest level category when i am having a user enter new items.
amybee a sample querry would help or a sample code. i have done it with
cascading combos but that is only with seperate category., subcat, subcat2
tables and a known number of levels. is it different for a recursive list.
I'd rather pick an item from the highest level then have the results filtered
to the next, and the next and so on.

Any Ideas?

 

[Fred]

I mentioned that I don't know the good way to do that.

You might want to repost that specific question.

 

[Barry A&P]

here is some stuff allen browne helped me with

You can create a
monster query that does this to about 4 generations, e.g.:
http://allenbrowne.com/ser-06.html
But the problem of infinite recursion arises, e.g. where are part is wrongly
entered as its own grandparent.

Here's a sample database that illustrates the technique:
http://www.mvps.org/access/modules/mdl0027.htm

Here's some more involved SQL approaches from Joe Celko:
http://www.intelligententerprise.com/001020/celko.shtml
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
http://www.dbmsmag.com/9606d06.html

Mailmany
I am deffinately a access beginner but i have done two of the things you are
talking about
i have a table
T_Categories
CategoryID
Categoryname

and a table
T_Subcategory
SubCatID
CategoryID
SubCategoryName
every sub category is linked to a main category by categoryID
on my form i have a lookup combo box for category and a lookup combo box for
sub category and in my record source for the sub category combo i have the
following criteria under CategoryID [Forms]![F_Main]![category_Combo] so that
once a main category is selected the sub category combo only shows related
sub categories.. also click microsoft access help on accesses toolbar and
search "Issues database Template" and download the sample. it has a a drill
down search form that works really slick to filter results on the form i had
a little trouble with all the "'s and ''s in the code but i got it with a
little forum help.. there is also a "inventory Management Template" that has
good stuff..

Good luck
Merry Christmas
Barry

I don't think hat "more info" is the question. But I think that we need to
separate your post in order to deal with it.

While your post is mainly about data, structure etc., one sentence implies
that any answer must apparently imply/deal with using Access as a back end
for a web site. This is a whole other ball game; let's set that aside for a
moment.

I think that your structure as shown looks good for what you are trying to
do.

As a side note, your recursive structure for your categories is fine for
defining an upside down "tree", I.E. multiple levels of one-to-many
relationships. It does place a constraint that each lower level category can
only be under one higher level category. Depending on your situation, a
small sacrifice woth making to avoid a big leap in complexity.

Regarding "what to store" (regarding categories) that's a question of what
you want/need to store, but the likely answer is just the lowest level
category; all higher level categories can be determined from that.

I'm not a good enough of an Access developer to pick and describe the best
way to show sub-categories when you pick a category when entering a record.


Sincerely,

Fred