How to setup one-to-one relationships

[PSI]

Hello

I'm trying to setup the following tables:

1) Business:
ID - Autonumber primary Key
Name - text field
MainAddressID - Number field with required=Yes
ShipAddressID - Number field with required=No
ShipSameAsMain - Yes/No field

2) Address:

ID - Autonumber primary Key
Street1 - text
Street2 - text
City - text
StateID - number ( index into State table )

where the Business.MainAddress and Business.ShipAddress are one_to_one
on Address.ID with referential integrity enabled and cascade delete
(i.e. if delete business record the corresponding address records are
deleted). One twist is that the ShipAddressID is not required if the
ShipSameAsMain is Yes.

I've been unable to make this work properly - mainly get errors trying
to populate the tables in DataSheet view namely "can't add or change a
record becaue a related record is required in table ..".

Is this a reasonable way to handle the address's? or is the
convetional wisdom to just embed the fields into the Business table (
ie MainStreet1, ShipStreet1, MainStreet2, ShipStreet2...)

Any guidance on how to approach this would be much appreciated.

Thanks
Frank

 

[Steve Schapel]

Frank,

IMHO the idea you are using is not the best approach, unless there is
a reason I haver missed for having these AddressID fields in the
Business table.

I would regard it like this... There is a one-to-many relationship
between Business and Address. You need to have the BusinessID in the
Address table, plus a field which identified the address type. I
would do it like this...

1) Business:
BusinessID - Autonumber primary Key
Name - text field

2) Address:
AddressID - Autonumber primary Key
BusinessID - number - FK to Business table
AddressType - number: 1=main, 2=ship
Address - text
StateID - number (index into State table)

From the point of view of your queries, reporting, etc, if you need
the Business's main address, you pull out the one with AddressType=1.
If you need the Shipping address, you pull out the one with
AddressType=2 if there is one, otherwise use the AddressType=1.

- Steve Schapel, Microsoft Access MVP

 

[PSI]

Steve,

Some additional questions are interspersed.

Thanks very much for the assistance.

Frank

Frank,

IMHO the idea you are using is not the best approach, unless there is
a reason I haver missed for having these AddressID fields in the
Business table.

The reason for the approach was ".. just learnin ". I thought the
AddressID fields were necessary to make use of referential integrity
to delete the address records when the Business record was deleted.

Q:With your suggested approach do I need to do two deletes ( ie
delete address records with BusinessID of nnn and then if successful
delete the business record)?

I would regard it like this... There is a one-to-many relationship
between Business and Address. You need to have the BusinessID in the
Address table, plus a field which identified the address type. I
would do it like this...

1) Business:
BusinessID - Autonumber primary Key
Name - text field

2) Address:
AddressID - Autonumber primary Key
BusinessID - number - FK to Business table
AddressType - number: 1=main, 2=ship
Address - text
StateID - number (index into State table)

From the point of view of your queries, reporting, etc, if you need
the Business's main address, you pull out the one with AddressType=1.
If you need the Shipping address, you pull out the one with
AddressType=2 if there is one, otherwise use the AddressType=1.

Thanks - This makes more sense than duplicating all the address info
in the business record.

Q: I have a form with Main and Shipping fields. Can I do a query to
get both address's or do I need to do separate queries for each
address ( taking into account that the shipping address is optional)?

It might get more complicated because once I work out the tables I
will be doing the queries and forms from C++/ADO ( not .net ) using
Bind to map the recordset to C++ class's).

 

[Steve Schapel]

Frank,


....

Q:With your suggested approach do I need to do two deletes ( ie
delete address records with BusinessID of nnn and then if successful
delete the business record)?

If you set up referential integrity with cascade deletes between the
two tables, then deleting a Business will automatically delete all
associated addresses.

....

Q: I have a form with Main and Shipping fields. Can I do a query to
get both address's or do I need to do separate queries for each
address ( taking into account that the shipping address is optional)?

