Difficult Relationship!

[george]

Hi again,

I have a main table, Policies, which contains general
information pertaining to all policies regardless of their
type (i.e. LifePolicies, MotorPolicies, HouseholdPolicies)
and also three other tables, Life, Motor, Household which
contain *additional* specific information about the
policies belonging to a particular type.

The database schema can be found at:

http://www.databaseanswers.org/data_models/insurance_broker
s/index.htm

Because I could not figure out how these four tables are
related among them in the above mentioned schema I posted
a question (see: "unknown symbol in ER diagram", "george",
Feb 21/2005, 2:20 AM) and it was kindly explained to me
that this is a supertype - subtype situation involving a
1:1 relationship between each one of the three subtables
and the main table and also the use of some triggers.

Well, all this I think is above my ability to apply as I
am not experienced in SQL programming but I nonetheless I
really like the idea of having three 1:1 relationships
that are mutually exclusive because of the triggers since
conceptually this is exactly what I need.

Are there any ideas for any workarounds? Myself I was
thinking (as a last resort) to create three 1:M
relationships and then somehow put some code on the forms
of the three subtables which would not allow the user to
add more than one record, but, I don't know, I'm kind of
desparate right now.

ANY IDEAS OR HINTS that you may have on the subject will
be greatly appreciated,

thanks in advance, george

 

[Jeff Boyce]

George

A 1:1 relationship doesn't require any workarounds, and is not a 1:M
relationship.

If you haven't done so already, open the relationships window and add your
four tables.

Drag the primary key from your "supertype" table to the primary key of your
first sub-type table... and this is a good point to remind you that you can
use an Autonumber for the supertype table's key, but must then use a LongInt
data type to store the corresponding key in the sub-type tables.

I'd recommend setting referential integrity on the joins between your
supertype and sub-type tables. At least initially, I don't perceive a
reason for you to check the "Cascading Update/Delete" boxes.

Or have I totally misunderstood what you were asking about ...?

 

[george]

Jeff hi,

thanks for your reply. You see, if I did what you tell me,
in other words if I simply joined each one of the three
tables to my main table with 1:1 relationships then at the
moment I would try to add a record let's say in the Motor
table I would get a message from Access to also add a
record to the Life table as well as to the Household table
and this is not what I want.

I would like to be able to add a record to the Motor table
but not in the other two tables. This can only be done (if
I understood correctly) only throu SQL update triggers in
order to bypass the referential integrity rules of the db.

I will give you an example. Suppose I have two policies
with the following attributes
MOTOR POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. VehicleRegistrationN
6. EngineSize

HOUSEHOLD POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. Address
6. ConstructionYear

To my Policies table I would store attributes 1-4 for both
policies because they are exactly the same and then I
would store attributes 5 and 6 in table Motor for the
Motor Policy and in table Household for the Household
Policy respectively. Going one step further, for the Motor
Policy I don't want to store anything either in Life table
or in Household table but the db won't let me do this with
1:1 relationships. I need to find a constraint so that the
db will let me do this by inactivating the referential
integrity rules. I hope it's more clear now what I mean.

thanks again, george

 

[rpw]

Hi,

For what it's worth, I can't seem to duplicate the problem you are having.
I built four tables to emulate your problem then I created relationships
following Jeff's instructions.

When I create a new Policy, there is not a matching record in the three
sub-tables. However, if I try to add a record in one of the sub-tables,
Access looks for a match in the main Policy table before it will allow the
record to be saved. After the record is added, there is not matching records
in the other sub-tables. My quick sample seems to be working the way you
want.

However, if I create a 1:1 relationship between the sub-tables, then when I
try to add a new record in Motor I get a message stating that there has to be
a matching record in Household. Maybe this is the problem?

 

[Chris2]

Jeff hi,

thanks for your reply. You see, if I did what you tell me,
in other words if I simply joined each one of the three
tables to my main table with 1:1 relationships then at the
moment I would try to add a record let's say in the Motor
table I would get a message from Access to also add a
record to the Life table as well as to the Household table
and this is not what I want.

Jeff,

The Motor, Household, and Life tables are all not linked by a
relationship in the schema. I cannot think of a reason for MS Access
to giving you an error message such as described above unless those
three tables *have* been linked together by relationships (and they
should not be).


DDL:

CREATE TABLE Policies
(policy_id AUTOINCREMENT
,policy_start_date DATETIME
,policy_renewal_date DATETIME
,premium_payable CURRENCY
,other_policy_details TEXT(255)
,CONSTRAINT pk_Policies PRIMARY KEY (policy_id)
)

