Good Design?

John,

Why wouldn't a many-to-many work?

Location table
LocationID (PK)
LocationName

Contact table
ContactID (PK)
ContactFirstName
ContactLastName

LocationAddress table
LocationID (FK) (Complex PK)
AddressID (FK) (Complex PK)
AddressTypeID (FK)

ContactAddress table
ContactID (FK) (Complex PK)
AddressID (FK) (Complex PK)
AddressTypeID (FK)

Address table
AddressID (PK)
AddressStreet
AddressCity
AddressState
AddressZip

Or is this just getting way too complicated and I'm setting myself up for
headaches?

Thanks!

John W. Vinson wrote:
I believe that simplifies structure but at the same time it seems to
contradict the resources I've been reading on normalization. Since this is my
first application I would rather err on the side of simplicity

In this particular case I would (somewhat uncomfortably) say that the side of
simplicity would have two address tables. If Locations and Contacts are two
different types of entities, and each of these entities has (independently)
zero, one or many addresses, then there are "Location Addresses" and "Contact
Addresses". You can't really have a foreign key in a table which OPTIONALLY
points to one table or the other.

At the very least, you'll need the address table to have TWO foreign keys, a
ContactID and LocationID; they'll need to be nullable (so that the ContactID
will be NULL for a "location address" and vice versa. I don't like nullable
foreign keys in principle but it might be necessary here (the problem is that
you could have an address record with NULL in *both* foreign keys, a true
orphan address).

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200904/1

TraciAnn,

Actually, this set-up just might work as it does eliminate two separate
Address tables, however, it keeps Locations and Contacts in separate tables
where they should be. It also prevents empty or NULL FK's which make us all
nervous. You could also potentially set up phone numbers the same way.
(Waiting to hear if John likes this way, I will defer to his opinion!)

As for the books and documentation on normalization and relational
databases, you should note that SOMETIMES one has to veer off the beaten
path. Not that this should be the norm but it does happen.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:9441ef39a0730@uwe...
John,

Why wouldn't a many-to-many work?

Location table
LocationID (PK)
LocationName

Contact table
ContactID (PK)
ContactFirstName
ContactLastName

LocationAddress table
LocationID (FK) (Complex PK)
AddressID (FK) (Complex PK)
AddressTypeID (FK)

ContactAddress table
ContactID (FK) (Complex PK)
AddressID (FK) (Complex PK)
AddressTypeID (FK)

Address table
AddressID (PK)
AddressStreet
AddressCity
AddressState
AddressZip

Or is this just getting way too complicated and I'm setting myself up for
headaches?

Thanks!

John W. Vinson wrote:
I believe that simplifies structure but at the same time it seems to
contradict the resources I've been reading on normalization. Since this
is my
first application I would rather err on the side of simplicity

In this particular case I would (somewhat uncomfortably) say that the side
of
simplicity would have two address tables. If Locations and Contacts are
two
different types of entities, and each of these entities has
(independently)
zero, one or many addresses, then there are "Location Addresses" and
"Contact
Addresses". You can't really have a foreign key in a table which
OPTIONALLY
points to one table or the other.

At the very least, you'll need the address table to have TWO foreign keys,
a
ContactID and LocationID; they'll need to be nullable (so that the
ContactID
will be NULL for a "location address" and vice versa. I don't like
nullable
foreign keys in principle but it might be necessary here (the problem is
that
you could have an address record with NULL in *both* foreign keys, a true
orphan address).

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200904/1

Gina,

Yes. This was my original setup for both Address and Phone; I may not have
documented it clearly. However, from one of your first responses, it seemed a
little overkill to have the many-to-many for just a single field. So, I
"veered off the beaten path" in favor of simplicity. On the other hand, my
goal is to understand and implement "best practice" and as long as I don't
over-complicate things with structures like this (using many-to-many
relationships where First Normal Form will do) I learn best by doing. So, as
long as I don't frustrate my support group winkwink I am all for "baptism
by fire".

