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 |
#1
|
|||
|
|||
Database Design
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 |
#2
|
|||
|
|||
"Mike" wrote in news:_JNid.368179$D%.153051@attbi_s51:
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 |
#3
|
|||
|
|||
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" wrote in message ... "Mike" wrote in news:_JNid.368179$D%.153051@attbi_s51: 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 |
#4
|
|||
|
|||
On Fri, 05 Nov 2004 23:12:07 GMT, Squid wrote:
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 |
#5
|
|||
|
|||
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 structu 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" wrote: On Fri, 05 Nov 2004 23:12:07 GMT, Squid wrote: 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 |
#6
|
|||
|
|||
On Mon, 8 Nov 2004 09:04:05 -0800, rpw
wrote: 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. tblFundOffice yes tblLocalUnion 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 |
#7
|
|||
|
|||
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. "Tim Ferguson" wrote in message newspsg576ao77bgiy5@rubens... On Mon, 8 Nov 2004 09:04:05 -0800, rpw wrote: 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. tblFundOffice yes tblLocalUnion 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 |
#8
|
|||
|
|||
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! |
#9
|
|||
|
|||
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! |
#10
|
|||
|
|||
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 |