Relationships
Irene
I'm using Access 97.
How do i specify an optional relationship (0..*) in access
97?
the problem i have now is everytime i enter data using a
form, access stops me from saving coz i don't have a valid
reference in the master table.
need help...
Thanks in advance!
Irene
John Vinson
>Hi,
>
>I'm using Access 97.
>
>How do i specify an optional relationship (0..*) in access
>97?
By creating the relationship but *not* checking the Enforce Relational
Integrity checkbox. If the relationship is optional, then it cannot be
enforced.
>the problem i have now is everytime i enter data using a
>form, access stops me from saving coz i don't have a valid
>reference in the master table.
I strongly suspect that you *should* have the relationship, and fix
your form. Could you explain the nature of the data? What is in the
"many" side table, what is in the "one" side table, and why do you
feel that it is appropriate to enter "orphan" data in the many side
with no corresponding parent record?
John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
Irene
thanks for your reply.
The data that i have now looks as follows:
There's a COMPANY table and it has an attribute named
CONTACT_PERSON. This attribute is a foreign key to the
PERSON table. each company may have one or no contact
person.
how do i link these two tables?
Irene
>.
>
Leslie Isaacs
I am starting to put together an mdb that will - I hope -
pull togerther various little mdbs I've been playing with
for the last year or so. I've been thinking hard about
the various entities and their relationships ... and am
now a bit stuck!
At the heart of the mdb there will be a collection of
People - so:
[tblPeople], with fields
[peo_serial] (key field)
[peo_first_name]
[peo_last_name]
etc, etc.
Many of these People will be grouped together with the
same address (and some other) details (because they work
at the Local Health Authority, for e.g.). So:
[tblOrganisations], with fields
[org_serial] (key field)
[org_address1]
[org_address2]
etc, etc.
.... and [tblPeople] gets a field [peo_org] which is
joined to [org_serial] in [tblOrganisations].
Does this look like the right set-up?
If so, what about referential integrity between [peo_org]
and [org_serial]? Seems like this should be in place, but
the problem is that not all records in [tblPeople] will
have a corresponding record in [tblOrganisations]. Some
peolple will be 'independent' - not part of an
organisation (unless I establish a 'dummy' organisation
called "independent"?). So I store these peoples'
addresses in [tblPeople]? That means I'm storing some
addresses in [tblPeople] and some addresses in
[tblOrganisations]: this feels wrong somehow.
The second issue is that for some 'people' I will want to
store 'extra' information: e.g. some 'people' will be
employees. So I have in mind
[tblEmployees] with fields
[emp_serial] (key field)
[emp_people] (joined to [peo_serial] in [tblPeople]
[emp_payrate]
etc, etc.
Does this look right? At least here I can have
referential integrity between [emp_people] and
[peo_serial] because all records in [tblEmployees] will
have a corresponding entry in [tblPeople].
I have realised that this posting has become a bit long.
Hope someone gets to the end and can confirm I'm on the
right lines - or redirect me appropriately!
Many thanks
Les.
John Vinson
>Hi John,
>
>thanks for your reply.
>
>The data that i have now looks as follows:
>
>There's a COMPANY table and it has an attribute named
>CONTACT_PERSON. This attribute is a foreign key to the
>PERSON table. each company may have one or no contact
>person.
>
>how do i link these two tables?
>
>Irene
I presume that each Company may have zero, one, or more Contacts....
right? And it wouldn't seem to make sense to have a Contact not
affiliated with any company, correct?
If so, you should have some unique identifier as the primary key of
the Company table, let's call it CompanyID. You need a "foreign key"
field in the CONTACT_PERSON table, a field of the same datatype as the
COMPANY primary key (use a Long Integer if this CompanyID is an
Autonumber). Join the two tables on this field. It will be a One
(Company) to Many (Contact) relationship; I'd strongly suggest
checking the enforcing relational integrity checkbox in the
relationships dialog.
Tim Ferguson
news:03ec01c2a76e$b83bc080$89f82ecf@TK2MSFTNGXA01:
> At the heart of the mdb there will be a collection of
> People - so:
>
> [tblPeople], with fields
> [peo_serial] (key field)
> [peo_first_name]
> [peo_last_name]
Looks fine so far.
> Many of these People will be grouped together with the
> same address (and some other) details (because they work
> at the Local Health Authority, for e.g.). So:
>
> [tblOrganisations], with fields
> [org_serial] (key field)
> [org_address1]
> [org_address2]
> etc, etc.
Fine again
> ... and [tblPeople] gets a field [peo_org] which is
> joined to [org_serial] in [tblOrganisations].
>
> Does this look like the right set-up?
No, not really. There are times when people work with several
organisations, either at once, or you may want to capture historical
information, so there's a place for a table BelongsTo to implement the
many-to-many relationship (BTW, the * means a PK field)
BelongsTo
=========
*Person LongInt references tblPeople.peo_serial
*Organisation LongInt references tblOrganisations.org_serial
StartDate Date/Time not required
EndDate Date/Time not required
JobTitle Text(32)
Department Text(32)
The other fields may not be needed in your application, but you get the
picture. The functionality is given by the two-field PK that forces only
one row for each Person for each Organisation.
> If so, what about referential integrity between [peo_org]
> and [org_serial]? Seems like this should be in place,
Agreed. (Bear in mind, this model only allows one organisation per person)
> the problem is that not all records in [tblPeople] will
> have a corresponding record in [tblOrganisations].
Not a problem: leave the FK field null so it doesn't have to point to
anything. RI means that if there's a value, it must be a valid value; if
there's no value then that's ok.
> called "independent"?). So I store these peoples'
> addresses in [tblPeople]? That means I'm storing some
> addresses in [tblPeople] and some addresses in
> [tblOrganisations]: this feels wrong somehow.
I agree. There's two things going on here, and an address is different from
an organisation. I would be tempted to declare two different things: Jo's
address is "21 St Anne Crescent...", while Jo's organisational address is
"St Mary's Hospital...". Some people will only have their own address, some
will have only an organisation(s). If the semantics fit with real life, you
can model it any way _you_ see it.
> The second issue is that for some 'people' I will want to
> store 'extra' information: e.g. some 'people' will be
> employees. So I have in mind
>
> [tblEmployees] with fields
> [emp_serial] (key field)
> [emp_people] (joined to [peo_serial] in [tblPeople]
> [emp_payrate]
> etc, etc.
This is a method called subclassing and is a standard practise. You don't
need the emp_serial field, but use the emp_people field as the PK. That
ensures only one employee record per person in a one-to-one relationship.
> I have realised that this posting has become a bit long.
Not a problem: specific questions and very clearly explained. If only
everybody..... :-)
Hope that helps
Tim F
Leslie Isaacs
Thanks for your reply.
The only question I still have is on the issue of the
addresses. It is pretty certain that for at least 95% of
the 'people' in [tblPeople] only one address (at a time -
I take your point there!) will be recorded: either
a 'organisation' address, or an 'independent' address. I
understand that I could establish the concept
of 'private' and 'organisation' adresses, but given the
above won't this just mean an awful lot of redundant
fields? But how to avoid them?
I would be very grateful for your continued advice.
Thanks
Les.
>.
>
Tim Ferguson
> The only question I still have is on the issue of the
> addresses. It is pretty certain that for at least 95% of
> the 'people' in [tblPeople] only one address (at a time -
> I take your point there!) will be recorded: either
> a 'organisation' address, or an 'independent' address. I
> understand that I could establish the concept
> of 'private' and 'organisation' adresses, but given the
> above won't this just mean an awful lot of redundant
> fields? But how to avoid them?
>
This is where the science of R theory merges into the art of DB design! The
"correct" answer depends on the nature of your data and the uses that _you_
are going to put it to. Any advice you get from these groups -- and that
most definitely includes me!! -- must be taken in the light of _your_
situation. OK, health warning over!
Entities. It all comes down to entities.
If home addresses are different from organisation ones, then you can quite
simply store them different ways -- for example, the way I suggested in the
previous post. They'd be different, for example, if you only wanted scan
the home addresses for your Christmas card list, or if org addresses have
fax numbers but people have mobiles. Or whatever.
On the other hand, it might be more convenient to consider all addresses
("locations"?) as the same and store them in a single table. Then you can
say, "whom do I know in Ambridge", or, "Whose phone number is this?"
regardless of whether it's an org or a person.
Finally, you might care to rethink your "central" entity. What about
starting with a Location, and then storing "This is the location of...",
being Roger, or Local Health Authority or whatever. And should phone
numbers (i.e. extension numbers) be held in the "joint" table BelongsTo.
As you can see, it can look quite complicated but it's really a question of
chopping things up until you get a real-life "thing".
One final thought: there are lots of ready-built contact managers on the
market. If this is a learning exercise then it's quite a hard one to start
on; if you need a business system to get up and running, then buying one
might actually be cheaper.
All the best
Tim F