Re: Table relationship problems
From: Duane Hookom (duanehookom_at_NoSpamHotmail.com)
Date: 11/24/04
- Next message: Tim Ferguson: "Re: "groups" in table and text field size"
- Previous message: Duane Hookom: "Re: DLookup()"
- In reply to: Don: "Re: Table relationship problems"
- Next in thread: Bruce: "Re: Table relationship problems"
- Reply: Bruce: "Re: Table relationship problems"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Nov 2004 11:20:41 -0600
I appologize if I seemed flippant. I was very serious about your table
structure. I created a loan processing application for a client that was
more normalized. They "stages" of the process were not hard-coded into
fields. There was a table of stages
tblStages
StageID
StageTitle
StageDescription
StageSequence
Each loan was included in a table of loans
tblLoans
LoanID
BorFirstName
BorLastName
....
Each stage of a particular loan was stored in a single record in a
"junction" table:
tblLoanStages
LoanStageID
LoanID relates to tblLoans.LoanID
StageID relates to tblStages.StageID
StatusID relates to tblStatus.StatusID
StatusDate ....
This system allowed my client to add or delete stages per loan and provided
the required flexibility.
-- Duane Hookom MS Access MVP -- "Don" <diverdon99@yahoo.co.uk> wrote in message news:M86dnTO8f4k6DTncRVnyjA@eclipse.net.uk... > Bruce, > > tblProgress does have many date fields as the loan is required to be > tracked through each stage of application to completion. There can only > be (ever) 1 loan per client as this is an interest free Goverment > initiative and various EU regulations exclude additional loans. > To my mind this is a perfect example of a 1 to 1 link but I am willing to > stand corrected if it can be configured in a more sensible way, indeed I > would welcome any other constructive ideas. > I want to be able to construct tables in a more orderly fashion, but > currently lack the knowledge to do so, hence the reasons to ask questions > in newsgroups such as this. > Client to Supplier is 1 to many and requires a few words of explanation. > The loans are given for a specific project repayable over a fixed term. > The project may have 1 or more suppliers and the loan is (part) paid on > receipt of a copy invoice. It is not important which supplier provides > the invoice. The only purpose of this table is to identify that more than > 1 supplier is involved and no other details of the supplier are required. > No loan starts repayment until all invoices are in and complete. This part > is a manual process. > > Client to Contacts is 1 to many for self evident reasons (I think) > > In summary the whole process from conception to final repayment of the > loan cannot step outside of the boundaries as dictated by the fields > required. The point of splitting the (Main) Client table into 4 was to > separate the 4 tabs on the form into logical groups and linking on a 1 to > 1 basis to reassemble the data. > > Because many financial databases including this one start of as an Excel > workbook I have always found it difficult to identify the separate > entities. Some are obvious but ususally the tables are still well over 20 > or so fields. > I have the luxury of not having to import existing data as it is small > enough to be retyped, so I was hoping to construct this as a more sensible > data structure. > Again the nature of this transaction is that the terms (and fields) cannot > be varied. > > I hope the background helps to explain some of the odd characteristics of > the structure as I have given and again I would welcome a more detailed > direction in which to go. > Many thanks > > Don > > > >>I disagree that it was a flippant answer. I understand your frustration, >>and >> perhaps the humor was not to your taste, but there are some valuable >> suggestions in that short answer. tblProgress seems to have multiple >> date >> fields, which could be a problem if you want to add another item. Duane >> is >> saying (I think) that it would generally be better to link to a >> tblProgressDate (or whatever), where each record contains a date, the >> associated event (credit check start, etc.), and whatever else is needed. >> Later, if you want to add "credit check on hold" you can more easily do >> so. >> For my own part, is client to supplier really one to many, or can each >> supplier be associated with many clients? >> >> "Don" wrote: >> >>> >>> >>> Duane, >>> >>> Its not that I disagree with you but I am hoping to find an answer here >>> rather than a flippant comment. From your past posts it is obvious that >>> you >>> are expert in this product but many of us are not. Hence we ask for >>> help. >>> >>> This is a financial database and one of the requiremnts is that data is >>> not >>> disjointed unnecessarily. If you have any constructive ideas they would >>> be >>> most welcome, as in my opinion this is not an isolated problem with >>> those >>> those of us less talented than you. >>> >>> I have read numerous books including your ebook on database design and >>> so >>> far have not found an answer. >>> >>> Don >>> >>> >>> > In the words of fellow MVP Jeff Boyce "you are committing >>> > spreadsheet". >>> > Multiple date and/or boolean fields should generally be normalized >>> > into >>> > multiple records, not multiple similar fields. I wouldn't spend any >>> > additional time attempting to make this work without re-evaluating >>> > your >>> > table structure. >>> > >>> >>> >>> > >
- Next message: Tim Ferguson: "Re: "groups" in table and text field size"
- Previous message: Duane Hookom: "Re: DLookup()"
- In reply to: Don: "Re: Table relationship problems"
- Next in thread: Bruce: "Re: Table relationship problems"
- Reply: Bruce: "Re: Table relationship problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
