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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |