A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Database Design



 
 
Thread Tools Display Modes
  #11  
Old November 10th, 2004, 02:04 AM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

On Tue, 9 Nov 2004 11:08:02 -0800, rpw
wrote:

tblRelationshipType

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


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.


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.


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

--
!-- --
  #12  
Old November 10th, 2004, 02:47 AM
rpw
external usenet poster
 
Posts: n/a
Default



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!

  #13  
Old November 10th, 2004, 04:19 AM
Squid
external usenet poster
 
Posts: n/a
Default

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.


"rpw" wrote in message
...
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.

"Squid" wrote:

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?

"rpw" wrote in message
...
Hi guys,

Thanks for your comments Tim.

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.

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.

tblRelationshipType
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

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.

??? 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!






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Design for Inventory Control A P General Discussion 2 October 25th, 2004 12:51 PM
Images in a database Franz General Discussion 10 October 7th, 2004 09:35 AM
You do not have exclusive access... ERROR Robin General Discussion 1 July 6th, 2004 01:18 AM
Database design help Kirk Database Design 3 May 4th, 2004 05:31 AM


All times are GMT +1. The time now is 07:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.