Thank you!!!

Gina Whipp wrote:
TraciAnn,

Actually, this set-up just might work as it does eliminate two separate
Address tables, however, it keeps Locations and Contacts in separate tables
where they should be. It also prevents empty or NULL FK's which make us all
nervous. You could also potentially set up phone numbers the same way.
(Waiting to hear if John likes this way, I will defer to his opinion!)

As for the books and documentation on normalization and relational
databases, you should note that SOMETIMES one has to veer off the beaten
path. Not that this should be the norm but it does happen.

John,

[quoted text clipped - 58 lines]
you could have an address record with NULL in *both* foreign keys, a true
orphan address).

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200904/1

On Tue, 07 Apr 2009 17:17:32 GMT, "TraciAnn via AccessMonster.com"
u50702@uwe wrote:

Why wouldn't a many-to-many work?


I guess it would! You still have two more-or-less identical tables
(LocationAddress and ContactAddress) but it allows for multiple contacts, or
multiple locations, or a mix to share the same address (probably desirable),
and for a contact to have multiple addresses (likewise). I'd say go for it!
--

John W. Vinson [MVP]

TraciAnn,

As I said "best practice" is what works which does not ALWAYS follow the
rule. I do not get fustrated... only a little more crazy! As I said go for
it and post back if you run into any issues.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"TraciAnn via AccessMonster.com" u50702@uwe wrote in message
news:94433d16a03a1@uwe...
Gina,

Yes. This was my original setup for both Address and Phone; I may not have
documented it clearly. However, from one of your first responses, it
seemed a
little overkill to have the many-to-many for just a single field. So, I
"veered off the beaten path" in favor of simplicity. On the other hand, my
goal is to understand and implement "best practice" and as long as I don't
over-complicate things with structures like this (using many-to-many
relationships where First Normal Form will do) I learn best by doing. So,
as
long as I don't frustrate my support group winkwink I am all for
"baptism
by fire".

Thank you!!!

Gina Whipp wrote:
TraciAnn,

Actually, this set-up just might work as it does eliminate two separate
Address tables, however, it keeps Locations and Contacts in separate
tables
where they should be. It also prevents empty or NULL FK's which make us
all
nervous. You could also potentially set up phone numbers the same way.
(Waiting to hear if John likes this way, I will defer to his opinion!)

As for the books and documentation on normalization and relational
databases, you should note that SOMETIMES one has to veer off the beaten
path. Not that this should be the norm but it does happen.

John,

[quoted text clipped - 58 lines]
you could have an address record with NULL in *both* foreign keys, a
true
orphan address).

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200904/1

Whenever I've used Location tables, it usually turns out that a specific
person at that location may need customized versions of the address. For
example, some locations are so large that the location has more than one
postal code. Users may have a mail stop, a room number or a department.

I have not found completely satisfactory solutions. They are generally more
complicated than "feels" right. Having a separate Address table with a
many-many relationship with Persons brings the difficulty of finding an
existing address when you might want to reuse it, since the same address can
be formatted somewhat differently by different people.

One option that has worked ok, albeit with extra complication, is to have an
Address table with all the "normal" address fields plus an optional
locationID FK from a Location table. You could either have an address belong
to a Person, as I usually do it, or have the PersonAddressUsage table to
allow multiple people at the same address. In either case, queries combine
the Location address fields with the Address address fields, with Location
providing the default values and any data in the Address over-riding those
defaults. This allows for as much customization of an Address as desired,
while also allowing the shared Location address info. You have to think
about whether the Address.street should be combined with Location.street, or
generate an extra line in the final address calculation. Probably the best
is two street fields in Address, one which would be inserted before the
Location street and one which could replace the Location street.

If appropriate, Location can be connected to an Organization table if the
shared Location should belong to an Organization. Organization and Person
can be combined as subcategories of a more general Party table, offering
some simplification since both Organizations and Persons have addresses,
phones, etc.

