| TR wrote -- | | We are rewriting our auto insurance system as well as designing a new physical data model. | Best practices suggests that we utilize foreign key and not null constraints on our tables within the database. | However, we have been presented with a challenge. The users want to be able to store partial data. | For example, they may enter the driver's first name and then exit the system. | Or they may enter just a driver's license number and nothing else. | Therefore, in the data model we pretty much have to allow null values for all columns. | Prior to the issuance of the policy, all columns would need to be validated against to verify completeness. | ( For example, first name is required ) This does not appear to be normal to me. | ( the concept of a transaction seems to be moved to just prior to issuance ) | | Here is a solution using a small holding table, plus rules for transfer to the main table. | Policies would only be issued from the main table. | You can run these rules in a browser, using the Internet Business Logic system | at www.reengineeringllc.com in the holding table customer some-ID has some-property with value some-value ------------------------------------------------------------------------------------------ in the holding table there is an entry for customer that-ID for the property that-property the holding table entries for customer some-ID are complete, validated and ready for transfer to the main table in the holding table customer that-ID has first name with value some-first-name in the holding table customer that-ID has last name with value some-last-name in the holding table customer that-ID has age with value some-age in the holding table customer that-ID has license # with value some-license-# in the holding table customer that-ID has address with value some-address add : customer that-ID that-first-name that-last-name aged that-age has license that-license-# and that-address ---------------------------------------------------------------------------------------------------------------- ADD customer that-ID to the main table from which policies can be issued customer some-ID has an entry in the holding table not : in the holding table the customer that-ID is missing a required entry in the holding table customer some-ID has age with value some-value that-value is greater than or equal 18 ---------------------------------------------------------------------------------------------------- the holding table entries for customer that-ID are complete, validated and ready for transfer to the main table customer some-ID has an entry in the holding table we require some-property to have a value in the holding table before a customer can be approved not : in the holding table there is an entry for customer that-ID for the property that-property ------------------------------------------------------------------------------------------------- in the holding table the customer that-ID is missing an entry for the property that-property in the holding table the customer some-ID is missing an entry for the property some-property -------------------------------------------------------------------------------------------- in the holding table the customer that-ID is missing a required entry customer some-ID some-first-name some-last-name aged some-age has license some-license-# and some-address that-ID is greater than 125 del : customer that-ID that-first-name that-last-name aged that-age has license that-license-# and that-address ---------------------------------------------------------------------------------------------------------------- REMOVE the entry for customer that-ID from the main table from which policies can be issued customer this-ID this-first-name this-last-name aged this-age has license this-license-# and this-address ======================================================================================================================================= 101 Barbara James 54 CA-99-76-3164 33 Lalor Dr, San Jose, CA 126 Jane Goodall 45 CT-99-93-299 88 Main Street, Hartford, CT in the holding table customer some-ID has some-property with value some-value ------------------------------------------------------------------------- customer that-ID has an entry in the holding table in the holding table customer this-ID has this-property with value this-value ======================================================================= 124 first name Arthur 124 last name Watkins 124 age 15 124 license # CT-99-93-281 124 address 55 Main Street, Hartford, CT 125 first name Liza 125 age 33 126 first name Jane 126 last name Goodall 126 age 45 126 license # CT-99-93-299 126 address 88 Main Street, Hartford, CT we require this-property to have a value in the holding table before a customer can be approved =============================================================================================== first name last name age license # address