Data in table or one to one relationship

[Mattymoo]

I have database with a fairly simple structure:

Client details
Claims (one-many with client)
Pending (one to many with claim)

For each claim I need to record an outcome and payment details etc (about 10
more fields). It already has 21 relative fields. Should I add the Outcome
etc to the exisitng table or set up a new one on a one to one relationship.
It doesn't need to be in a separate table for security purposes, but are
there any advanteges or disadvantages of doing it one way or the other?

Thanks

Pauline

 

[tina]

my thoughts would be to: include the Outcome field in the Claims table -
presumably each claim will ultimately have an outcome; set up a separate
Payments table with either a one-to-one or one-to-many relationship with the
Claims table - possibly not all claims will result in payments (some claims
are denied, right?), and possibly some claims may result in multiple
payments before they are ultimately closed.

hth

 

[Larry Daugherty]

Hi Pauline,

First, a question: why do you need table Pending? In the hopes of saving
you some work and design effort and redundant data entry ... It would seem
that Pending should be a single status bit in the record in tblClaim:
Pending Yes/No checkbox.

If I'm inferring correctly what you're doing, I'd make tblOutcome a simple
table for looking up the outcome (Who knows, maybe tblOutcome becomes
tblStatus and Pending becomes a member of that table)? In that case, you
might have a combobox on your Claim form that will find your desired
status/outcome of the moment in tblStatus/tblOutcome.

You've mentioned the other reasons why Outcomes might be a separate table.
It is best to keep a complete record in just one table. Saves various kinds
of maintenance headaches over the years.

HTH

 

[Mattymoo]

Larry

The reason I have a separate pending table is that each claim can have
multiple pending reasons at any one time (eg waiting for info from client and
wating for info from third party simultaneously), so based on that have I
structured that part correctly?

Tina, yes some claims are agreed, some are denied and some we refuse to
progress, so it makes sense to record the outcome in the claims table and the
payment details separately. we wouldn't get into a situation where there are
multiple payments on one claim, so a one to one relationship sounds logical.

 

[tina]

then i would say that your table setup described in your first post is
correct, with the addition of an Outcome field in the Claims table and an
additional table for Payments with a one to one relationship with the Claims
table. in a one-to-one table setup, the primary key field of your "parent"
table (Claims) will also be used as the primary key field of the "child"
table (Payments).

generally speaking: you don't determine what fields to put in what tables
based on "number of fields" in a table. a table is about a "person, place,
thing, or event", and the fields you include in the table are specific bits
of data that describe that subject. i can think of three "usual" reasons to
split off a field or fields into child tables - 1) because there may be
multiple instances of that data for one record, as in multiple concurrent
pending reasons for one claim, or 2) because certain data needs additional
protection that does not apply to the rest of the data, for example a
customer's credit card number, or 3) because certain data will not be
present in a significant number of records, such as some claims will have no
payment information.

hth

 

[Larry Daugherty]

Hi Pauline,

You've got it exactly right.