A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Difficult Relationship!



 
 
Thread Tools Display Modes
  #1  
Old February 25th, 2005, 12:06 PM
george
external usenet poster
 
Posts: n/a
Default Difficult Relationship!

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_...surance_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
  #2  
Old February 25th, 2005, 01:09 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

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 ...?

--
Good luck

Jeff Boyce
Access MVP

"george" wrote in message
...
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_...surance_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


  #3  
Old February 25th, 2005, 04:32 PM
george
external usenet poster
 
Posts: n/a
Default

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


-----Original Message-----
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 ...?

--
Good luck

Jeff Boyce
Access MVP

"george" wrote in

message
...
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_...surance_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


.

  #4  
Old February 25th, 2005, 07:59 PM
rpw
external usenet poster
 
Posts: n/a
Default

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?

"george" wrote:

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


-----Original Message-----
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 ...?

--
Good luck

Jeff Boyce
Access MVP

"george" wrote in

message
...
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_...surance_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


.


  #5  
Old February 25th, 2005, 08:40 PM
Chris2
external usenet poster
 
Posts: n/a
Default


"george" wrote in message
...
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.


  #6  
Old February 26th, 2005, 08:34 PM
Jack MacDonald
external usenet poster
 
Posts: n/a
Default

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.


On Fri, 25 Feb 2005 07:32:25 -0800, "george"
wrote:

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


-----Original Message-----
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 ...?

--
Good luck

Jeff Boyce
Access MVP

"george" wrote in

message
...
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_...surance_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


.



**********************

remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
  #7  
Old February 28th, 2005, 09:35 AM
Jamie Collins
external usenet poster
 
Posts: n/a
Default

Chris2 wrote:

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.


Referential Integrity exists to prevent disastrous errors from
entering the database.


Chris2, Your schema's DRI fails to enforce the OP's business rule about
adding a referencing row to only one of the subclass tables. For
example, your schema does not prevent the following:

INSERT INTO Policies
(policy_start_date ,policy_renewal_date
,premium_payable ,other_policy_details)
VALUES
(#03/01/2006# ,#03/01/1954#
,500.10 ,"Too Expensive")
;
INSERT INTO Motor (policy_id ,vehicle_details )
VALUES
(1 ,"Vector W-8 Twin Turbo" )
;
INSERT INTO Household (policy_id ,age_of_property )
VALUES (1 ,50)
;
INSERT INTO Household (policy_id ,age_of_property )
VALUES (1 ,30)
;
INSERT INTO Household (policy_id ,age_of_property )
VALUES (1 ,-20)
;
....


Consider this alternative DDL:

CREATE TABLE Policies (
PolicyN CHAR(9) NOT NULL UNIQUE,
CHECK (LEN(PolicyN) = 9),
policy_type VARCHAR(10) NOT NULL,
CHECK (policy_type IN ('Household', 'Motor', 'Life')),
StartingDate DATETIME DEFAULT DATE() NOT NULL,
ExpiryDate DATETIME,
CHECK (StartingDate = ExpiryDate),
YearlyPremium CURRENCY DEFAULT 0 NOT NULL,
CONSTRAINT pk__policies PRIMARY KEY (PolicyN, policy_type)
)
;
CREATE TABLE HouseholdPolicies (
PolicyN CHAR(9) NOT NULL,
policy_type VARCHAR(10) DEFAULT 'Household' NOT NULL,
CHECK (policy_type = 'Household'),
Address MEMO NOT NULL,
ConstructionYear INTEGER NOT NULL,
CHECK (ConstructionYear BETWEEN 800 AND (YEAR(DATE())+1)),
CONSTRAINT pk__householdpolicies PRIMARY KEY (PolicyN, policy_type),
CONSTRAINT fk__householdpolicies FOREIGN KEY (PolicyN, policy_type)
REFERENCES Policies (PolicyN, policy_type)
ON UPDATE CASCADE ON DELETE CASCADE
)
;
CREATE TABLE MotorPolicies (
PolicyN CHAR(9) NOT NULL,
policy_type VARCHAR(10) DEFAULT 'Motor' NOT NULL,
CHECK (policy_type = 'Motor'),
VehicleRegistrationN CHAR(17) NOT NULL,
CHECK (LEN(VehicleRegistrationN) = 17),
EngineSize INTEGER NOT NULL,
CHECK (EngineSize BETWEEN 1 AND 100000),
CONSTRAINT pk__motorpolicies PRIMARY KEY (PolicyN, policy_type),
CONSTRAINT fk__motorholdpolicies FOREIGN KEY (PolicyN, policy_type)
REFERENCES Policies (PolicyN, policy_type)
ON UPDATE CASCADE ON DELETE CASCADE
)
;

Jamie.

--

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting a foreign key relationship in SQL Stevio Running & Setting Up Queries 2 December 22nd, 2004 03:51 PM
Re-establishing a broken relationship David McKnight Database Design 2 December 1st, 2004 11:49 AM
PK not required to create FK 'relationship' Jamie Collins New Users 3 October 28th, 2004 09:13 AM
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. HDW Database Design 3 October 16th, 2004 03:42 AM
Setting dual relationship with tool connector Carlos Visio 0 May 20th, 2004 12:51 AM


All times are GMT +1. The time now is 04:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.