Compound Key Question

[David W. Fenton]

I'm generally against using compound keys, except in join tables,
but I'm currently mapping out a schema where the join table has
child records. The application is for fund-raising and I have four
relevant tables:

tblPerson
tblOutreach -- the list of fund-raising actions/events (letters,
events, etc.)

To join these two tables, I have:

tblPersonOutreach
PK: PersonID + OutreachID

Joined to that is:
tblContributions
PK: ContributionID
FK: ???

The question is:

Should I create a surrogate Autonumber in tblPersonOutreach to have
a single-field join between tblContribution and tblPersonOutreach,
or store the two fields of the compound key of tblPersonOutreach in
tblContribution?

I would normally lean towards using the surrogate key
(tblPersonOutreachID), but in this case, I can see that it would be
quite common for me to want to display or print data from tblPerson
and tblContribution, or data from tblOutreach and tblContribution
(+tblPerson, in most cases). Storing the compound key means that I
could leave the join table out of the mix entirely.

Then it occurs to me that if I'm going to structure it this way,
maybe I should just collapse tblPersonOutreach and tblContribution
into a single table. It means that there won't be a proper 1:N
relationship between the Outreach action and the contributions it
generates, but, well, given that the vast majority of these outreach
actions will generate only one contribution per action, and given
that there's very little data in the join table, I'm not sure I see
any real benefit from maintaining the strictly correct normalized
structure.

I've got another app which is used for fundraising and it works
exactly the same way, with the two tables collapsed into one, and it
has never posed any kind of problem whatsoever. However, the
difference there is that in that application, a record is created
only when a contribution is received (i.e., they don't track
unsuccessful outreach actions), so it makes a great deal of sense to
collapse the two into one.

It actually would make UI design easier. On the other hand, I could
always use a flattened recordsource for me list of Outreach
events/contributions (I was originally considering using a simple
datasheet list with cascading datasheets to show the contributions).

Thoughts? Comments?

I'm really not seeing any benefit to having the join table as a
separate table, except for an exactitude in data modelling that has
no benefit that I can see of in an actual application.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Alan Webb]

David,
I ain't agin or fer any particular thing. Compound keys, if they help, are
something I'll use. It depends on what my customer wants. It most
especially depends on how my client answers the question, "What about your
organization are you measuring?" In most cases reports are about measuring
the performance of an organization in terms of declared aspects of the
business. Crap, bunch of jargon. One more time, in most cases the
management of an organization wants a database so they can learn how the
organization is performing and then make some decisions that will make it
perform better. Implied in your Person and Outreach tables is a need to
understand how much money was contributed by a person or during a particular
outreach event or activity. This would lead me to build a couple dimension
tables--Person and Outreach, and a fact table, Contributions, which would
have as a compound primary key PersonOutreach. I don't have access to your
requirements document so I can't say whether my start at a schema would work
for your customer. But compound keys are useful in data warehouse design
and I wouldn't just dismiss them because they don't fit what a text book
says is the orthodox design approach.

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.90...

I'm generally against using compound keys, except in join tables,
but I'm currently mapping out a schema where the join table has
child records. The application is for fund-raising and I have four
relevant tables:

[Paul Pentz]

If I understand your situation correctly, it looks like you have a
many-to-many relationship between tblPerson and tblOutreach. That is,
one person can potentially contribute to multiple outreaches, and one
outreach will have many people contributing to it. In my experience,
it's always best to use a join table between many-to-many relationships
with a unique key for each join record. I would structure it like this

tblPerson tblPersonOutreach tblOutreach
--------- ----------------- -----------
PersonKey------| PersonOutreachKey |-----OutreachKey
PersonName |-----PersonFK | OutreachName
PersonAddr OutreachFK--------------| OutreachAddr
MorePersonData ContributionData MoreOutreachData

I may never use the join table key (PersonOutreachKey) but it's there
uniquely identifying each record if needed. That's where I would put
all the contribution data, such as amount, date, etc., data specific to
each contribution. When I design a database, I use unique keys for every
table.

Of course there are many ways you could do this, that's just how I'd do it.

Paul
David W. Fenton wrote:

I'm generally against using compound keys, except in join tables,
but I'm currently mapping out a schema where the join table has
child records. The application is for fund-raising and I have four
relevant tables:

tblPerson
tblOutreach -- the list of fund-raising actions/events (letters,
events, etc.)

To join these two tables, I have:

tblPersonOutreach
PK: PersonID + OutreachID

Joined to that is:
tblContributions
PK: ContributionID
FK: ???

The question is:

Should I create a surrogate Autonumber in tblPersonOutreach to have
a single-field join between tblContribution and tblPersonOutreach,
or store the two fields of the compound key of tblPersonOutreach in
tblContribution?

I would normally lean towards using the surrogate key
(tblPersonOutreachID), but in this case, I can see that it would be
quite common for me to want to display or print data from tblPerson
and tblContribution, or data from tblOutreach and tblContribution
(+tblPerson, in most cases). Storing the compound key means that I
could leave the join table out of the mix entirely.

Then it occurs to me that if I'm going to structure it this way,
maybe I should just collapse tblPersonOutreach and tblContribution
into a single table. It means that there won't be a proper 1:N
relationship between the Outreach action and the contributions it
generates, but, well, given that the vast majority of these outreach
actions will generate only one contribution per action, and given
that there's very little data in the join table, I'm not sure I see
any real benefit from maintaining the strictly correct normalized
structure.

