Example of fully normalized database

[Lars Brownie]

Can someone point me to an example of a fully normalized access database.
I'm planning of further normalizing one of my databases and it would be nice
to see how this is handled. I guess normalizing is always a choice between
following the normalization rules and keeping your database
easy-to-develop-in and easy-to-work-with.
Thanks, Lars

 

[Lars Brownie]

Thanks Jeff.

With the main entities in my database I normalize as you described. But...

I'm not sure I'd agree with your "choice". Just one person's opinion:
choosing not to normalize means you and Access will have to work harder to
get "simple" things done. Normalization isn't done solely for the sake of
the "rules", but because data that's well normalized is easier for Access'
relationally-oriented features/functions to work with.

In some cases this is not true I think. If you normalize further, it will
take you more time to create queries, forms, and certain functionalities.

For instance, I have a request table with a field Type_Request. This field
is filled with 1 of the 15 types of requests and can contain up to 20
characters. Since several requests have the same Type_Request, data is
repeating in that field. For normalization and for saving memory, I should
only store the ID_Request.

Let's say I have another 3 fields like that in that same table. For every
query that need those values I need to add the corresponding tables. I can
also imagine that queries that are already quite complex, would become too
complex when adding several other tables which would mean I would have to
break it up in 2 or more queries.

Also at the moment it's very easy in my form to search on the Type_Request
field, just hit Crl-F. When only ID_request would be there, I need to create
an extra functionality to search on the actual Type_Request name. Also, now
I can use the quick-filter on a part of the Type_Request field's value. If I
store only the ID, I can't use the quick filter anymore.

Maybe I'm more worried than I should, that's why I was wondering if there is
an example-mdb online that demonstrates how it can be done and that it is
easier than I tink.

Lars

 

[Jeff Boyce]

Lars

I'm not sure I'd agree with your "choice". Just one person's opinion:
choosing not to normalize means you and Access will have to work harder to
get "simple" things done. Normalization isn't done solely for the sake of
the "rules", but because data that's well normalized is easier for Access'
relationally-oriented features/functions to work with.

Easy-to-develop-in and easy-to-work-with are subjective ratings, and depend
extensively on the amount of experience you have with normalization, MS
Access, and graphical user interface design.

Here's an incredibly simplistic example of what I would consider a
well-normalized design:

tblPerson
PersonID
FirstName
LastName
DateOfBirth

tblMagazine
MagazineID
MagazineTitle

trelSubscription
SubscriptionID
PersonID
MagazineID
SubscriptionDate
SubscriptionDuration

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Michael Gramelspacher]

With the main entities in my database I normalize as you described. But...


In some cases this is not true I think. If you normalize further, it will
take you more time to create queries, forms, and certain functionalities.

For instance, I have a request table with a field Type_Request. This field
is filled with 1 of the 15 types of requests and can contain up to 20
characters. Since several requests have the same Type_Request, data is
repeating in that field. For normalization and for saving memory, I should
only store the ID_Request.

Let's say I have another 3 fields like that in that same table. For every
query that need those values I need to add the corresponding tables. I can
also imagine that queries that are already quite complex, would become too
complex when adding several other tables which would mean I would have to
break it up in 2 or more queries.

Also at the moment it's very easy in my form to search on the Type_Request
field, just hit Crl-F. When only ID_request would be there, I need to create
an extra functionality to search on the actual Type_Request name. Also, now
I can use the quick-filter on a part of the Type_Request field's value. If I
store only the ID, I can't use the quick filter anymore.

Maybe I'm more worried than I should, that's why I was wondering if there is
an example-mdb online that demonstrates how it can be done and that it is
easier than I tink.

Lars

Are you saying that you do not use tables for lookups, because you want to make
it easier on the database engine, in that it will not have to do so many joins?
That seems compassionate.

CREATE TABLE RequestTypes (
request_type VARCHAR (20) NOT NULL PRIMARY KEY
);

CREATE TABLE SomeTable (
table_id VARCHAR (10) NOT NULL PRIMARY KEY,
request_type VARCHAR (20) NOT NULL
REFERENCES RequestTypes (request_type)
ON UPDATE CASCADE
);

This way you do not need a join to get the request_type, and you still have a
way to limit request_types to those values in the table.