CREATE TABLE Life
(life_id AUTOINCREMENT
,policy_id LONG NOT NULL
,occupation_code TEXT(255)
,life_expectancy INTEGER
,CONSTRAINT pk_Life PRIMARY KEY (life_id)
,CONSTRAINT fk_Life_Policies
FOREIGN KEY (policy_id)
REFERENCES Policies (policy_id)
)

CREATE TABLE Motor
(motor_id AUTOINCREMENT
,policy_id LONG NOT NULL
,vehicle_details TEXT(255)
,CONSTRAINT pk_Motor PRIMARY KEY (motor_id)
,CONSTRAINT fk_Motor_Policies
FOREIGN KEY (policy_id)
REFERENCES Policies (policy_id)
)

CREATE TABLE Household
(household_id AUTOINCREMENT
,policy_id LONG NOT NULL
,age_of_property INTEGER
,CONSTRAINT pk_Household PRIMARY KEY (household_id)
,CONSTRAINT fk_Household_Policies
FOREIGN KEY (policy_id)
REFERENCES Policies (policy_id)
)

I would like to be able to add a record to the Motor table
but not in the other two tables. This can only be done (if
I understood correctly) only throu SQL update triggers in
order to bypass the referential integrity rules of the db.

I will give you an example. Suppose I have two policies
with the following attributes
MOTOR POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. VehicleRegistrationN
6. EngineSize

INSERT INTO Policies
(policy_start_date
,policy_renewal_date
,premium_payable
,other_policy_details
)
VALUES
(#03/01/2004#
,#03/01/2005#
,500.10
,"Too Expensive"
)

INSERT INTO Motor
(policy_id
,vehicle_details
)
VALUES
(1
,"Vector W-8 Twin Turbo"
)

(Note: My apologies, but the schema did not include EngineSize or
VehicleRegistrationN attributes, so I didn't design them in.)

No errors are produced by the above INSERT statements.

HOUSEHOLD POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. Address
6. ConstructionYear

INSERT INTO Policies
(policy_start_date
,policy_renewal_date
,premium_payable
,other_policy_details
)
VALUES
(#05/01/2004#
,#05/01/2005#
,600.25
,"Highway Robbery"
)

INSERT INTO Household
(policy_id
,age_of_property
)
VALUES
(2
,50
)

(Note: My apologies, but the schema did not include Addres or
ConstructionYear attributes, so I didn't design them in.)

No errors are produced by the above INSERT statements.

I need to find a constraint so that the
db will let me do this by inactivating the referential
integrity rules.

Referential Integrity exists to prevent disastrous errors from
entering the database. De-activating RI could allow for the database
to enter a state of incosistency. It could be solved, if you can find
the error. Or the database engine could go into fits over it. Or
file corruption could occur. "Inconsistent Database" is a phrase that
makes jaded DB Admins shudder.

Basically, you shouldn't be looking for a way to deactivate RI in
order to run ordinary INSERT statements.

As my examples above show, it isn't necessary.


Sincerely,

Chris O.

 

[Jack MacDonald]

I would add a field to the main table entitled "PolicyTypeID" with
values of 1, 2, 3 for the various types of policies.

Then I would make a form for the main policy table. Include all the
standard field plus a combo box for the policy type. Then I would make
a separate subform for each policy type, including just its particular
fields. Then add the subforms to the main form, and use the
Master/Child linking fields as appropriate. This feature causes Access
to populate the sub-table with the correct PolicyID value from the
main table.

Finally, I would add code that uses the value of the PolicyTypeID to
either make two of the three subforms invisible, OR use a single
subform object and change its source object as appropriate.

Jeff hi,

thanks for your reply. You see, if I did what you tell me,
in other words if I simply joined each one of the three
tables to my main table with 1:1 relationships then at the
moment I would try to add a record let's say in the Motor
table I would get a message from Access to also add a
record to the Life table as well as to the Household table
and this is not what I want.

I would like to be able to add a record to the Motor table
but not in the other two tables. This can only be done (if
I understood correctly) only throu SQL update triggers in
order to bypass the referential integrity rules of the db.

I will give you an example. Suppose I have two policies
with the following attributes
MOTOR POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. VehicleRegistrationN
6. EngineSize

HOUSEHOLD POLICY
1. PolicyN
2. StartingDate
3. ExpiryDate
4. YearlyPremium
5. Address
6. ConstructionYear

To my Policies table I would store attributes 1-4 for both
policies because they are exactly the same and then I
would store attributes 5 and 6 in table Motor for the
Motor Policy and in table Household for the Household
Policy respectively. Going one step further, for the Motor
Policy I don't want to store anything either in Life table
or in Household table but the db won't let me do this with
1:1 relationships. I need to find a constraint so that the
db will let me do this by inactivating the referential
integrity rules. I hope it's more clear now what I mean.

thanks again, george

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security