I would have modelled the category/subcategory relationship the very same way ... a few years ago.
Now I understand better why some say it is bad habit to have a FK point to a PK of the same table (self-referencing tables). It can make maintenance/manipulation problematic, once the table is filled with records.
Imagine you want to do something simple like copying your srCategory records to another table with the very same structure. It should be a breeze. But it isn't. The DBMS will check for each inserted record if the FK constraints are met. That would mean you'd have to insert them in such a sequence that you only add records that point to a parent record that is already present. A quick solution could be to drop the FK constraint in the destination table, do the INSERTS, then create the FK constraint again: ugly.
A better approach is to provide a junction-table srCategorySubcategory with two FK's to srCategory, ParentCategoryId and ChildCategoryId (PK or unique index defined on ChildCategoryId).
Now, if you want to copy your srCategory records to another table with the very same structure, it is a breeze: first copy srCategory then the junction table srCategorySubcategory.
That said, it doesn't occur that many. Of all the self-referencing tables in our database (not so many really), I have only experienced that maintenance problem once. But then it wasted a lot of my time. I don't model self-referencing tables any more.
Seems you're using PowerDesigner. I love its clearness over UML.
Last edited by Wim; 11-20-08 at 09:57.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages
Do you thing it is still bad idea to use one table?
Eliminating the use of self-referencing tables is good practice. It makes maintenance afterwards easier. I didn't saw the relevance of this rule, until it bit me.
Imagine populating your table the very first time: Your INSERT-statements will have to be submitted in the exact sequence so all referenced parents exist before you INSERT its children.
Make the comparison: During development one must frequently populate (related) tables and afterwards delete them (not drop), you must do that it in a special sequence: making sure you INSERT in parent tables before inserting in child tables. And apply the reverse order when emptying the tables: make sure you DELETE from child tables before deleting the parent tables.
The order of filling and emptying tables is one thing, if you also must apply that logic to individual INSERT statements, it really gets cumbersome. That's the whole point.
Moreover, does the junction table works ok for categories
and 5 level down subcategories?
Yes, no problem. The difference between the original srCategory and srCategorySubcategory is not that big: