Normalization

[JoeB]

I found an interesting thread on this from Nov., 2000, but it didn't
fully answer my question. I understand normalization, but am trying to
find the line between good database design and personal preference.
I'm wondering if I've created too many tables. Normalization as I
understand it would be to create tables if a field can have more than
one occurrance (for example, book titles by an author). But if a field
will only have one value, does it make sense to group into additional
categories? I'll try to keep this short, but the question probably
only makes sense with the actual database involved. It's a fairly
typical application: a large group of stores, information includes the
usual - store nbr, address, phone number. Then I have related tables
such as pbx, with fields for type, remote access number, password,
date installed, etc. Another table with same info for the
vru/voicemail pc. There are other tables, as well. In the example of
the pbx, there's only one at each location, so there's only one record
for each location. Is it unnecessary, then, to have a separate table?
Would it be more efficient or result in faster queries to put all
these fields in one large table, even if I wind up with 50+ fields?
I've used pbx and vru as examples, but there are other similar tables.
More than one vendor, analyst and technician can support a location,
so those are in a separate table, which would be the only way to do it
with normal forms, but I'm unsure about the rest. My design seems
organized and easy to understand, but queries over the network are
torturously slow. If you made it to this point, I'm much obliged.
Thanks.
Joe

[Bruce Dodds]

JoeB wrote:

I found an interesting thread on this from Nov., 2000, but it didn't
fully answer my question. I understand normalization, but am trying to
find the line between good database design and personal preference.
I'm wondering if I've created too many tables. Normalization as I
understand it would be to create tables if a field can have more than
one occurrance (for example, book titles by an author). But if a field
will only have one value, does it make sense to group into additional
categories? I'll try to keep this short, but the question probably
only makes sense with the actual database involved. It's a fairly
typical application: a large group of stores, information includes the
usual - store nbr, address, phone number. Then I have related tables
such as pbx, with fields for type, remote access number, password,
date installed, etc. Another table with same info for the
vru/voicemail pc. There are other tables, as well. In the example of
the pbx, there's only one at each location, so there's only one record
for each location. Is it unnecessary, then, to have a separate table?
Would it be more efficient or result in faster queries to put all
these fields in one large table, even if I wind up with 50+ fields?
I've used pbx and vru as examples, but there are other similar tables.
More than one vendor, analyst and technician can support a location,
so those are in a separate table, which would be the only way to do it
with normal forms, but I'm unsure about the rest. My design seems
organized and easy to understand, but queries over the network are
torturously slow. If you made it to this point, I'm much obliged.
Thanks.
Joe

The problem isn't clear to me. Are you considering adding the pbx
attributes (type, remote access number, password,date installed) to the
store table? You should only do that if each store will always have a
pbx, and will never have more than one.

If your queries are slow, there may be ways to speed them up short of
redesigning the tables. There are tips on this in the Access Developer's
Handbook, if you have it.

[TC]

Joe, it is very difficult to comment on any proposed table structure, unless
you state the primary key of each table.

For example, you say there is a table "pbx, with fields for type, remote
access number, password, date installed, etc.". It is really not possible to
comment on that table at all, unless you state its primary key.

Depending on the primary key, the table might be:
- correctly designed;
- correctly designed, but have restricions that you might not be aware of,
or
- incorrectly designed.

So maybe post back with some ables >and< associated rimary keys?

HTH,
TC
"JoeB" <jb**********@fds.com> wrote in message
news:e6**************************@posting.google.c om...

I found an interesting thread on this from Nov., 2000, but it didn't
fully answer my question. I understand normalization, but am trying to
find the line between good database design and personal preference.
I'm wondering if I've created too many tables. Normalization as I
understand it would be to create tables if a field can have more than
one occurrance (for example, book titles by an author). But if a field
will only have one value, does it make sense to group into additional
categories? I'll try to keep this short, but the question probably
only makes sense with the actual database involved. It's a fairly
typical application: a large group of stores, information includes the
usual - store nbr, address, phone number. Then I have related tables
such as pbx, with fields for type, remote access number, password,
date installed, etc. Another table with same info for the
vru/voicemail pc. There are other tables, as well. In the example of
the pbx, there's only one at each location, so there's only one record
for each location. Is it unnecessary, then, to have a separate table?
Would it be more efficient or result in faster queries to put all
these fields in one large table, even if I wind up with 50+ fields?
I've used pbx and vru as examples, but there are other similar tables.
More than one vendor, analyst and technician can support a location,
so those are in a separate table, which would be the only way to do it
with normal forms, but I'm unsure about the rest. My design seems
organized and easy to understand, but queries over the network are
torturously slow. If you made it to this point, I'm much obliged.
Thanks.
Joe

[Mike Sherrill]

On 21 Nov 2003 10:31:38 -0800, jb**********@fds.com (JoeB) wrote:

Normalization as I
understand it would be to create tables if a field can have more than
one occurrance (for example, book titles by an author).
If you learned normalization from a book, you need to get a better
book.
In the example of
the pbx, there's only one at each location, so there's only one record
for each location. Is it unnecessary, then, to have a separate table?
I'm pretty sure you need a table for pbx. But, for the record, how
did you come to the decision to put it in a table of its own in the
first place?
Would it be more efficient or result in faster queries to put all
these fields in one large table, even if I wind up with 50+ fields?
It might make some queries faster, but you'll find it hard (or
impossible) to enforce all the required integrity constraints.
My design seems
organized and easy to understand, but queries over the network are
torturously slow.

What are you trying to query? The universe? Have you tried
subreports?

--
Mike Sherrill
Information Management Systems