If you were to have a request_type_id, you would still need a unique index on
request_type. Many will say that it is best to have an autonumber key on a
lookup table. Some reasons are that is too hard on the database engine to have
a 20 character versus a long integer key. It is too hard on the database engine
to cascade updates, if a request_type changes. There may be other reason that
elude me at the moment.

 

[Lars Brownie]

Are you saying that you do not use tables for lookups, because you want to

make
it easier on the database engine, in that it will not have to do so many
joins?

No, at the moment I have one lookup table that hold the lookup values for
all lookup fields. And I would still need it after further normalizing. What
I was talking about was storing the ID number in stead of the actual value
in the main table.

Lars

 

[Michael Gramelspacher]

No, at the moment I have one lookup table that hold the lookup values for
all lookup fields. And I would still need it after further normalizing. What
I was talking about was storing the ID number in stead of the actual value
in the main table.

Lars

Wow! I have read warnings about that in the literature. It's called One True
Lookup Table (OTLT) or Massively Unified Code Key (MUCK).
http://www.projectdmx.com/dbdesign/lookup.aspx

 

[Arvin Meyer [MVP]]

What you may have overlooked is that normalization rules are part of
relational database design, and are necessary for efficient processing of
the data.

Relational databases are called that because they follow Relational Set
Theory rules in Calculus, not because tables have a relationship.
Normalization facilitates proper Set Theory operation.

There are reasons to denormalize. OLAP cube processing is one. An OLAP cube
is a denormalized copy of normalized data. Other reasons can be to use temp
tables to ease repeated long processing times, or storage of audits, or
history when all the elements of a calculation aren't stored.

 

[Lars Brownie]

Thanks for the pointer. Valid arguments.

I just thought it was a good idea since it saves me about 10 lookup tables.

Lars

 

[Lars Brownie]

What you may have overlooked is that normalization rules are part of
relational database design, and are necessary for efficient processing of
the data.

Relational databases are called that because they follow Relational Set
Theory rules in Calculus, not because tables have a relationship.
Normalization facilitates proper Set Theory operation.

There are reasons to denormalize. OLAP cube processing is one. An OLAP
cube is a denormalized copy of normalized data. Other reasons can be to
use temp tables to ease repeated long processing times, or storage of
audits, or history when all the elements of a calculation aren't stored.

I see.
About the example in my 2nd post, do you agree that I should only store the
ID number while this also complicates my application design?

Lars

 

[Lars Brownie]

Thanks for your help.

See inline:

Memory is fairly cheap. Are you concerned about "saving memory" because
you
are attempting to build an application that will fit on a cell phone or
PDA?

I was concerned that if my tables would increase to a million records my mdb
file would get too big as I'm storing the actual values and not the ID
number in the tables.

That sounds like a perfect place to use a combobox. No need to remember
codes and no need to search (Ctrl-F). Just fill the combobox using a
query
against your lookup table(s).

I already have a combo for that on my form but sometimes you also need to
search in or filter on the main table on (a value of) the combox.

But I just looked at the Northwind database to see how it is done and this
is a real eyeopener! I can actually add a employee-ID to the Order table and
add a combobox to it, in a way that in datasheetview I can still search and
filter on the Employee name. This also works like that in forms, the form
just copies the underlying SQL query. Great! I also noticed that in queries
I do need to add the Employee table to be able to set criteria to the field,
which is understandable.

Wish I had know this before starting to develop this app. I need to rethink
it all...

Thanks again, Lars

 

[Jeff Boyce]

Lars

I'm thinking you might be confusing how the data gets stored (normalized
tables) with how it gets presented to users (graphical user interface).

Thanks Jeff.

With the main entities in my database I normalize as you described. But...


In some cases this is not true I think. If you normalize further, it will
take you more time to create queries, forms, and certain functionalities.

So, your concern is about how you use the tool? I'm atypical of many
developers in that my focus is on how I present the data (actually,
structured/organized data is "information") to the users.

I see applications like Word and Excel as "bookcases" ... and most folks
already know how to put books on and take books off.

I see Access as more of a "power saw". Most folks never have a need to work
with a power saw, they just want what it can make, say, a bookcase! But
folks who want to use Access to build other folks' bookcases need to learn
how to use it properly.

For instance, I have a request table with a field Type_Request. This field
is filled with 1 of the 15 types of requests and can contain up to 20
characters. Since several requests have the same Type_Request, data is
repeating in that field. For normalization and for saving memory, I should
only store the ID_Request.

Memory is fairly cheap. Are you concerned about "saving memory" because you
are attempting to build an application that will fit on a cell phone or PDA?

