A Temporary Table or One Long Array

[Joy M]

Hi -

I am doing a database that keeps track of insurance policies for an
insurance agent.

The Invoice table (parent) contains the total premium (for a policy), the
Invoice Company table (child) contains premiums which add up to the total
premium.

For example, in the Invoice table, Car Premium = $500.
In the Invoice Company table
1st record - company is State Farm, premium is $200
2nd record - company is ING, premium is $300

The operator is entering this information onto a form and subform.
We do not want to save the records in the subform if the premium amounts do
not add up to the total premium, because we don't want the database to be
out of balance, ever.

One way we thought of doing this was to extend the Invoice record with say
10 multiples of the 5 fields in the Invoice Company table, in the unlikely
event that the total premium was spread over 10 companies. Most of the time
it is one company, some times it is 2 or 3 companies. This way when the
Invoice record is written, we know it is in balance, but this method wastes
space.

Another choice would be to store the information on the form into a
temporary table and when everything added up properly, then write the
records to the Invoice Company table. In both of these ways, we do not save
information that is not in balance.

So can you give me some advice on how to go?

And if temporary tables are to be used, then - briefly - how do I do it? Do
you know of any examples I can look at?

Thanks so much!

Joy

 

[tina]

comments inline.

Hi -

I am doing a database that keeps track of insurance policies for an
insurance agent.

The Invoice table (parent) contains the total premium (for a policy), the
Invoice Company table (child) contains premiums which add up to the total
premium.

For example, in the Invoice table, Car Premium = $500.
In the Invoice Company table
1st record - company is State Farm, premium is $200
2nd record - company is ING, premium is $300

The operator is entering this information onto a form and subform.

where is the operator getting the total premium amount? from paper media?
and likewise the company premiums? in what circumstances would the company
premiums not add up to the total premium?

We do not want to save the records in the subform if the premium amounts do
not add up to the total premium, because we don't want the database to be
out of balance, ever.

so if the company premiums DON'T add up to the total premium, you don't want
to save the company premium records? how will that keep your database "in
balance"? if you write a query to compare the total dollar amount in the
Invoices table with the total dollar amount in the InvoiceCompany table, the
two tables will be MORE out of balance, not less. the solution is to either
not save *any* invoice company records - or, save them all and resolve
inconsistencies in the total-company-premiums / invoice-premiums at the
point of data entry.

One way we thought of doing this was to extend the Invoice record with say
10 multiples of the 5 fields in the Invoice Company table, in the unlikely
event that the total premium was spread over 10 companies. Most of the time
it is one company, some times it is 2 or 3 companies. This way when the
Invoice record is written, we know it is in balance, but this method wastes
space.

the parent/child table setup is correct for your situation. you absolutely
should *not* de-normalize the Invoice table in the way you describe above.
to do so would take the dangerous step of limiting your business process
based on arbitrary physical limitations imposed by the database design. what
happens if/when your business grows/expands/changes, and you have even one
invoice record that needs to reflect eleven companies? your database cannot
support the business process at that point, and you're forced to rely on a
paper tracking system to supplement the database in the short-term, and
rewriting the database for the long term.

Another choice would be to store the information on the form into a
temporary table and when everything added up properly, then write the
records to the Invoice Company table. In both of these ways, we do not save
information that is not in balance.

again, whether you write the Invoice Company data directly to a child table,
or to a temporary table and then to a child table, not saving records that
are "out of balance" will not bring the data into balance - unless you
*also* don't save the total premium record in the parent Invoice table.

So can you give me some advice on how to go?

since you considered moving the child table's fields into the main table,
i'll assume that you need to store the child data as part of the permanent
records. and you imply that putting the data in the parent table would allow
you to balance the total premium with the sum of the company premiums for a
specific record. so i must also assume that you're able to adjust the dollar
amounts in some appropriate manner to make the two totals balance.

if you can do that in a single record, you can do it between parent/child
records. the issue is not your table design, it's the design of the user
interface. you need to design a parent/child form that supports a check of
the parent record's premium against the total of the child records'
premiums. or is your operator simply manually adding the premiums of the
child records, and entering that total manually into the parent record? if
so, you might write code in the form to automatically add all the premiums
entered in the subform and write that total into the main form's "money"
field - then there's no issue of input error, and no "balance" errors.

hth