One-To-One Tables

[oldblindpew]

John, Jerry, Steve:

I THINK I'VE GOT IT!! It came to me last night driving home from work. I
need the Agreements table and the Requirements table in a many-to-many
relationship, with the Certificates table serving as the junction table. But
since the Requirements table will actually do double duty as providing both
insurance requirement values and certificate offering values, I will refer to
it as the Insurance Parameters table instead.

TblAgreements
AgreementID
<Agreement fields>

TblInsParameters
InsParameterID
InsParameterDescrip
<Other fields, if any>

TblCertificates
CertificateID
AgreementID
InsRequirementID (same as InsParameterID)
InsOfferingID (same as InsParameterID)

So if the InsOfferingID and the InsRequirementID match, then the Certificate
is valid for that one particular parameter.

Now, it is not clear from my reference books whether a junction table must
use a composite primary key consisting of values that match the primary keys
from the two joined tables, or, whether you are free to use a separate
primary key. I hope the latter is the case, because I've grown prejudiced
against composite keys.

I wasn't sure where in the thread to put this posting, so I put it here in
hopes you all find it. Thanks for your suggestions, and I would appreciate
any further suggestions, corrections or advice you may have to offer on this
plan of attack. I'm sure as I get on with this there will be plenty of
unforeseen obstacles to negotiate.
--OBP

 

[John W. Vinson]

Now, it is not clear from my reference books whether a junction table must
use a composite primary key consisting of values that match the primary keys
from the two joined tables, or, whether you are free to use a separate
primary key. I hope the latter is the case, because I've grown prejudiced
against composite keys.

You can indeed put in an autonumber primary key as a surrogate; I would
recommend putting a unique Index on the combination of the two fields to
prevent inadvertant duplicates.

Glad you got it working.

 

[oldblindpew]

Well, it isn't working yet, but at least I have a plan, thanks largely to
you. If you hadn't replied to my original question, I would probably still
be thinking of this like a spreadsheet, which might have proven functional,
but not optimal. I will pay close attention to your advice about the Index,
also. Thanks again and again!
-OBP

 

[David W. Fenton]

Now, it is not clear from my reference books whether a junction
table must use a composite primary key consisting of values that
match the primary keys from the two joined tables, or, whether
you are free to use a separate primary key. I hope the latter is
the case, because I've grown prejudiced against composite keys.

I would never use a surrogate key in a join table unless there were
tables related to the PK of the join table. For instance, if you
have a junction table between person and employer and you need to
have multiple titles for each of the records in the junction table,
then you'd need to store the junction table's PK in the storing the
position titles.

tblPerson PK PersonID
tblCompany PK CompanyID
tblPersonCompany PK PersonID+CompanyID, FK PersonID, FK
CompanyID tblTitle PK TitleID, FK PersonID+CompanyID

versus

tblPerson PK PersonID
tblCompany PK CompanyID
tblPersonCompany PK PersonCompanyID, FK PersonID, FK CompanyID
tblTitle PK TitleID, FK PersonCompanyID

But this is what I consider an unusual case. It's very seldom that
you need a many-to-one relationship beween a child table and have a
junction table be the parent record. I had to strain to come up with
such a situation, and I've encountered it only a few times in my 15+
years of database application design.

 

[oldblindpew]

Hi, David, and thanks for your reply.

Side-issue: Person-to-Company sounds like a many-to-one relationship. I
thought the purpose of join tables was to handle many-to-many relationships.

Main issue: I like the concept of composite keys, but I read somewhere on
the internet from one published expert that composite keys should be avoided
because they create problems. My own experience is that even though Access
knows that a composite key is two fields that sometimes act as one, Access
apparently does not provide ready-made support for this reality. For
example, I like to use a list box as a navigation tool to allow the user to
pick a record, but this won't work with composite keys because the list box
is incapable of passing the value of more than one field.

Another question: In your first example, does your junction table actually
store PersonID and CompanyID twice, once as PK and again as FK?

Thanks, OBP

 

[BruceM]

I thought you were withdrawing from further response.

I remember seeing the posting you have copied here, but it isn't in this
thread.

 

[David W. Fenton]

Side-issue: Person-to-Company sounds like a many-to-one
relationship. I thought the purpose of join tables was to handle
many-to-many relationships.

