Help! - Table design: Normalization and subclassing questions

[stgpatrick]

Hello,

I am trying to develop a "Record Keeping" database. The purpose of this
database is merely to print one report that lists all of my important and
pertinent information upon my disability/death. I would like it to be
somewhat easy to enter/edit data, but that is not a requirement. The output
is necessary.

It will have categories such as: Personal & Legal Documents; Income Sources;
Investments & Retirement, etc.

Each category has subcategories such as: Living Will (Under category:
Personal & Legal Documents); 401(k) Account (under category: Investments &
Retirement), etc.

Each subcategory has many fields and can have more than 1 record (e.g. I can
have more than one 401(k) plan, etc.)

Now, I'm still working on the normalization for this, but I'm murdering my
brain cells on subclassing the subcategories. It seemed simple enough, I've
read as much as I can find on sub classing and have gone to The Access Web
and have downloaded the onetoone database example, it made sense, but It
doesn't quite fit the bill for me.

This is what I'd like to see:

Report for P.C.
-----------------
Investments & Retirement
401(k) Account
Broker Name: blah
Account #: 123455
401(k) Account
Broker Name: more blah
Account #: 098432
IRA Account
Type: Roth
Account #: 980498
Personal & Legal Documents
Living Will

etc.
-----------------

Can anyone help me? Thank you so much in advance. I've learned a few things
just simply browsing through these access newsgroups.

My tables so far...And this link to show a relationship diagram:
http://www.myfsi.net/erdiagram.jpg

tblContacts
ContactID (primary)

tblReports
ReportID (primary)
ContactID (foreign)

tblReportDetails
ReportID (foreign)
SubcategoryID (foreign) - I've included this per the OneToOne database
example, this is to subclass my categories and be able to choose the correct
subform.

tblSubCategories
SubCategoryID (primary)
CategoryID (foreign)

tblCategories
CategoryID (primary)

tblSubCategory1 (not real name - substitute "tbl401kplans" for example)
ReportID (primary)
SubCategoryID (foreign) - I've included this because one subcategory can
exist in more than one category - Is this too much, should I create seperate
subcategory tables even though they may be alike, It's fine with me as there
would be roughly 50 subcategories?

tblSubCategory2
.... etc. (up to 50 of these?)

 

[stgpatrick]

Update:

I have solved this problem and am posting "my" solution for those that are
looking to create a same or similar program.

To understand the answer, please completely read the previous post (the
question).
Also, refer to this link for a graphic that may better explain what I did:
http://www.myfsi.net/erdiagram2.jpg
(it's updated)

Now,

Here are my Epiphanies:

1. I used a "Hierarchy" or "Tree" or Self-referencing table to categorize.
This allows you essentially unlimted number of "levels"
It is as simple as adding an additional field called "ParentID" to the table
of categories. It stores the Primary key of that same table

2. I used a modified subclassing scheme. Traditionally you have a one-to-one
relationship between a table that stores your classes and individual tables
that further define your classes.
However, I did not a one-to-one ratio since I may have more than one
instance of a class. So, instead I allowed a one-to-many relationship between
my table listing all of Categories and the individual category tables.

Now, the reason I allowed some relaxation on normalization was due to my
requriments. First, It's pretty much only myself working with this database,
so I don't care about the forms or interface, and can work with the tables if
necessary.
Also, the purpose of this program is to print only one report. <period>

<vent>Now, On another note: I'm dissapointed that my last post didn't get
any replies, as I felt it was an interesting challenge.
Especially since I had done all of the hard work, but was simply looking for
verification.
But, I'm sure I know why: Too Much Information. I was simply trying to avoid
5 emails attempting to explain my issue.
I will try to do better communicating in the future. </vent>