Normalizing and how to use Access for GUI

[Lars Brownie]

After Jeff Boyce opened my eyes yesterday I started normalizing a
hierarchical cost category sctructure and trying to find an intuitive GUI.
Now it occurs to me why I've kept avoiding this path ;-)

This is what I had:
tbTransaction - ID_main(PK), Other transactions fields, Cat1, Cat2, Cat3
tbCategory - ID_category(PK), Cat1, Cat2, Cat3 (Unique on last 3 text
fields)

Example data in tbCategory:
ID_category Cat1 Cat2 Cat3
1 Committee Office supllies Cartridges
2 Committee Office supplies Paper/envelopes
3 Savings Reservation 4th Anniversary

In my transaction form I had 3 cascading combo's to fill in the category
fields. So I stored all 3 category textvalues in my transaction table.

This is what I have now:
tbTransaction - ID_main(PK), Other transactions fields, ID_cat2cat3

tbCategory1 - ID_cat1(PK), Category1(Unique)
tbCategory2 - ID_cat2(PK), Category2(Unique)
tbCategory3 - ID_cat3(PK), Category3(Unique)

tbCat1Cat2 - ID_cat1cat2(PK), ID_cat1, ID_cat2(Unique on last 2 fields)
tbCat2Cat3 - ID_cat2cat3(PK), ID_cat1cat2, ID_cat3(Unique on last 2 fields)

In tbTransaction I only store ID_cat2cat3 from which I can derive the 3 cat
values (by linking).

Three questions:
1. Is this properly normalized?

2. What would be an easy to use GUI for selecting categories and for
entering new categories?

Ad 2: if I would still want the three combo's on my form how would I store
for instance cat1 when the user selects it, since it's not known yet what
the user will pick for cat 2 and 3. So ID_catcat2 is not known yet. Or
should I make a special category form to pick from and to enter new
categories?

3. I also have costs that only have 2 categories. Should I add a cat3 null
value or 'No category 3' text value for that.

Thanks in advance, Lars

 

[bcap]

There's no absolute right or wrong answer to a question like this, but I
must say that I really don't like the design you've come up with, it seems
very cumbersome.

Some issues come to mind: Firstly, are you absolutely certain that you will
never be asked for a fourth (or more) category? Secondly: Are Category
values unique to their level? For example, can "Committee" be a category 2
value as well as a Category 1 value? (your sample data suggests not). And,
thirdly, is there a limited set of valid combinations of the three
categories, or are they valid in any combination (I suspect the former,
since you have used the word "hierarchical")?

The design you need depends on the answers to the above, but my guess is
this is what you want:

tbTransaction - ID_main(PK), Other transactions fields, Cat1, Cat2, Cat3
(i.e. your original design)

tbCategory1: Category1
tbCategory2: Category1, Category2 (composite primary key, both fields)
tbCategory3: Category1, Category2, Category3 (composite primary key, all
three fields).

The obvious way to design the user interface for transaction entry is (as
you already know) three cascading combo boxes. The avoidance of an ID field
for the categories (which appears to be entirely unnecessary) means that
this will work in continuous and datasheet views as well as form view.

For entering new categories, the obvious design is a form bound to
tbCategory1, with nested subforms for tbCategory2 and tbCategory3. The
problem with this is that a continuous form (which you might want for
tbCategory2) cannot have a subform. You could either display the
tbCategory2 subform in datasheet view, relying on a sub-datasheet for the
tbCategory3 subform, or you could use linked subforms instead of nested
subforms.

 

[a a r o n _ k e m p f]

Access doesn't support normalization

move to SQL Server

that was Codds #1 rule- use a db server

 

[Ken Sheridan]

Lars:

Firstly, having a column in the referencing table only for the lowest level
of a hierarchy does mean that the table is (all other things being equal)
normalized to at least Third Normal Form (3NF) because you have eliminated
the transitive functional dependencies.

I would endorse the point made by bcap however, that using surrogate numeric
keys is unnecessary, as using 'natural' keys does make life easier in a
number of ways, particularly when using correlated combo boxes in continuous
from view as bcap points out. However, because of your point 3 the category
3 table will need a numeric key column for reasons which will become clear
below, but it can still reference the 'natural' key of category 2 by a text
foreign key column, so correlation of the combo boxes in continuous form view
can still be easily achieved.

