relational reasoning -- why two tables and not one?

#1

On another mailing list about a database which shall not be named, a
poster asked about a single-table database structure. It was to track
donations, and that thank-you letters had been sent in reply.

A couple folks ( myself included ) thought that there should be at
least two tables -- 'donors' and 'donations' . But the poster argued
that no, there would never be a holiday fund drive appeal sent out to
all donors, or a year-end statement, or anything of that sort. So a
single table would suffice.

I tried to argue the point that having 'donors' and 'donations' more
accurately modeled 'reality' , and failed. Another poster, who was in
fact in favor of two tables, argued against the 'modeling reality'
argument, saying that theorists would it's wrong to have
'donation_dates' as a separate table, even though the relationship "in
reality" is one date for many donations. If it meets the functionality
specs, it's fine.

( There was talk of needing Donors as an entity later on, but there
was no need for it in the specs now, so it's moot ).

I couldn't think of a good argument against it, so I must be wrong.
But my gut instinct or intuition is telling me there is some
understanding of relational theory or something that I am not
grasping, which would prove insightful in this discussion.

Thoughts and comments?

#2

lawpoop wrote:

Quote:

On another mailing list about a database which shall not be named, a
poster asked about a single-table database structure. It was to track
donations, and that thank-you letters had been sent in reply.

A couple folks ( myself included ) thought that there should be at
least two tables -- 'donors' and 'donations' . But the poster argued
that no, there would never be a holiday fund drive appeal sent out to
all donors, or a year-end statement, or anything of that sort. So a
single table would suffice.

I tried to argue the point that having 'donors' and 'donations' more
accurately modeled 'reality' , and failed. Another poster, who was in
fact in favor of two tables, argued against the 'modeling reality'
argument, saying that theorists would it's wrong to have
'donation_dates' as a separate table, even though the relationship "in
reality" is one date for many donations. If it meets the functionality
specs, it's fine.

( There was talk of needing Donors as an entity later on, but there
was no need for it in the specs now, so it's moot ).

I couldn't think of a good argument against it, so I must be wrong.
But my gut instinct or intuition is telling me there is some
understanding of relational theory or something that I am not
grasping, which would prove insightful in this discussion.

Thoughts and comments?

I don't see any particularly compelling reason for donors and donations
relations based on the meager information available from your post.
However, I suspect a compelling argument can be made for the necessity
of donations and replies relations.

#3

On Oct 14, 4:44*pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

Quote:

I don't see any particularly compelling reason for donors and donations
relations based on the meager information available from your post.
However, I suspect a compelling argument can be made for the necessity
of donations and replies relations.

Care to expound?

#4

lawpoop wrote:

Quote:

On Oct 14, 4:44 pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:

I don't see any particularly compelling reason for donors and donations
relations based on the meager information available from your post.
However, I suspect a compelling argument can be made for the necessity
of donations and replies relations.

Care to expound?

Insofar as someone might not yet have replied, the cardinality of
donations and replies differ.

The donations relation is historical data so there is no particular
reason to assume anything depends on donor should a donor donate at
different times and you made no mention of a temporal database.

#5

lawpoop wrote:

Quote:

On another mailing list about a database which shall not be named, a
poster asked about a single-table database structure. It was to track
donations, and that thank-you letters had been sent in reply.

A couple folks ( myself included ) thought that there should be at
least two tables -- 'donors' and 'donations' . But the poster argued
that no, there would never be a holiday fund drive appeal sent out to
all donors, or a year-end statement, or anything of that sort. So a
single table would suffice.

I tried to argue the point that having 'donors' and 'donations' more
accurately modeled 'reality' , and failed. Another poster, who was in
fact in favor of two tables, argued against the 'modeling reality'
argument, saying that theorists would it's wrong to have
'donation_dates' as a separate table, even though the relationship "in
reality" is one date for many donations. If it meets the functionality
specs, it's fine.

( There was talk of needing Donors as an entity later on, but there
was no need for it in the specs now, so it's moot ).

I couldn't think of a good argument against it, so I must be wrong.
But my gut instinct or intuition is telling me there is some
understanding of relational theory or something that I am not
grasping, which would prove insightful in this discussion.

Thoughts and comments?

Whose reality? Also, theory doesn't dictate requirements. As Ralston
Saul suggests, there has been no such thing as absolute reality since a
few hundred years ago. When Date writes about modelling reality, I
think he means it very loosely. Reality as a target risks inventing
imaginary requirements although I realize there is a going industry
based on foisting that on users. Apparently the poster eliminated a
number of possible requirements so a single table doesn't seem
unreasonable given the 'meagre' information as Bob B put it, even if
some donors are repeat donors.

#6

paul c wrote:

Quote:

lawpoop wrote:

On another mailing list about a database which shall not be named, a
poster asked about a single-table database structure. It was to track
donations, and that thank-you letters had been sent in reply.

A couple folks ( myself included ) thought that there should be at
least two tables -- 'donors' and 'donations' . But the poster argued
that no, there would never be a holiday fund drive appeal sent out to
all donors, or a year-end statement, or anything of that sort. So a
single table would suffice.

I tried to argue the point that having 'donors' and 'donations' more
accurately modeled 'reality' , and failed. Another poster, who was in
fact in favor of two tables, argued against the 'modeling reality'
argument, saying that theorists would it's wrong to have
'donation_dates' as a separate table, even though the relationship "in
reality" is one date for many donations. If it meets the functionality
specs, it's fine.

( There was talk of needing Donors as an entity later on, but there
was no need for it in the specs now, so it's moot ).

I couldn't think of a good argument against it, so I must be wrong.
But my gut instinct or intuition is telling me there is some
understanding of relational theory or something that I am not
grasping, which would prove insightful in this discussion.

Thoughts and comments?

Whose reality? Also, theory doesn't dictate requirements. As Ralston
Saul suggests, there has been no such thing as absolute reality since a
few hundred years ago. When Date writes about modelling reality, I
think he means it very loosely. Reality as a target risks inventing
imaginary requirements although I realize there is a going industry
based on foisting that on users. Apparently the poster eliminated a
number of possible requirements so a single table doesn't seem
unreasonable given the 'meagre' information as Bob B put it, even if
some donors are repeat donors.

Even then, it is historical data. For the same donor, all the
information might be the same from one donation to the next, or it might
all be different. Assuming one wants to record the information as it was
(or as it was reported by the donor) at the time of the donation, few
if any functional dependencies are likely to exist.

Unless, of course, one creates a temporal database, but even then, the
charity seems to collect no information about intervening points of time
so a temporal database seems inappropriate too.

#7

Bob Badour wrote:

Quote:

...
Even then, it is historical data. For the same donor, all the
information might be the same from one donation to the next, or it might
all be different. Assuming one wants to record the information as it was
(or as it was reported by the donor) at the time of the donation, few
if any functional dependencies are likely to exist.

Unless, of course, one creates a temporal database, but even then, the
charity seems to collect no information about intervening points of time
so a temporal database seems inappropriate too.

I presume 'historical' means rows are 'written once', normally not
replaced. If so that would be a good word to use if it is in fact one of
the requirements.

#8

paul c wrote:

Quote:

Bob Badour wrote:

...
Even then, it is historical data. For the same donor, all the
information might be the same from one donation to the next, or it
might all be different. Assuming one wants to record the information
as it was (or as it was reported by the donor) at the time of the
donation, few if any functional dependencies are likely to exist.

Unless, of course, one creates a temporal database, but even then, the
charity seems to collect no information about intervening points of
time so a temporal database seems inappropriate too.

I presume 'historical' means rows are 'written once', normally not
replaced. If so that would be a good word to use if it is in fact one of
the requirements.

We don't really have requirements. All we have are a handful of 3rd hand
comments, but those comments do suggest the relation is historical. The
database records donations made and replies sent. All past tense.
Nothing to change or keep up to date. Just what happened at some point
in the past--much like an audit trail.

#9

lawpoop wrote:

Quote:

On another mailing list about a database which shall not be named, a
poster asked about a single-table database structure. It was to track
donations, and that thank-you letters had been sent in reply.

A couple folks ( myself included ) thought that there should be at
least two tables -- 'donors' and 'donations' . But the poster argued
that no, there would never be a holiday fund drive appeal sent out to
all donors, or a year-end statement, or anything of that sort. So a
single table would suffice.

I tried to argue the point that having 'donors' and 'donations' more
accurately modeled 'reality' , and failed. Another poster, who was in
fact in favor of two tables, argued against the 'modeling reality'
argument, saying that theorists would it's wrong to have
'donation_dates' as a separate table, even though the relationship "in
reality" is one date for many donations. If it meets the functionality
specs, it's fine.

( There was talk of needing Donors as an entity later on, but there
was no need for it in the specs now, so it's moot ).

I couldn't think of a good argument against it, so I must be wrong.
But my gut instinct or intuition is telling me there is some
understanding of relational theory or something that I am not
grasping, which would prove insightful in this discussion.

Thoughts and comments?

Having a single table amounts to having a constraint that says that
the two facts (the fact about a specific donor and the fact about a
specific donation) are incapable of being learnt separately by the
business.

Can one have a donor who has not donated (yet)? Can one have a donation
without it comes from a donor? Can one have multiple donations by one
donor? If the answer to ANY of these questions is yes, then one
appears to need two tables. If the answer to ALL these questions is
no, then one appears to need a single table in order to enforce an
always correct representation of the business knowledge.

Of course my argument appears to break down if one is willing to permit
nullable columns, but I'm not.

--
Roy

#10

Roy Hann wrote:

Quote:

...
Can one have a donor who has not donated (yet)? ...

That is a great question because it indicates the rampant database
mysticism in the semi-literate so-called developed world. I'm sure
there are db's where prospective donors are called donors and a donor
isn't required to actually donate! Managers without budgets are similar.