newbie schema design question

[sklett]

* i posted this in the SQLserver group, but it has some ado.net issues, so
maybe here is good too *

I have a schema in place and I'm starting to think that it must be a bad
design because I'm having the hardest time organized my results so that I
can present them the way I want. Here is the situation.

Our website has a support section that is organized by product, so each
product has support data(office documents, faqs, etc) that correlates to a
product. I want to present the data hierarchically like this:

PRODUCT
SUPPORT CATEGORY(s) (IE: faq category "usage")
SUPPORT ITEM(s) (IE: faq)



My DB schema looks like this:

[ Products_Main ]
ProdId
ProdTitle
Etc..

[ Prod_Faqs ] (many to many)
ProdId
FaqId

[ Faq_Main ]
FaqId
CategoryId
Question
Etc...

[ Faq_Categories ]
CatId
Title


So let's say the user wants to see the FAQs for product "A". I need to get
all the records from [ Prod_Faqs ], then JOIN [ Faq_Main ], [
Products_Main ], and finally [ Faq_Categories ]
This already presents a problem because I want to group the FAQs by
category. It feels like I should instead link products to FAQ categories,
then get the FAQ items from the category.


one more thing, the reason this is a problem is that I have no relationship
between a PRODUCT and a FAQ_CATEGORY.
If I did, then I could easily join everything in a way that was logical, but
it seems backwards and incorrect to have tie a category to a product when
the FAQ itself is what has a logical relationship to the product.

I may be shooting myself in the foot by "over abstracting". Does it seem
like good design to have a many-to-many table linking FAQ_CATEGORIES to
PRODUCTS?
I see the category as an orginizational object, not a data object, that is
why I am reluctant to integrate it too heavily into the schema.



As it is right now, I am getting 1 big table back, then looping through the
results and populating a series of arrays of classes to gather everything
the way that I want so that I can populate nested web controls.
IE:

class CSupportCategory
{
ArrayList mFaqList; // array of CSupportFaq
}

class CSupportFaq
{
string Title;
// etc...
}


A bit lost. I just want to stop question my DB design and get on with
things, but I fear it's a bad design and I don't want to work off a bad
design.
Any help or guidance, suggestions would be so greatly appreciated.

Thanks for reading,
Steve

 

[sklett]

I just realized that even if I DID use the categories as a way to retreive
the data, I would have faq items that don't belong with the correct
products.
This is kicking my butt, please... someone must have some ideas??

 

[Raymond Lewallen]

Steve,

Does your query look something like this?

select * from Products_Main pm
inner join Prod_Faqs pf on pm.prodid = pf.prodid
inner join Faq_Main fm on pf.FaqId = fm.FaqID
inner join Faq_Categories fc on fm.CategoryID = fc.CategoryID
wher pm.prodid = @intProdID
order by fm.CatID

Raymond Lewallen

 

[sklett]

Hi Raymond,

No, doesn't look like that. That would result in a single table with all of
the categories and faq items together, right?

If so, the problem with that would be that I need to be able to bind the
categories for a given product to one DataList, then bind the faq items for
that category to a nested datalist.
furthermore, there are times when I will want to show multiple products.

Ideally, I would be able to use DataRelations and each time the Products
DataList fires OnItemDataBound, I could get the child categories for that
product, bind them to a second, nested DataList, then when that second
DataList's OnItemDataBound event fires, I could get the child rows(faq
items) and bind them to a third nested DataList.

I just can't see a way to establish the relationships between all the
players.

I tried adding a [ Prod_FaqCategories ] many-to-may table and using it to
get the categories for each product, but them when it came time to get teh
categories faq items, I was getting duplicates and items that didn't belong
to the current product (faqs can be used for multiple products)


I can't wait to see the solution to this, I have been trying for 3 days
now.....