Let's say I have another 3 fields like that in that same table. For every
query that need those values I need to add the corresponding tables. I can
also imagine that queries that are already quite complex, would become too
complex when adding several other tables which would mean I would have to
break it up in 2 or more queries.

I have some applications in which I "chain" a half-dozen queries along, one
to the next. I have no doubt that a SQL wiz could come up with a very long,
very complicated SQL statement that would do the same thing, but I have to
be able to maintain the application. I'm building not only for the users'
ease of use, but for my ease of upkeep.

Also at the moment it's very easy in my form to search on the Type_Request
field, just hit Crl-F. When only ID_request would be there, I need to create
an extra functionality to search on the actual Type_Request name. Also, now
I can use the quick-filter on a part of the Type_Request field's value. If I
store only the ID, I can't use the quick filter anymore.

That sounds like a perfect place to use a combobox. No need to remember
codes and no need to search (Ctrl-F). Just fill the combobox using a query
against your lookup table(s).

Maybe I'm more worried than I should, that's why I was wondering if there is
an example-mdb online that demonstrates how it can be done and that it is
easier than I tink.

Take a look at the Northwind database that comes with Access.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Clif McIrvin]

Thanks for your help.

Wish I had know this before starting to develop this app. I need to
rethink it all...

Thanks again, Lars

Lars, 20 years ago I considered myself a good programmer / systems
analyst. Circumstances changed, and other than occasionally needing to
use a computer to do my job I've had very little to do with them since.

Circumstances changed again, and it has become necessary to roll my own
application to create a tool to manage the data my current job
generates. [Why? Different discussion <grin>.]

Since I started banging my head against the wall of the on-board Access
documentation about 18 months ago I've come up with the 'guts' of an
application that 'sort of' gets the job done.

Somewhere along the line, I discovered these newsgroups, and, like you,
have come to recognize that I pretty much need to start over from
scratch and do it right this time .

Looking back, I'm nothing short of amazed at what I've learned just
lurking here for a few short months. For one thing, in the last few
weeks I've begun to get an inkling of the incredible power and
capability of queries.

Through a series of nested ('chained' ?) queries, I've recently managed
to make a fairly largish Excel linked worksheet begin to look like
normalized data ---

I guess what I'm trying to express is that there's a lot more power and
elegance in this tool that you and I are dabbling with than we (at least
I!) had ever guessed, and I'm quite confident that we're in the right
place to have someone open our eyes.

There is an incredible amount of experience and knowledge 'in this
room,' and my hat's off to all you who graciously lend a helping hand!

Thank You All!


BTW: Jeanette, in case you're following this thread, Congratulations!
that nice, shiny MVP looks good behind your name!

 

[(PeteCresswell)]

Per Lars Brownie:

No, at the moment I have one lookup table that hold the lookup values for
all lookup fields. And I would still need it after further normalizing. What
I was talking about was storing the ID number in stead of the actual value
in the main table.

I was forced into that on one project. Can't recall how many
conceptual tables were folded into it - and maybe my reaction is
really more of a reflection of limited brainpower - but I thought
it made development and the next poor fool's understanding of the
DB significantly harder.

It's been awhile, but it was Oracle and I think the techies said
it was something to do with performance.

 

[(PeteCresswell)]

Per Lars Brownie:

Can someone point me to an example of a fully normalized access database.
I'm planning of further normalizing one of my databases and it would be nice
to see how this is handled.

Has anybody even heard of a fully-normalized DB?

It's been a long time since I read the whole spiel on
normalization, but my recollection was that after 2nd or 3rd
normal the rules seemed like they'd be a lot more difficult to
implement.

 

[Arvin Meyer [MVP]]

I see.
About the example in my 2nd post, do you agree that I should only store
the ID number while this also complicates my application design?

The ID is the only thing required. Because Access is a desktop database
which does the processing at the workstation, not at the server, you may
have to run one query to limit the data to only that required, then a second
query to do the calculations on that data.

 

[Arvin Meyer [MVP]]

Normalization, as defined by Codd in his original thesis on relational
database design, really requires the first 3 rules. A purist can further
refine the design to 6 (actually 7 with Boyce-Codd Normal Form) but that, as
you mention, is much more difficult, and usually unnecessary to make SQL
work efficiently. I've never purposely gone beyond 3NF, and I've only seen 1
production database that has.