It might get more complicated because once I work out the tables I
will be doing the queries and forms from C++/ADO ( not .net ) using
Bind to map the recordset to C++ class's).

I don't know anything about C++ or the type of methodology you are
proposing. The basic concept, as far as Access is concerned however,
is to have a form based on the Business table, another form, in
continuous view, based on the Address table, and then put the Address
form on the Business form as a Subform. The subform's LinkChildFields
and LinkMasterFields properties would be set to BusinessID, which
would ensure the address(es) for the current Business record are the
one(s) that are shown/entered. The subform, based on my suggested
structure, would only have the address fields and the addresstype, for
which you could (and I would) use an Option Group. To enforce your
business rules, you would need to use some code on the BeforeUpdate
event of the Address form, for example throw up a messagebox if there
is an attempt to enter a an address designated as a shipping address
if there is not already a main address for that company.

- Steve Schapel, Microsoft Access MVP

 

[PSI]

On Thu, 04 Sep 2003 07:50:53 +1200, Steve Schapel

Steve,

I'll give your approach a try and see where it leads me.

Thanks again for the help
Frank

 

[PSI]

Hello Steve:

I've setup the tables as you suggested. I tried to extend the concept
to add a third table:

3) Employee
ID - Autonumber primary key
Name - Text

which will also use the Address table but am not sure how to proceed
with setting up the relationships-(in this case the Employee has 1 to
1 relationship to the Address).Adding an EmployeeID field to the
address table in addition to the BusinessID and extending the type
field to reflect Business,Shipping, Employee etc resulted in the same
error originally encountered.

Essentially I have multiple objects ( Business, Employees, Vendors etc
) all of which have one or sometimes multiple addresss. I thought
using a separate address table would be the way to go ( probably
because in C++ code I would setup address as a class object) but I'm
no longer sure this is the way to go.

Any further thoughts would be appreciated.

Frank

 

[Steve Schapel]

Frank,

I am just out the door to a meeting right now... I'll give a fuller
response later (if no-one else does . But in a nutshell, one way
to handle this is to put another field in the Address table to flag
whether the address is a company address, employee address, vendor
address, etc.

- Steve Schapel, Microsoft Access MVP

 

[PSI]

Steve,

I understand using the flags but I'm hung up on setting the
relationships so that deleting/updating a business ( or
Employee,Vendor etc ) record will cascade to the appropriate action on
the address table.

It seems that I need multiple FK's in the address table but I haven't
been able to make this work ( due to the original "can't add or change
a record becaue a related record is required in table .." error). Also
it seems cumbersome to have to add a new FK for every object
introduced that uses an address ( but I'm new to this so maybe that's
just the way it is ). The alternative, using flags and a single FK
field linked to each primary table, doesn't work ( intuitively, I
don't see how it could but perhaps I'm doing it wrong).

In any event, I look forward to your "fuller response" whenever you
have the time.

Thanks
Frank

 

[Steve Schapel]

Frank,

This is a good lesson in not trying to define a solution without
knowing the whole picture!

And now I'm still not sure, because I don't know how different from
each other the data required for Business, Employee, Vendor, etc. But
here's how it's starting to look to me...

Table: Entity
EntityID
EntityName
EntityType (i.e. Business, Vendor, Employee, etc)
other fields relevant to all or most entities

Table: Address
AddressID
EntityID
AddressType
Address
StateID

and if applicable, sub-typing tables such as...
Table: Businesses
EntityID
fields only applicable to businesses and not to other entity types

- Steve Schapel, Microsoft Access MVP

 

[PSI]

Steve:

My apologies for the delay in the discussion - had to leave town for
work.

Other than Name, the only commanality between entities is the Address
block (at the moment - it will obviously change as soon as its
implemented ). Contact information is another block (
Phone,fax,cel,email ) is another block which I had thought to try and
support in the same way as Address, once I had Address figured out.

I will try setting up some entities and let you know. It's starting to
look complicated from a C++ implementation perspective but that part
of it I can manage.

Thanks
Frank