Relationships

  • Thread starter Thread starter Elmar
  • Start date Start date
E

Elmar

In a members data base I have amongst others these tables:

tblPerson
tblHousehold (residence for one or many people)
tblAddress (residential (household) address, postal address (personal
or household))
tblPhone (household landline, personal mobile, workplace)

As a phone may be used by several people and each person may have
access to several phones, there is a many-to-many relationship. To
accommodate that I have an intermediate table tblTelecom with FKs to
tblPhone and tblPerson. This does not, however, take care of the
hosehold's landline. Do I need a second intermediate table
tblHoldTelecom with FKs to tblPhone and tblHousehold? A similar
problem occurs with the addresses.

Thanks in advance for any help

Elmar
 
Elmar said:
In a members data base I have amongst others these tables:

tblPerson
tblHousehold (residence for one or many people)
tblAddress (residential (household) address, postal address (personal
or household))
tblPhone (household landline, personal mobile, workplace)

As a phone may be used by several people and each person may have
access to several phones, there is a many-to-many relationship. To
accommodate that I have an intermediate table tblTelecom with FKs to
tblPhone and tblPerson. This does not, however, take care of the
hosehold's landline. Do I need a second intermediate table
tblHoldTelecom with FKs to tblPhone and tblHousehold? A similar
problem occurs with the addresses.

Thanks in advance for any help

Elmar
Click to expand...


Hi Elmar,

Have you look at Microsoft's "Contacts" templates?
I'm just a rookie but, it does seem like your going through a lot of trouble
here.

This one is a Sales Contact for Access 2003
http://office.microsoft.com/en-us/templates/TC010178391033.aspx?
pid=CT101426031033

Here are the fields they put in just in a single table:


ContactID
FirstName
LastName
Dear
Address
City
StateOrProvince
PostalCode
Region
Country
CompanyName
Title
WorkPhone
WorkExtension
HomePhone
MobilePhone
FaxNumber
EmailName
Birthdate
LastMeetingDate
ContactTypeID
ReferredBy
Notes
MaritalStatus
SpouseName
SpousesInterests
ChildrenNames
Hometown
ContactsInterests

HTH
Richard
 
KenSheridan via AccessMonster.com said:
As for the table from the MS template which the other respondent described, I
haven't checked it out myself, but if a single table really has all those
columns it’s a case of committing first degree spreadsheet, and can't
seriously be regarded as a relational database table at all!

Ken Sheridan
Stafford, England
Click to expand...


It actually has three tables, but the reason I pointed this one out was to
show the poster another option. Nobody needs all theses fields but he sounded
like he could use may be 6-7.

Downloads: 1071777
Rating: 4 out of 5 stars (7175 votes)

Richard
 
Elmar:

It depends on whether you are happy with Null foreign keys or not.  If you
are, then all you need are columns in tblHousehold and tblAddress which
reference the key of tblPhone.  If not then you will need to model the
relationships by tables.  On the assumption that each phone can be the
landline for only one household and/or address the relationship is one-to-
many, so in each of the tables modelling the relationships, HouseholdID and
AddressID would in each case be candidate keys as well as the composite
candidate keys of HouseholdID/PhoneID and AddressID/PhoneID.  My inclination,
if you do adopt this approach, would be to make the composite keys the
primary key in each case, and separately index HouseholdID and AddressID
uniquely.

The above would allow anomalies, however, as  it would be possible for a non-
landline phone to be referenced by either of the HouseHoldLandlines or
AddressLandlines tables, so to protect against this the foreign key in each
which references tblPhone would be better a two-column one of PhoneID and
PhoneType, the latter column having a ValidationRule property which only
allows a value 'Landline" and a DefaultValue property of the same value.
PhoneID (or whatever the key of tblPhone is) and PhoneType is of course a
candidate key of PhoneType, as is any column and the primary key of a table.

As for the table from the MS template which the other respondent described, I
haven't checked it out myself, but if a single table really has all those
columns it’s a case of committing first degree spreadsheet, and can't
seriously be regarded as a relational database table at all!

Ken Sheridan
Stafford, England
Click to expand...

Hello Ken,

it appears you have a solution, unfortunately, I'm not grasping it.
I'll try to represent here what I've got so far (pity we can't use
atachments)
First fields are PK in each table all others (except one) shown here
are FK:

tblAddress Linked 1-1 to tblHousehold (fldAddressID-
fldHouseholdID)
fldAdressID (this 1-1 allows other tables to link to
tblAddress directly
without the need for tblHousehold, for
such things as postal addresses.)
(Maybe there is no need after all for
tbl Household inwhich case)
its fields are to be included into
tblAddress)

tblHousehold Linked 1-1 to tblAddress and 1-M to tblPerson
fldHouseholdID
fldPersonID
fldTelecomID (so far this field does nothing)

tblPerson Linked 1-M from tbl Household (on
fldHouseholdID)
fldPersonID
fldHouseholdID
fldTelecomID

tblTelecom Linked 1-M from tblPerson (on fldPersonID)
fldTelecomID and 1-M from tbl Phone (on fldPhoneID)
fldPhoneID
fldPersonID

tblPhone Linked 1-M to tblTelecom (on fldPhoneID)
fldPhoneID
fldPhoneType (this field is not FK !)
fldTelecomID

How do/does the Household/Address' phone(s) get linked into tblPhone
or tbl Telecom so that it appears to be one of person A's phones and
similarly for any other person residing at Househole/Address?
 
Back
Top