Database Design

[Mike]

Hello All:

I know how I have my access application right now that isnt quite right, but
it works. But I would like to know the proper way to do this. I have 4
tables. Pension Fund Offices, Local Unions-pension & Medical Fund Offices,
Local Unions-medical.

Pension Fund Office table:
FundOfficeCode (primary key)
Fund Office Name

Local Union Table:
LocalUnionCode (primary key)
Local Union Name
FundOfficeCode
Amount

Same basic layout is for the Medical Tables.

There a one to many relationship between Pension Fund Office and Local
Unions-pension. And an one to many relationship between Medical Fund Office
and Local Unions-medical The Local Union tables contain the same
information. The tricky part is that one union could remit money to the
same fund office for both medical & pension. Or the local union could send
money to separate fund offices for pension & medical.

Can this be normalized within one access database? Or should I create two
separate databases for Pension & Medical, then create another database just
to be used as a front-end?

TIA

Mike

 

[Tim Ferguson]

Can this be normalized within one access database? Or should I create
two separate databases for Pension & Medical, then create another
database just to be used as a front-end?

There is rarely any logical design reason to chop a single database into
separate mdb files. If the tables are going to share information, then
you cannot control the dependencies unless they are in the same mdb.

But your design has me slightly puzzled.

The Local Union tables contain the same
information.

Does this mean you should have one LocalUnion table? If they are the same
things then storing them twice is asking for trouble. I am also
suspicious that the PFO and MFO records should be in one table too even
with a column called MedicalOrPension or something.

The tricky part is that one union could remit money to
the same fund office for both medical & pension. Or the local union
could send money to separate fund offices for pension & medical.

This sounds like you really need a different table to hold the payments,
or arrangements for pensions if there is a many-to-many relationship
between the offices and the unions.

Hope that helps


Tim F

 

[Squid]

One Fund Office can administer only Pension or only Medical or both funds.
A fund office will always have a one to many relationship to the local
unions (they usually administer benefits for more than one union). The
local union may participate in only Pension or only Medical or both funds.

So thats why original design was to separate Pension & Medical databases...
it was simply 2 tables and the relationship to ensured the reports produced
the correct results. For exmaple in the Pension database: I had the end
user enter the amounts for each union. Then ran a query to give the total
amount to be reciprocated back to each individual Fund Office. (e.g. Locals
1, 2, 3 are mapped to Fund Office A, sum of locals 1, 2, 3 equals amount
reciprocated to Fund Office A).

But then I tried including Medical Fund, that could have different local
unions reporting to a fund office than Pension... that were I am getting
confused. Then how to maintain the one to many relationship between Fund &
Local Union.

 

[Tim Ferguson]

One Fund Office can administer only Pension or only Medical or both
funds.

That seems to cover just about all the possibilities!

So thats why original design was to separate Pension & Medical
databases...

and now you've lost me. Above you said that offices can relate to either
or both, which suggests that both types are highly inter-dependent. What
benefit do you expect from separation?

For exmaple in the Pension database: I had the end
user enter the amounts for each union.

I hope these "PaidAmounts" are in another table..?

At the moment, I think we have a design something like:

+-< CanAdminister >-+
FundOffices -+ +-- Funds
| |
+-< PaidAmounts >---+


so there are two many-to-many relationships linking the FundOffices and
Funds tables. I just have this feeling that you are making it more
complicated than in needs to be.

All the best


Tim F

 

[Guest]

Hi Tim,

Here's some excerpts from another of Squid's threads discussing the same
problem:

Please let me know if each of these statements is true or false.

1. A Fund Office can handle only Pensions, only Medical, or Both. - True
2. A Fund Office works with many Local Unions. - True
3. A Local Union can buy only Pensions, only Medical, or Both. - True
4. A Local Union can buy Pensions from only one Fund Office. - True
5. A Local Union can buy Medical from only one Fund Office. - True
6. If the Local Union buys both Medical and Pension, each can be purchased
from different Fund Offices. - True

** Buy is not really the correct termalogy. local union participates in a
benefit plan. Local Union will never participate in more than one Pension
Fund or Medical Fund

