Is a One-to-one relationship appropriate here?

[Cheese_whiz]

Hi all,

I have a database that serves as a file management application for a legal
office. All the primary files are stored in a files table, which includes
file type, when it was opened, category, subcategory, etc. In addition,
there is a 'status' field that has four possibilities:
open/closed/appealed/settled.

The total number of fields in the files table is around 10-12 in terms of
the 'main' fields (I'll explain).

When a case is 'settled', an additional tab appears for input of the
settlement info. Initially, I had placed all settlement info (fields) in the
main files table. Each file will only have one entry for the fields relating
to settlement. However, I'm strongly considering extending the data
collected related to settlements to include a detailed breakdown of parts of
a settlement (backpay, reinstatement, and various other award categories that
would all come together to constitute the total settlement package. That
information would likely go into a 'settlement details' type of table, and be
one-to-many related to the main files table on the fileID field (fileID being
the pk of the files table) or one-to-many on the settlements table if I pull
settlement data out of files and put it into it's own table.

I would guesstimate the approximate percentage of cases that will be
recorded as 'settled' to be around 33-40%.....maybe even a little higher.

So my question is this: should the main settlement info go into it's own
table and be related, one-to-one, with the files table, and related
one-to-many with the settlement details table, or should I just keep all the
main settlement data in the files table and relate the 'settlement details'
table directly to files?

Since I haven't fully committed to the 'settlement details' table (though
I'm a strong lean in that direction), if answers depend on the existence of
it, it would be nice to know that...

Thanks, in advance, for any replies,
CW

 

[Janis]

It sounds like you need a separate settlements table for payment
information. Especially if there is a possibility for more than one type
settlement. This would be a one to many.

 

[Dale Fye]

Actually, the relationship between your files table and settlements table
will be a 1-to-ZeroOrOne relationship, since roughly 60% of your files will
not have a record in the Settlements table. But yes, I would do it this way.

Then, the relationship between the settlement and settlement-details tables
would be a one-to-one or many (depending on the number of "parts" of the
settlement.

 

[Cheese_whiz]

Thanks for the replies. Much appreciated.
CW

Actually, the relationship between your files table and settlements table
will be a 1-to-ZeroOrOne relationship, since roughly 60% of your files will
not have a record in the Settlements table. But yes, I would do it this way.

Then, the relationship between the settlement and settlement-details tables
would be a one-to-one or many (depending on the number of "parts" of the
settlement.

 

[Janis]

Conversely you could relate the main file to the detail files and not have
the one to one. All you need is a status field that says settled. You could
relate the mainID field and this status field to the details records. Can
someone say which is preferable?