As regards the interface you'll find a demo of how correlated combo boxes
can be used while preserving normalization to 3NF at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The demo uses the local administrative area of County, District and Parish
in my neck of the woods, but the principle is exactly the same as in your
case. The tables in the demo file do use surrogate keys, because place names
can be duplicated so unique numeric keys are necessary. In fact the file was
deliberately set up in this way to show how correlated combo boxes can be
used in single form view even when a surrogate key is used; at least as far
as the user is concerned, but in reality a hybrid control made up of a text
box overlying a combo box is used. You'll see that I'm not too keen on the
use of hybrid controls like this in continuous form view however, and the
demo includes an alternative approach of a multi-column combo box and unbound
text boxes referencing the columns of the combo box.

At this point it might be worth correcting the impression which some people
have that using 'natural' keys is less 'normal' than using surrogate keys.
Take two tables, Items and Categories with rows in Items as follows:

Item Category
-------------------------
Foo Widget
Bar Widget

and in Categories:

Category
------------
Widget
Thingumajig

This is no less normal than:

Item CategoryID
-------------------------
Foo 1
Bar 1

CategoryID Category
--------------------------------
1 Widget
2 Thingumajig

With the former its essential that cascade updates be enforced of course
when defining the relationship.

As regards the situation where only the top two levels of the hierarchy
apply this is common with geographical data (in England a City might not be
in a County for instance, being a unitary authority). The same solution
applies in your case. In category 2 you need values such as 'N/A' in
multiple rows of the category 3 table, each of them with a different category
2 foreign key value. As the text column can't now be the primary key a
numeric primary key column is necessary. The foreign key column in the
referencing table would thus be a long integer number data type referencing
the numeric primary key of the category 3 table. In the form, therefore this
foreign key column would be bound to a combo box from which you'd select the
'N/A' relevant to the appropriate category 2 value. By using correlated
combo boxes as in my demo file there'd be only one 'N/A' row to select of
course as you'd have already selected the category 1 and category 2 values.
If the alternative of a multi-column combo box is used you'd see a 'N/A' row
for each category 2 value in the list and select the one in question.

Finally for the pros and cons of 'surrogate' and 'natural' keys you might
like to take a look at:


http://community.netscape.com/n/pfx/forum.aspx?msg=19495.1&nav=messages&webtag=ws-msdevapps


In this Bob Hargrove has included the discussions on this topic from the old
CompuServe CASE forum collated by Joe Celko.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

PS: To add new categories use the combo box's NotInList event procedure
which allows the new category name to be typed into the combo box. Where the
new category needs a higher level category assigned (i.e. when adding new
category 2 or 3 values) to it the procedure should also open a form in dialog
mode to enter the higher level category for the new category. Here's an
example for adding a city:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In the frmCities form's Open event procedure is the following code which
assigns the new city name to the DefaultValue property of the City control:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

And BTW, ignore Aaron; he's SQL Server fixated and appears to have stopped
taking the medication again!

Ken Sheridan
Stafford, England

 

[bcap]

Dickhead

Access doesn't support normalization

move to SQL Server

that was Codds #1 rule- use a db server

 

[Terry Kreft]

He he he, I go away for a few months, I come back and you're still here
spouting ill informed rubbish.



--
Terry Kreft


Access doesn't support normalization

move to SQL Server

that was Codds #1 rule- use a db server

 

[Lars Brownie]

Thanks for your comments, bcap.

Some issues come to mind: Firstly, are you absolutely certain that you
will never be asked for a fourth (or more) category? Secondly: Are
Category values unique to their level? For example, can "Committee" be a
category 2 value as well as a Category 1 value? (your sample data
suggests not). And, thirdly, is there a limited set of valid combinations
of the three categories, or are they valid in any combination (I suspect
the former, since you have used the word "hierarchical")?

Re 1: as sure as a comet will hit the earth in the next ten years
Re 2: indeed, unique to their level. Theoretically Committee could be a
value for category 2 as well, but that would be really exceptional.
Re 3: yes, there is a limited set of valid combinations.

You make me rethink my whole rethinking again... I kinda liked the fact that
I only had to store one ID_number in my main table in stead of 3 text
values.

I'll do some further experimenting.

Lars

 

[Lars Brownie]

Thanks for taking the time for your thorough explanation and tips! I get
most of it

I'm beginning to get a better view now on how to handle new values or value
changes. I checked your sample mdb which looks really good. As a test, with
my normalized setup, I managed to create a 3 field combo in my main table,
storing only the ID_number there but showing all three values, but I don't
think I will persue that.

Btw: I was wondering, in your Districts table you have a composite key of
District_ID and CountyID. Shouldn't this index be unique?

Your solution seems in between bcap's solution and my normalized setup. I'll
do some more testing. This is a good learning project.

Thanks again,

Lars

 

[Aaron Kempf knows nothing]

Access doesn't support normalization

move to SQL Server

that was Codds #1 rule- use a db server