cat and a dog, and you are right, but my application is more like this - say you
have several sets of unvented gas logs (in a heatilator box), some vented gas logs (for chimney use), and some
electric space heaters. Say you have 2 categories - gas logs and space heaters. The unvented gas logs are both
gas logs and space heaters, the vented gas logs are only gas logs, and the space heaters are only space heaters.
Now you have many categories to many items. That's the kind of thing I was talking about. I think I get where
you were going though, and thanks for the help!

Corey,

I have taken the liberty of putting this back on the list, so that others can
follow (and learn, or offer alternative inspiration) - I have assumed that the products mentioned don't
qualify as private/competitive information, and so trust this will be ok with you.

If you caught a follow-up discussion, a suggestion was made about a many-to-many
relationship, and how to have a table 'in between' the other two to cope (the middle table having a 1-m
relationship with each of the others, and thus creating/simulating a m-m transform) - but then, if you fold the cat
and subcat tables into each other we're back to needing two tables again ... and so you can see that the answer is
not straightforward.

Getting into a tutorial on data analysis is way behind the scope of an email
msg. Typically a data analyst will first try to identify all of the 'entities' the database will record, and the
relationships between them (1-1, 1-m, m-1, m-m). Thereafter we'll look at the data-flows between tables in the
database and from some data capture situation to get it into the database, and flows out of the database to
screens, reports, etc. Finally we'll try to look at how the data behaves over time: when it is first captured,
how it might change while it's in use, and how it is removed from the database - entity by entity. We would
apply a number of tools and techniques to try to get this all sorted out and arrive at a database schema -
did I mention 'normalisation' earlier? Plus it is somewhat cyclic in nature - and can be a very
trying/frustrating exercise. At this level of abstraction, clearly you are talking about "cats" and "sub-cats", but must be
very aware of the relationships between "space heaters" and "log fires", etc.

Are you only dealing with these three types of heaters, or do you have a much
wider product line (and if so, does this type of relationship occur often)? For what reasons do you want to
maintain and process according to the cat<->sub-cat relationship? Is there ever a sub-sub-cat situation? Because I
don't know/can't know these things, anything I say has a high foot-in-mouth potential.

Venturing forward, perhaps it would be helpful to think about an example
something like...

Products table Prod.Id Prod.Nm Prod.Subcat(est/lowest level) 1 Pot Belly 2 2 Pioneer 2 3 Settler 3 4 Cozy 3

Categories table Cat.Id Cat.Nm Cat.SuperCat (higher level) 1 unvented 2 gas log 1 3 space htr 1

Notice that no heater in the Products table is listed as being in Category 1
(=unvented), that's because unvented heaters are either gas logs or space heaters and so you must use the
sub-cat/most precise description (I can think of reasons why you might use all three, but let's try to keep it
simple).

This would allow you to (at least) list all the heaters in a particular category
by cycling through each row in Categories noting the Cat.Id and finding all the Products with a matching
Prod.Subcat - which would list nothing under vented and everything (else) under "gas log" or "space htr". If you want
to list every heater (in our sample) twice: once under "unvented" and once under one of the other two cats,
you will have to get smarter and note the Cat.Id when the Cat.SuperCat is zero or NULL and then scan the
Categories table first to find rows where the Cat.SuperCat is equal to the Cat.Id, and then use that second
(sub-cat) row's Cat.Id against the Products table.

Working back the other way, if you are listing products and want to label each
by category, eg: Pot Belly gas log unvented Pioneer gas log unvented Settler space htr unvented Cozy space htr unvented

Then you can see that you have to start from the Products table, note the
Prod.Subcat and then relating that to Cat.Id find the sub-cat first, and then if the sub-cat reports that it is part
of a cat, relate the Cat.SuperCat to the matching Cat.Id to get the Cat.Nm of the super-cat/cat. This is a good
example of how you are 'walking the tree' of the 'tree structure' - even if you only have two levels (cat and
sub-cat) in your tree (the subject of another 'tutorial' last night).

Notice that when you are programming a new product/data-entry situation (for one
example) you will have to require that the new product be related to a category - and what will you do if
"unvented" is selected? Perhaps you will need to insist that all products are described by (highest level of
detail, ie) sub-cat. Does that mean that if the data-entry refers to a Cat.Id where Cat.SuperCat=0/NULL it must be
rejected? In the case of a gas log fire the answer is yes. However what of other products where there is no
Cat-SubCat relationship, the Cat.SuperCat will presumably also be set to 0/NULL and yet that sounds like a
valid situation - so now the business rule/definition becomes Cat.SuperCat=0/NULL is ok, providing the Cat.Id
value doesn't appear as a Cat.SuperCat in another row of the Categories table... [and what was it that
Lewis Carroll said about the Cheshire Cat as it started to disappear...?]

Another idea which might work if you have very few categories that basically
don't change, might be to describe each product by using an ENUM or SET datatype (according to your DBMS). Some
list members will reply now with dire warnings or even howls of protest and accusations of heresy against the
rules of data analysis (and they'd be correct), but whatever it takes to get the job done, right? However, if the
sub-cats ever change significantly, you won't be pleased...

Sorry I can't say "here's your answer", but then that's why there is a data
analysis phase - to spend time in consideration (of all of the facts) and produce an answer. (not even "the"
answer - you know the expression "horses for courses"?) Consider this, you have already learned major lesson
number one, and that is if you plough in to start implementing before completing an analysis, it looks as if
you are quick on the draw, but there'll be tears before bedtime!

Hope I've at least helped you some way down the road - keep plenty of that cat food on hand! =dn