I've got another app which is used for fundraising and it works
exactly the same way, with the two tables collapsed into one, and it
has never posed any kind of problem whatsoever. However, the
difference there is that in that application, a record is created
only when a contribution is received (i.e., they don't track
unsuccessful outreach actions), so it makes a great deal of sense to
collapse the two into one.

It actually would make UI design easier. On the other hand, I could
always use a flattened recordsource for me list of Outreach
events/contributions (I was originally considering using a simple
datasheet list with cascading datasheets to show the contributions).

Thoughts? Comments?

I'm really not seeing any benefit to having the join table as a
separate table, except for an exactitude in data modelling that has
no benefit that I can see of in an actual application.

[rkc]

David W. Fenton wrote:

Then it occurs to me that if I'm going to structure it this way,
maybe I should just collapse tblPersonOutreach and tblContribution
into a single table. It means that there won't be a proper 1:N
relationship between the Outreach action and the contributions it
generates, but, well, given that the vast majority of these outreach
actions will generate only one contribution per action, and given
that there's very little data in the join table, I'm not sure I see
any real benefit from maintaining the strictly correct normalized
structure.

I'd have to assume, because you don't say, that there must be some
other info in tblPersonOutreach that would be duplicated in
tblContribution if you did combine them. Otherwise there is no point
to tblPersonOutreach in the first place.

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:AQ*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Then it occurs to me that if I'm going to structure it this way,
maybe I should just collapse tblPersonOutreach and
tblContribution into a single table. It means that there won't be
a proper 1:N relationship between the Outreach action and the
contributions it generates, but, well, given that the vast
majority of these outreach actions will generate only one
contribution per action, and given that there's very little data
in the join table, I'm not sure I see any real benefit from
maintaining the strictly correct normalized structure.

I'd have to assume, because you don't say, that there must be some
other info in tblPersonOutreach that would be duplicated in
tblContribution if you did combine them. Otherwise there is no
point to tblPersonOutreach in the first place.

Presently, the only data I have in tblPersonOutreach (other than the
two keys) is the date of creation of the record and who created it.
I'm not sure if that information serves any purpose at all
independent of the other information. Once a contribution is
received, does it really matter when the PersonOutreach record was
created (most of them will be created in an automated fashion when
the app is finished, anyway, as the result of data exports or mail
merges).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:AQ*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

Then it occurs to me that if I'm going to structure it this way,
maybe I should just collapse tblPersonOutreach and
tblContribution into a single table. It means that there won't be
a proper 1:N relationship between the Outreach action and the
contributions it generates, but, well, given that the vast
majority of these outreach actions will generate only one
contribution per action, and given that there's very little data
in the join table, I'm not sure I see any real benefit from
maintaining the strictly correct normalized structure.

I'd have to assume, because you don't say, that there must be some
other info in tblPersonOutreach that would be duplicated in
tblContribution if you did combine them. Otherwise there is no
point to tblPersonOutreach in the first place.

Presently, the only data I have in tblPersonOutreach (other than the
two keys) is the date of creation of the record and who created it.
I'm not sure if that information serves any purpose at all
independent of the other information. Once a contribution is
received, does it really matter when the PersonOutreach record was
created (most of them will be created in an automated fashion when
the app is finished, anyway, as the result of data exports or mail
merges).

I see Person(contributor) in a many to many relationship with
Outreach(contributed to) joined by Contribution.

Unless PersonOutreach serves a purpose that can't be served by Contribution.

[David W. Fenton]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:ox*******************@twister.nyroc.rr.com:

David W. Fenton wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote in
news:AQ*******************@twister.nyroc.rr.com:

David W. Fenton wrote:
Then it occurs to me that if I'm going to structure it this way,
maybe I should just collapse tblPersonOutreach and
tblContribution into a single table. It means that there won't
be a proper 1:N relationship between the Outreach action and the
contributions it generates, but, well, given that the vast
majority of these outreach actions will generate only one
contribution per action, and given that there's very little data
in the join table, I'm not sure I see any real benefit from
maintaining the strictly correct normalized structure.

I'd have to assume, because you don't say, that there must be
some other info in tblPersonOutreach that would be duplicated in
tblContribution if you did combine them. Otherwise there is no
point to tblPersonOutreach in the first place.

Presently, the only data I have in tblPersonOutreach (other than
the two keys) is the date of creation of the record and who
created it. I'm not sure if that information serves any purpose
at all independent of the other information. Once a contribution
is received, does it really matter when the PersonOutreach record
was created (most of them will be created in an automated fashion
when the app is finished, anyway, as the result of data exports
or mail merges).

I see Person(contributor) in a many to many relationship with
Outreach(contributed to) joined by Contribution.

Unless PersonOutreach serves a purpose that can't be served by
Contribution.

Well, I'm not going to be able to resolve it until I talk with the
client. It depends on what data they want to store.

Say, for example, they hold a fundraising event, but they send out
snail mail invites to some people and email invites to others. Where
should I store that information? It belongs in PersonOutreach.

Now, the client may not care whether a person was sent an email or
an actual letter. If they don't, then I don't have to worry about
it.

Or, if they do, I may need to structure Outreach differently, so
that an event can have multiple types of contact with the donors.

I'm leaning towards just implementing it without collapsing the two
tables, but basically, the question comes down to:

What's the cost of the extra join?

If the cost is not high, then I can implement it that way and the
user will never know, since I'll only ever present data in the
flattened view (which would be the case even when I need the extra
table).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc