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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |