Compound Key Question

**Alan Webb** · Nov 13 '05, 08:53 AM

Re: Compound Key Question

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" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns960EAED D1A09Adfentonbw aynetinvali@24. 168.128.90...[color=blue]
> 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:[/color]

**Paul Pentz** · Nov 13 '05, 08:53 AM

Re: Compound Key Question

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 tblPersonOutrea ch tblOutreach
--------- ----------------- -----------
PersonKey------| PersonOutreachK ey |-----OutreachKey
PersonName |-----PersonFK | OutreachName
PersonAddr OutreachFK--------------| OutreachAddr
MorePersonData ContributionDat a MoreOutreachDat a

I may never use the join table key (PersonOutreach Key) 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:[color=blue]
> 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:
>
> tblPersonOutrea ch
> PK: PersonID + OutreachID
>
> Joined to that is:
> tblContribution s
> PK: ContributionID
> FK: ???
>
> The question is:
>
> Should I create a surrogate Autonumber in tblPersonOutrea ch to have
> a single-field join between tblContribution and tblPersonOutrea ch,
> or store the two fields of the compound key of tblPersonOutrea ch in
> tblContribution ?
>
> I would normally lean towards using the surrogate key
> (tblPersonOutre achID), 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 tblPersonOutrea ch 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.
>[/color]

**rkc** · Nov 13 '05, 08:53 AM

Re: Compound Key Question

David W. Fenton wrote:
[color=blue]
> Then it occurs to me that if I'm going to structure it this way,
> maybe I should just collapse tblPersonOutrea ch 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.[/color]

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

**David W. Fenton** · Nov 13 '05, 08:55 AM

Re: Compound Key Question

rkc <rkc@rochester. yabba.dabba.do. rr.bomb> wrote in
news:AQPVd.6828 7$H05.24636@twi ster.nyroc.rr.c om:
[color=blue]
> David W. Fenton wrote:
>[color=green]
>> Then it occurs to me that if I'm going to structure it this way,
>> maybe I should just collapse tblPersonOutrea ch 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.[/color]
>
> I'd have to assume, because you don't say, that there must be some
> other info in tblPersonOutrea ch that would be duplicated in
> tblContribution if you did combine them. Otherwise there is no
> point to tblPersonOutrea ch in the first place.[/color]

Presently, the only data I have in tblPersonOutrea ch (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** · Nov 13 '05, 08:55 AM

Re: Compound Key Question

David W. Fenton wrote:[color=blue]
> rkc <rkc@rochester. yabba.dabba.do. rr.bomb> wrote in
> news:AQPVd.6828 7$H05.24636@twi ster.nyroc.rr.c om:
>
>[color=green]
>>David W. Fenton wrote:
>>
>>[color=darkred]
>>>Then it occurs to me that if I'm going to structure it this way,
>>>maybe I should just collapse tblPersonOutrea ch and
>>>tblContribut ion into a single table. It means that there won't be
>>>a proper 1:N relationship between the Outreach action and the
>>>contribution s it generates, but, well, given that the vast
>>>majority of these outreach actions will generate only one
>>>contributi on per action, and given that there's very little data
>>>in the join table, I'm not sure I see any real benefit from
>>>maintainin g the strictly correct normalized structure.[/color]
>>
>>I'd have to assume, because you don't say, that there must be some
>>other info in tblPersonOutrea ch that would be duplicated in
>>tblContributi on if you did combine them. Otherwise there is no
>>point to tblPersonOutrea ch in the first place.[/color]
>
>
> Presently, the only data I have in tblPersonOutrea ch (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).[/color]

I see Person(contribu tor) in a many to many relationship with
Outreach(contri buted to) joined by Contribution.

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

**David W. Fenton** · Nov 13 '05, 08:56 AM

Re: Compound Key Question

rkc <rkc@rochester. yabba.dabba.do. rr.bomb> wrote in
news:oxaWd.5020 6$vK5.44100@twi ster.nyroc.rr.c om:
[color=blue]
> David W. Fenton wrote:[color=green]
>> rkc <rkc@rochester. yabba.dabba.do. rr.bomb> wrote in
>> news:AQPVd.6828 7$H05.24636@twi ster.nyroc.rr.c om:
>>
>>[color=darkred]
>>>David W. Fenton wrote:
>>>
>>>
>>>>Then it occurs to me that if I'm going to structure it this way,
>>>>maybe I should just collapse tblPersonOutrea ch and
>>>>tblContribu tion into a single table. It means that there won't
>>>>be a proper 1:N relationship between the Outreach action and the
>>>>contributio ns it generates, but, well, given that the vast
>>>>majority of these outreach actions will generate only one
>>>>contributio n per action, and given that there's very little data
>>>>in the join table, I'm not sure I see any real benefit from
>>>>maintaini ng the strictly correct normalized structure.
>>>
>>>I'd have to assume, because you don't say, that there must be
>>>some other info in tblPersonOutrea ch that would be duplicated in
>>>tblContribut ion if you did combine them. Otherwise there is no
>>>point to tblPersonOutrea ch in the first place.[/color]
>>
>>
>> Presently, the only data I have in tblPersonOutrea ch (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).[/color]
>
> I see Person(contribu tor) in a many to many relationship with
> Outreach(contri buted to) joined by Contribution.
>
> Unless PersonOutreach serves a purpose that can't be served by
> Contribution.[/color]

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