Re: How to represent category, subcategory, product

Wow, what a great response! I waited days with no reply on a SQL Server group, I should have come here first.

Thanks Gene, Lennart, Neo, Bob for your suggestions, some very helpful ideas there. As Bob suggests, this was something of a reduction of my current requirements so I'll have to consider how the various options sit with the rest of the design, but I can see how each of approaches would be appropriate in situations I've come across.

I think Lennart's method fits the best in this case. I must admit I didn't realise it was possible to have a two-field foreign key. Although for some reason which I can't fathom, this method appears to allow a Product to reference a non-existent Category or Subcategory as long at the second part of the key is a null. I don't know if it's possible to tighten up this aspect?

Neo, your suggestion is intriguing, I'll be looking into it. It sounds a very unusual approach. Is it? It seems you'd need a pretty heavy piece of SQL every time you wanted to reference the derived table of subcategories, and I don't suppose it would be possible for another table to have a foreign key onto the derived table, i.e. for an entity which could only ever be a direct member of a subcategory, not a top level category.

I'm surprised how difficult it's been to find a decent resource which helps teach these building blocks of relational database design, I haven't found any database counterparts of the libraries of algorithms or design patterns that you find in the programming world. Everything I've seen in the way of database tutorials so far seems to teach you normal form and send you on your way. Surely there must be some way to break into this circle without nagging the sages of usenet!

Thanks all, TH. Received on Wed Nov 22 2006 - 00:47:21 CET