Local Union 76 has Pension & Medical. Pension & Medical is remitted to Fund
Office A
Local Union 55 has Pension & Medical. Pension is remitted to Fund Office A.
Medical is remitted to Fund Office B
Local Union 12 has Pension only. Pension is remitted to Fund Office A
Local Union 23 has Medical only. Medical is remitted to Fund Office B

The report output will be:
Pension:
Fund Office A - sum of locals 76, 55, 12 remittance amounts

Medical
Fund Office B - sum of locals 55, 23 remittance amount

Based on the above answers from Squid I created the following table structure:

tblFundOffice
FundOfficeID (PK)
FundOfficeName
(Other fields like address, etc.)

tblLocalUnion
LocalUnionID (PK)
LocalUnionName
LocalUnionAddress, etc....

tblFundLocal 'this would be the junction table...
FundLocalID (PK)
FundOfficeID (FK)
LocalUnionID (FK)
RelTypeID (FK)

tblRelationshipType
RelTypeID
RelTypeName 'Pension, Medical, etc.

tblTransactions
TransactionID (PK)
FundLocalID (FK) 'establishes Local Union, Fund Office, & Type
TransactionDate
TransactionAmount

I suspect that Squid is concerned about how to pull reports and show
payments from Local Unions to a specific Fund Office. I believe that he's
been getting those reports through an unorthodox table structure rather than
through query techniques.

Tim - if you would please, take a look at the above structure (and his other
thread on the same subject?) and see if it doesn't fit Squid's needs. Make
any comments you feel are relevant.

 

[Tim Ferguson]

Tim - if you would please, take a look at the above structure (and his
other thread on the same subject?) and see if it doesn't fit Squid's
needs. Make any comments you feel are relevant.
yes

yes

tblFundLocal 'this would be the junction table...

Represented by CanAdminister in my diagram.
Awkward, isn't it? It's hard (in Access) to constrain this table to allow
only ONE medical office and ONE pension office per LocalUnion. Using two
FKs to implement the one-to-many's, though, makes the Transactions harder
to control.

tblRelationshipType

Probably redundant, depends on what else needs to be known about
Relationships

tblTransactions
TransactionID (PK)
FundLocalID (FK) 'establishes Local Union, Fund Office, & Type
TransactionDate
TransactionAmount

Using a FK to reference the FundLocal (CanAdminister) table means that you
can't make a medical payment to a pension-only union. That is probably a
more robust requirement than the only-one-office-per-union rule which
could plausibly change in the future (what do I know about pensions? I
just pays me money every month!). This probably is the way to go. As a
purist, I would probably argue about using natural keys rather than all
these (e.g FundLocalID) placeholders, but it's not a very important point.

I suspect that Squid is concerned about how to pull reports and show
payments from Local Unions to a specific Fund Office. I believe that
he's
been getting those reports through an unorthodox table structure rather
than
through query techniques.

"Fragile" is the word that springs to mind!

All the best

Tim F

 

[Squid]

Not as easy as it looks, eh? Adding the additional fund through this
application turned something very simple, into something very difficult.
RPW's structure seems to work. Once I figure out (hopefully with the help
of Dick Goldgar -microsoft.public.access-) why the SQL of the cascading
comboboxes arent populating the textbox, this is one step closer to working.

Tim, as I mentioned in the original thread. I was using Pension Fund Office
table referencing local unions (one to many relationship). This provided
easy data entry (transactions) for the user. (LocalUnionID, Amount). Then
ran a simple query compiling the total amounts per LocalUnion to FundOffice
into a report (by Fund Office). When I tried to integrate the Medical Fund
Offices... I began scratching my head bald.

One field I forget to add at first, but seems to fit, is the APCode(Accounts
Payable Code)... in the FundLocal Table.

 

[Guest]

Hi guys,

Thanks for your comments Tim.

Represented by CanAdminister in my diagram.
Awkward, isn't it? It's hard (in Access) to constrain this table to allow
only ONE medical office and ONE pension office per LocalUnion.

Ahhh, good point - one I didn't consider. However, after reading your
comments, I went ahead and built the tables. It's not hard to make those
constraints if you create a unique indexing (nulls OK) on the combination of
the LocalUnionID and RelTypeID fields of the table.

