Lost all sense of proportion

[Tal]

Hello All,

So in my ongoing attempt to create the most open and flexible charitable
receipting database, I have created a convoluted table structure comprising
26 tables, half of which seem to be junction tables.

I am wondering if someone would be able to look at my table structure and
let me know if it makes any sense at all.
I wonder if it helps that I used Allen Browne's Human Relationship database
as a jump off point, but I seem to have lost all sense of proportion since
then.
Concurrently, I have a barrel of picky donors and pickier users, so it has
been designed with all these needs in mind, I hope.

If you're willing to take a look, please let me know with your email address.

Many thanks,
Tal.

 

[Fred]

Hello Tal,

Assuming that this is talking about your thread of 1-2 days ago, I was
followiing that and guessed that you might end up where you did. It
involved you jumping to one of the more sophisticated (recursive) linking
architectures and dealing with other "final stage" functionality when it
looked like you still hadn't fully thought out the nature and organization
of entities and information that you are databasing. Would suggest starting
there. One point in particular is that you seemed to want to thinking
about treating address as entities, but using an architecture that treated
them as attributes of entities.

 

[Tal]

H Fred,

Interesting things to think about.
I think the reason I got where I did with the addresses is because they seem
to have a life of their own - meaning one client can have multiple addresses
and multiple clients can share one address and this seems to happen
independent of phone numbers, email addresses, credit cards, etc.
So I have created a whole bunch of little tables with a big junction table.
Does this make sense?

Thanks for your insight.

Tal

 

[Fred]

Hello Tal,

You have to start with the nature of the information that you want to
database and the missions that you want your database to accomplish.

This will include the types of scenarios than you DB must accomodate. In
many cases, extra allowing for scenerios has a cost of extra complexity. In
my opinion, you'll want to ask yourself these questions:

1. Do you NEED to track households as an entity? I.E. if Dick and Jane
are married and need to be tracked, is just tracking them as two individuals
good enough, or do your need their household to be tracked as an entity?

2. For your organizations, is your data element an instance of the
organization at a location, or is it the organization as a whole? (the
former is simpler)

3. Is it good enough to just record individuals as a member of ONE
organization (Business, Club, Household)

#1 and #2 affect the answer to #3. If the answer to #3 is "Yes" then a
simpler 2 level structure where the main tables are "Organizations" and
"People" would work.. If the answer to #3 is "no" then you need somethiing
more sophisticated / complex / abstract like Allens discussed database
structure.

4. Is it good enough to just record the "main" address for each
organization and person? (#2 will affect the answer to this) If "Yes",
then addresses should be just fields in that/those tables(s). If not,
then addresses should be in separate tables linked to those entities.

There's my two cents. Not sure if it helps or not.

Fred

 

[Tal]

Hi Fred,

Of course this is very helpful. Unfortunately the answer to most of your
questions is No. Thus my need for complexity.
I will give you an example:

Larry is a donor.
Larry sometimes donates as an individual.
Larry sometimes donates with his wife - ie. household comprising several
individuals
Larry sometimes donates through his business - another corporate entity
comprising several different individuals
Larry sometimes donates with his brother through their family foundation -
yet another different corporate entity comprising another set of individuals.
Sometimes Larry's wife donates with her brother through their family
foundation.

And the kicker is this:
Larry sometimes donates with his wife but wants the receipt to go through
his business or vice versa.
Larry's wife sometimes donates through her family foundation but wants the
receipt to go to Larry's business.
Or sometimes Larry's family foundation donates but want the receipt to be
split between the 3 siblings.

See what I'm working with?
Thoughts greatly appreciated. Also, Fred, if you're willing, I would be very
happy to send you the Access file with the table structure and Relationships.

Cheers,
Tal

 

[Fred]

Although seeing your DB would provide additional info, I think that what's in
this thread is more useful to your solution. If you don't have a clear
picture of these underlying things, then your DB structure is built on
swampland.

So, we know that there are myriad possibilities of where donations can come
from. That still doesn't finalize the answer of whether each one of those
possibilities needs to be tracked / recorded as an entity.

Now you have to decided (for yourself or to tell us) what exactly it is your
mission to tack in the DB. Obviously (and vaguely speaking) it includes
contact informaiton on organizations and people.

Is it also your mission to track DONATIONS? And if so, have you decided
that a donation has to be recorded as having come from a single entity (as
you are sort of implying). So, if Doug and Tom decide to get together and
make a donation, you would have to create "DougTom" as a new single entity to
tie it to. Or could you / would yo prefer to just record the donation as
being 50% form Doug and 50% from Tom. (which would simplify your life)

If your answer is the complex variant of all of these things, then you need
a structure like Allens and your life is going to be complicated. I'd make
those 5 tables, not 30. Maybe add one for addresses.

Sincerely,

Fred