Re: How to represent category, subcategory, product

From: JOG <>
Date: 22 Nov 2006 14:37:27 -0800
Message-ID: <>

On Nov 22, 5:31 am, "Lennart" <> wrote:

> Neo wrote:[...]
> > Based on above data, we can derive Computer and Printer are currently
> > subCats of Device,
> > because:
> > Device = {DellPc1, HpPr1}
> > Computer = {DellPc1}
> > Printer = {HpPr1}
> > Computer and Printer are proper subcategories of Device.Now I see you, once again :-)

I'd take neo's responses with a pinch of salt TH. He is promoting his own pet project. In terms of a serious answer to your question, I echo the sentiments that your particular modelling needs will affect the correct implementation, and that without any understanding of them suggestions here are going to be completely in the dark. However, for what it is worth I have learnt that reducing my problem to an underlying set of propositions and analyzing them often helps me far more than thinking in terms of tables, connections and categorizations. Consider a contrived category/sub_category set of data such as:

By normalizing to 3NF it becomes clear that you are actually dealing with two distinct predicate structures:

  • bennie is a cat.
  • tc is a cat.
  • yogi is a bear.
  • kermit is a frog.
  • nemo is a fish.
  • All cats are mammals.
  • All bears are mammals.
  • All frogs are amphibians.

or written as relations:

R1(id, category) = { (bennie, cat), (tc, cat), (yogi, bear), (kermit, frog), (nemo, fish) }
R2(sub_category, super_category) = { (cat, mammal), (bear, mammal), (frog, amphibian) }

This seems an intuitive and logical approach to me. To get a list of everything in a subcategory:
SELECT * FROM R1 INNER JOIN R2 ON (R1.category = R2.sub_category)

To get a list of everything not in a subcategory: SELECT * FROM R1 ANTI JOIN R2 ON (R1.category = R2.sub_category) or:
SELECT * FROM R1 WHERE NOT EXISTS (SELECT * FROM R2 WHERE R1.category = R2.sub_category)

To get a list of every item and its root parent category: SELECT id, super_category AS category FROM R1 INNER JOIN R2 ON (R1.category = R2. sub_category)
SELECT id, category FROM R1 ANTI JOIN R2 ON (R1.category = R2. sub_category)

I emphasize that this may not be the correct approach for what /your specific situation/ requires so do not take it as a prescription, but rather merely as food for thought. Received on Wed Nov 22 2006 - 23:37:27 CET

Original text of this message