Using two
FKs to implement the one-to-many's, though, makes the Transactions harder
to control.

If I understand you right, that's why I went with an autonumber PK
(placeholder) in this table.

Probably redundant, depends on what else needs to be known about
Relationships

I don't see how this would be redundant. I put this table in for two
reasons. First, there is the possibility that in the future more fund types
might be handled. Currently only two - pensions & medical - but Strike funds
comes to mind and there might be others. Second, the 'relationship type' ID
can be used in the indexing of the junction table (so as to avoid duplicate
or multiple relationships).

I'll try to illustrate the relationships:

FundOffice RelType LocalUnion
\ 1:m |1:m /1:m
\-------- tblFundLocal-----/
|1:m
tblTransactions

With this table setup I was able to duplicate Squid's example. Here's the
data from the junction table (names replace the IDs for visual).

FundLocalID FundOfficeID LocalUnionID RelTypeID
1 A 76 Pension
2 A 76 Medical
3 A 55 Pension
4 B 55 Medical
5 A 12 Pension
6 B 23 Medical

Using a FK to reference the FundLocal (CanAdminister) table means that you
can't make a medical payment to a pension-only union.

??? Maybe I missed something somewhere, or I'm misunderstanding you. I read
the problem as a method of tracking the union's remittance of funds to a fund
office. If payouts from the fund office are done, why would a medical payout
be paid to a union that wasn't participating in the fund? Or are you
anticipating future possibilities?

That is probably a more robust requirement than the only-one-office-per-union rule which
could plausibly change in the future (what do I know about pensions? I
just pays me money every month!). This probably is the way to go. As a
purist, I would probably argue about using natural keys rather than all
these (e.g FundLocalID) placeholders, but it's not a very important point.

Yeah, no sense starting a debate eh? ;-)

All the best

Tim F

Right back at ya!

 

[Squid]

Regarding the question on the purpose of this... This database will be used
for a FundOffice to compute payments due to foreign fund offices. Funds
(pension, medical, etc) are remitted back to a local union's fund office.
Say someone from NewYork local union 978 works in the Pittsburgh
juristiction. His benefits are reported to the area he is working in
(Pittsburgh Fund Office - Steeler Country!). The Pittsburgh Fund Office
will reciprocate the money back to local union's fund office(s). The
foreign fund office can administer various funds for various local unions.

Need a beer yet?

 

[Guest]

So, in addition to all the rest of the criteria, the worker (or the employer)
pays into the appropriate fund. The funds are transferred to the appropriate
administrative FundOffice (if necessary) and then remitted to the LocalUnion
who can then pay out to the worker if the time comes. I don't think there's
enough tables. By the way, who's doing the data entry?

Hmmm. Beer sounds good. Maybe I'll have one when I get home in about 3
hours.

 

[Tim Ferguson]

I don't see how this would be redundant.

Only "probably". TBH, I'd see this as a one-column table with short text
string just to contstrain the FK in the FundLocal table to "M" or "P" (and
later "S" etc as you say). For my money, it's overload to invent a numeric
key that just needs translation into english.

??? Maybe I missed something somewhere, or I'm misunderstanding you.

The latter I think: I meant to show that I was agreeing with your model! I
have to say that I am out of my depth in terms of the actual business
domain, but I do understand most of the constraints mentioned originally.


All the best


Tim F

 

[Guest]

Only "probably". TBH, I'd see this as a one-column table with short text
string just to contstrain the FK in the FundLocal table to "M" or "P" (and
later "S" etc as you say). For my money, it's overload to invent a numeric
key that just needs translation into english.

Ah yes, now I see the redundancy (or overload) within the table. I guess
I'm just so used to having those autonumber PKs!

Have a good one!

 

[Squid]

That part of it is out of the scope of this application. We have
proprietary software that handles the employer payment and all that good
stuff.

A very non-technical person will be doing the data entry. So entering via
the datasheet would be bad, very bad (this guy is amazed by word wrap). I
am using your suggestion with the form. I think this is working with the
initial testing.