I've seen designs for addresses built out of hierarchical
GeographicLocations. The top level might be Country, then Region, City,
Street, Building, etc. It can be done with separate tables for each
GeographicLocationType, or a single GeographicLocation table. This seems to
offer the most rigorous representation, but has always seemed too
complicated for me to choose as an implementation.

You can get additional ideas by looking at how postal address
standardization programs represent addresses. They typically use 20-30
fields for a single address, trying to achieve a single way to represent any
particular address. So a street address like 120 North Center Street would
be in multiple fields:
StreetNumber: 120
StreetPreOrdinal: North
StreetName: Center
StreetPostOrdinal: null
StreetLabel: Street

"John W. Vinson" wrote in message
news
On Tue, 07 Apr 2009 17:17:32 GMT, "TraciAnn via AccessMonster.com"
u50702@uwe wrote:

Why wouldn't a many-to-many work?


I guess it would! You still have two more-or-less identical tables
(LocationAddress and ContactAddress) but it allows for multiple contacts,
or
multiple locations, or a mix to share the same address (probably
desirable),
and for a contact to have multiple addresses (likewise). I'd say go for
it!
--

John W. Vinson [MVP]

Clif,

Do you have time today to help me over a hurdle?

I am fighting against the clock to figure out the record creation concept for
the "Call Call Detail" scenario. More specifically, during UI when you
create child records, how do you force those records to take on the ID (PK)
of the parent record without manually entering it?

I HAVE to have a working form TODAY and my studies are moving slower than I
can afford. Once I understand this process though, it will help me with the
majority of my current challenges.

If you are available, I sure would appreciate it.

Thanks!

Clif McIrvin wrote:
TraciAnn -- It's pretty evident that you are working hard and doing your
research ... you'll do well!

Enjoy the process g

--
Clif

Yes. I LOVE THESE GUYS!!! (You TOO)! Everyone has been so wonderful
and it
[quoted text clipped - 39 lines]
Interface
design which you can find by browsing his site.


--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200904/1

On Thu, 09 Apr 2009 15:18:00 GMT, "TraciAnn via AccessMonster.com"
u50702@uwe wrote:

I am fighting against the clock to figure out the record creation concept for
the "Call Call Detail" scenario. More specifically, during UI when you
create child records, how do you force those records to take on the ID (PK)
of the parent record without manually entering it?

If the Call Detail records are being entered on a subform, you would have the
CallID as the Master and Child Link Fields. The Subform *takes care of that
for you*. When a record is created on the subform, the value in the Master
Link Field fills in automatically into the child link field.
--

John W. Vinson [MVP]

In addition to what John said about Master / Child Link fields when you
have the one to many relationship between the two tables (these are
related tables, right?) set to enforce referential integrity the linking
is automatic.

--
Clif

"John W. Vinson" wrote in message
...
On Thu, 09 Apr 2009 15:18:00 GMT, "TraciAnn via AccessMonster.com"
u50702@uwe wrote:

I am fighting against the clock to figure out the record creation
concept for
the "Call Call Detail" scenario. More specifically, during UI when
you
create child records, how do you force those records to take on the ID
(PK)
of the parent record without manually entering it?

If the Call Detail records are being entered on a subform, you would
have the
CallID as the Master and Child Link Fields. The Subform *takes care of
that
for you*. When a record is created on the subform, the value in the
Master
Link Field fills in automatically into the child link field.
--

John W. Vinson [MVP]



--
Clif

Is it REALLY THAT EASY?!!!

I LOVE ACCESS!!!!

Thanks John!

John W. Vinson wrote:
I am fighting against the clock to figure out the record creation concept for
the "Call Call Detail" scenario. More specifically, during UI when you
create child records, how do you force those records to take on the ID (PK)
of the parent record without manually entering it?

If the Call Detail records are being entered on a subform, you would have the
CallID as the Master and Child Link Fields. The Subform *takes care of that
for you*. When a record is created on the subform, the value in the Master
Link Field fills in automatically into the child link field.

--
---
TraciAnn

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200904/1