DB Design Theory...

Hey all, I got one for ya. Here's what I'm trying to do - I have a database of products.

Some of these products are parents with no children. Others are children with a parent that I can make up. Here's the kicker, these products do not have very similar characteristics. For instance, some products have a ton of specification information, while others are fairly simply described. And almost all my products share SOME criteria, while there are many special groups that don't share any.

Lets say here's my database:

Table - Products
Core Model Number | Product Type
Widget1 | Ball
Widget2 | Ball
Widget3 | Frisbee
Widget4 | Hat

So I also have a Specifications table:

Model Number | SpecType | SpecDetail
Widget1 | Diameter | 12"
Widget1 | Color | Blue
Widget1 | Texture | Glossy

Widget2 | Diameter | 14"
Widget2 | Color | Red
Widget2 | Texture | Rubber
Widget2 | Bounce Rating | Excellent

Widget3 | Diameter | 11"
Widget3 | Color | Orange
Widget3 | Material | Plastic
Widget3 | Flyer Rating | 8

Widget4 | Bill Color | Green
Widget4 | Hat Color | White
Widget4 | Material | Fabric

So now I've got this big group of data. I am trying to organize my data in such a way that I can, for instance, find all items in my database that have a diameter between 12 -> 14. By doing that search (SELECT DISTINCT ModelNo FROM SpecsTable WHERE SpecType = 'Diameter' AND ... [criteria] ...).

But that simply returns: Widget1, Widget2. And that's okay, but then in order to pull all the information about those items out, I have to query the DB again using (SELECT * FROM SpecsTable WHERE ModelNo ... [go through array of previous results]... ORDER BY ModelNo)... That seems not only very ugly, but I can also see a great deal of problems arising in large scale (ie this products db will most likely carry 10,000 - 20,000 products when completed). Any suggestions? Also, this system needs to be flexible to allow for FUTURE product additions with specifications & functionality that I don't even know of yet, so I need this to be extremely flexible (ie the purpose of what I've done for the 3 column product db).

Thanks gurus. I look forward to hearing your thoughts on the problem.

Sorry, but I'm not understanding what your question is???

Hmm, sorry, guess I wasn't specific. What I am trying to accomplish is to build a system that can deploy flexible attributes. I am currently in the process of redesigning our legacy which originally had 1 product type in mind, with certain criteria available at all times. What has happened is we're launching new products which have brand new criteria (ie the difference from balls -> frisbees -> hats). Totally different criteria.

Our current model would be:

Model No | Product Type | Color | Material | Texture | Diameter | Cost | Price
Widget1 | Ball | Blue | <NULL> | Glossy | 12" | $15 | $25
Widget2 | Ball | Red | <NULL> | Rubber | 14" | $20 | $28

But then I'd get a new characteristic, so I'd add a column on the end:
... | Bounce Rating |
W1...| <NULL> |
W2...| Excellent |

And then lets say a couple weeks later we start selling frisbees... So now I have to add another column:
... | Flyer Rating |
W1..| <NULL> |
W2..| <NULL> |
W3..| 8 |

And then a few weeks later we start selling hats. Those don't even fit in our scheme but we add on a couple more columns, like ...
... | Bill Color | Hat Color |

So now my products table has the columns:
-Model No
-Product Type
-Color
-Material
-Texture
-Diameter
-Cost
-Price
-Bounce Rating
-Flyer Rating
-Bill Color
-Hat Color

So my problem is that my products table continues to grow with waaaaay too many Null values all over the place from when we input a new column to accomodate soem new product. Fortunately our website could respond to this because we would simply change our search criteria by organizing everything by product type. But it fills up this db with useless garbage.

So I am trying to create a much simpler table that simply lists out all of my criteria in a list format. But what I want is to be able to search through my products for certain criteria; and then display all the information about the items that meet those criteria. I don't want it to simply give me a resulting set:

Widget1
Widget2

Every time I search for items that have diameter between x & y. I want it to return:
Widget1 ... spectype ... detail about that specification
Widget1 ... spectype ... etc...
Widget2 ... etc etc etc ...

That way I hit my db with 1 query and have the results that I want to use.

The alternative method that I know how to do right now is to get the result set (Widget1, Widget2) and the create a new select statement that builds its WHERE clause off of an array containing all the results from my previous results set. For instance, the 1st query would be (this is not the exact code, but you get the point):
SELECT * FROM SpecTable WHERE Specification Type = Diameter AND (SpecDetail => 12 AND =< 15)...

Then I receive the results and create an array aResults. Then I create another Select statement to find all the information about those products which match my 1st resulting set; (SELECT * FROM SpecTable WHERE ModelNo = aResults(0) OR ModelNo = aResults(1) OR ModelNo = aResults(n)...)

What I want to do is select from a set of criteria and then apply those results immediately again; can this be done? Should I redesign my schema (given my constraints) in a different way so that I can do this?

Thanks, hope that clears it up. Please ask questions if I can clarify any more.