Self-Referencing One to Many

[Scott A]

What do I do with this type of relationship in Access? I
can't figure out how to set up the tables!

I have a table of categories, and have included records
for all categories, including sub-categories. (they're all
categories, right?)

My categories table has the following structure:

CategoryID (PK)
CategoryCode
CategoryDescription
CategoryType (numeric values for master and subcategories)
ParentID (PK of parent category)

This works fine as long as the subcategory only belongs to
one parent group, however, there are subcategories that
belong to more than one parent category (i.e., 'General').

I shouldn't be entering more than one value in the Parent
ID field, should I?

If anyone has a better approach, please let me know.

Thanks,

Scott A

 

[MDW]

I'm a bit confused by what you're trying to accomplish, I
guess. However, the table structure you have now should be
OK for as many levels of categories as you want. I would
use CategoryID and ParentID as a compound primary key
(though you could get away with just having CategoryID as
the PK, and put an index on ParentID).

I would say that if a CategoryID is the same as the
ParentID, then we know it's a "top level" category. Each
other row would correspond to one specific (sub)category,
and its ParentID would indicate its placement within the
tree. Thus, you'd have several rows for
category "General", but each with a different ParentID.

So if the table looks like this:
CategoryID CategoryDescription ParentID
001 Databases 001
002 Tables 001
003 Queries 001
004 SQL 001
005 Languages 005
006 VB 005
007 VBScript 006
008 VBA 006
009 SQL 005

The resulting tree would look like this:
Databases
--- Tables
--- Queries
--- SQL
Languages
--- VB
--- --- VBScript
--- --- VBA
--- SQL

 

[John Vinson]

This works fine as long as the subcategory only belongs to
one parent group, however, there are subcategories that
belong to more than one parent category (i.e., 'General').

I shouldn't be entering more than one value in the Parent
ID field, should I?

Of course not. You're on the right track!

If you have a Many to Many relationship between two different tables,
you create a "junction" table to model the relationship itself. In
this case you have a Many to Many self join relationship - again, you
need a junction table! It would be something like:

Subcategories
ParentCategoryID
ChildCategoryID

This table would be on the "many" side of *two* one-to-many
relationships to the Catgories table, joining CategoryID to both of
its fields.

 

[Scott A]

Aha! I hadn't realized that this was actually a M:M
relationship.

I set up the junction table and have been able to do
create as many parent-child relationships between items in
this table.

Thanks for the tip,

Scott