Person-to-Company is very often a many-to-many because a person can
have a position at more than one company/organization. I could be
both president and CEO of ACME Corp., and also on the board of
InfiniCorp, and then a partner in PrivateVentures, LLC. What I've
just described is N:N for person/company, and 1:N for a person's
position(s) within each company.

Main issue: I like the concept of composite keys, but I read
somewhere on the internet from one published expert that composite
keys should be avoided because they create problems.

They do. They repeat a whole lot of data, which means you lose the
efficiency of Jet/ACE when retrieving the index pages, and then only
the needed data pages. If most of the data is in the foreign key,
Jet/ACE may not need to retrieve anything but the index, but the
number of index pages isn't going to be much larger for the same
number of rows, so I'd think that those composite keys would cause a
lot more data to be pulled across the Internet.

I don't use a composite key for any table whose PK is the foreign
key in another table. That is, if the table is not the parent in a
relationship with another table, a composite key is fine, particular
if it's a composite key of foreign keys, as in a many-to-many join
table.

My own experience is that even though Access
knows that a composite key is two fields that sometimes act as
one, Access apparently does not provide ready-made support for
this reality. For example, I like to use a list box as a
navigation tool to allow the user to pick a record, but this won't
work with composite keys because the list box is incapable of
passing the value of more than one field.

Well, not as a bound column, but you can still get the data in the
other columns. I think you use ItemData(i).Column -- I always have
to look it up, to be honest.

But for a bound listbox, it doesn't work well.

I don't bind many listboxes, but I certainly use lots of bound combo
boxes, and the problem with composite keys is identical there.

Another question: In your first example, does your junction table
actually store PersonID and CompanyID twice, once as PK and again
as FK?

No. I was describing the INDEXES not the fields. The table has only
the two fields, and there's a composite PK made up of the two
fields, and each field is individually a FK.

 

[oldblindpew]

David,
Thank you for your clear and practical answers.

I have reexamined the question of reading data from various columns in a
list box selection. I had thought before that this could only be
accomplished for a multiselect list box, and obviously, selecting more than
one record to navigate to would be undesirable. However, it appears one can
read any column in a list box selection by using the list box's Column
property. I haven't tested it yet, though.

As for the main topic of this thread, I have about come full circle back to
my original plan of using 1:1 tables for insurance information. This is due
mostly to the nature of the data: Insurance requirements are not all the same
kind of thing, and I don't think it would do to put them all in one master
table, like state name abbreviations. Also, I have many Agreements in my
Agreements table, but not all of them have Insurance, so this is a classic
example where a separate 1:1 table is justified.

Some may argue that store inventory items are all different, but they all go
in one table. My Agreement is just an Order, and my Insurance Requirements
just the Order Detail. Maybe, but store items share a common set of fields:
Item No, Description, Quantity, Unit of Measure, Unit Price, Shipping Weight.
Insurance requirements, in contrast, can be Y/N, Dollar amounts, Dates, or
Numeric values.

I think I'll just have to go ahead and find out the hard way. One thing
about Access is that so far, with my simple application, it hasn't been too
hard to make changes.
--OBP

 

[David W. Fenton]

As for the main topic of this thread, I have about come full
circle back to my original plan of using 1:1 tables for insurance
information. This is due mostly to the nature of the data:
Insurance requirements are not all the same kind of thing, and I
don't think it would do to put them all in one master table, like
state name abbreviations. Also, I have many Agreements in my
Agreements table, but not all of them have Insurance, so this is a
classic example where a separate 1:1 table is justified.

I used to be big on supertypes and subtypes, but found that the
outer joins needed to display all of them in a single list (or
UNION) were an unacceptable performance drain.

Now, with server-side views doing the joins, it might not be as bad,
but with Jet/ACE, it can be a complete killer.

That said, I just implemented a Customer History table that has
foreign keys on 3 different tables and does exactly what I just
described. It is performing just fine -- dunno why, but maybe I'm
just better at it than I used to be.

The perfect 1:1 scenario is when you create a basic record and then
some records then eventually reach a state when a bunch of other
fields need to be filled out, but not all of the basic records reach
that point. In a case management database for a psychiatric clinic,
the 1:1 side table recording the demographics, and were not filled
out until the case was closed. That was a perfect application of
1:1, as cases that never closed but were simply abandoned did not
have the demographics. It also made it easy to deal with the
demographic data as separate from the information specific to the
individual, which was good for confidentiality.

But I haven't encountered that many such situations.