relationship design advice

[Bon]

Hi All,

Think I may not be looking at this right and could use some brainstorming
please.

Here's the details of tables:

Top Category
Sub Category (ies)
Sub Sub Category (ies)
Details

My conundrum is that there may not be sub-category or categories under a top
category and I would need to show the details directly off of the top
category. I'm looking for suggestions on best table, relationship, and/or
query design?

 

[Roger Carlson]

Think of all of your categories as sub-categories, and have them all in a
single table. Something like this:

SubCategories
=======
SubCategoryID (pk)(autonumber)
CategoryID (fk-SubCategoryID) (autonumber)
CategoryName
etc.

In the CategoryID field, you put the ID number of the level above it. In
the case of a Top Category that does not have a subcategory, put the same
number in both fields. You can Join the table to itself on
CategoryID--SubCategoryID to find the hierarchy.

The SubCategoryID will go in the Details table:

Details
=====
DetailsID
SubCategoryID
etc.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Bon]

I see. Sounds like the idea - now I have to figure out how to design the
form to pull the correct ID (or nudge the user to pull the correct ID). I'm
sure I'll be back for more in the meantime, thanks very much for taking time.

Bonnie

 

[Armen Stein]

Hi Bonnie,

This is a great flexible hierarchical structure, and I agree with
Roger that it's an elegant way to solve the problem you presented. But
before you go too far you need to know that displaying those records
hierarchically (on forms or reports) can be tricky. You may need to
be able to handle unlimited indenting for subcategories on reports,
for example. And in forms, some developers decide that the only way
to show the hierarchy properly is to use a treeview control, which
takes some VBA programming to use.

So it depends on what you need to do. If you just need to know which
subcategory to relate some other records to, you should be okay. It's
the presentation of the hierarchy that gets more difficult.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Bon]

Honestly my head is spinning a bit on this one. She has so many levels, I
just gave a simple example. Making a user-friendly data entry screen is a
challenge. Looks like I will have to customize the PK because an autonumber
can be the same in two tables and is a bit touchy.

I would love to see an example of how someone has done this elegantly. And
I haven't used a Tree-View before so I'll have to research that.

Thanks all

 

[Armen Stein]

Honestly my head is spinning a bit on this one. She has so many levels, I
just gave a simple example.

Yes, this is always a tough one. The classic case is an org
structure. Each employee or position (except the top one) has
multiple direct reports. The technique is a self-referencing
relationship:

tblEmployee
EmployeeKey (PK)
EmployeeKey_ReportsTo (FK

or specifically to your case

tblCategory
CategoryKey (PK)
CategoryKey_BelongsTo (FK)

Making a user-friendly data entry screen is a
challenge. Looks like I will have to customize the PK because an autonumber
can be the same in two tables and is a bit touchy.

Not sure what you mean by autonumbers being a bit touchy. We use
autonumbers for the keys all the time.

But yes, the issue is not the storage structure - it's the data entry
and reporting that can be difficult. If all you need to do is specify
which Category a Category belongs to, then that's a simple UI - just
use a combobox for the foreign key. But representing the whole tree
visually is harder. In reports you need to indent the different
levels manually.

I would love to see an example of how someone has done this elegantly. And
I haven't used a Tree-View before so I'll have to research that.

On screen, you can use the treeview control, but that does take some
coding. It's really cool for the user, though. Here's an example of
how we use it in one of our applications:

http://www.jstreettech.com/cartgenie/images/large/CG_ProdNavigator_Mgr.jpg

If you Google or Bing VBA treeview control you'll get lots of coding
examples and ideas.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com