DB Design and Relationship Questions

[tjr]

Thank you, in advance for your help. I need to determine best practices for
establishing relationships between following tables. But first:

I have clients who purchase a house, financed by one of many lending
institutions with many possible contacts and various payment request
schedules my goal is to track and maintain Payment or Draw Information as
needed as explained below.

I need to track the bank and contact info, payment request schedule, phases
in construction, payment information, pymnt. amts., balance to finish, bank
requirements for required documentation and whether we have compiled and
submitted the req. docs., dates for every pymt requition and individual
contact notes. Other data will be added as necessary.

I have the following tables:

tblHomeOwnerInformation
HOAcctNo(pk)-text {combination of lastname & street# }
HOLastName
HOFirstName
JobSiteStreet
JobSiteCity
JobSiteZip
HOResidenceStreet
HOCity
HOState
HOZip
+ other identifying info that I have to track each in its own field aptly
named according to contents (hoping all field names not important due to
special and interest level limitations).

tblBankInfo
BankID(pk)-autonum
HOAcctNo(fk)-text
BankName
BankAddress
BankCity
BankPhone
+ other info about each bank in individual fields

tblContacts
ContactID(pk)-autonum
BankID(fk)-autonum
HOAcctNo-text
BankContactPerson
BankContactPhone
+ other info about each possible contact from each bank

tblContactEvents
ContactID(pk)
HOAcctNo(pk)
ContactDateTime(pk)
BankID(fk)-number
ContactSubject
ContactNotes
ContactFollowUpDate

tblDrawInformation *I think this is poor table design – I don’t know how
many docs are requested for each different banks draw schedule*
DrawID(pk)-autonum
HOAcctNo(fk)-text
ContractAmt-currency
PreconstructionDraw-tex
PresonstructionDrawRcvd-D/T
PresonstructionDrawAmt-currency
PresonstructionDrawDocs1Requested-text
PresonstructionDrawDocs1Rcvd-Y/N
PresonstructionDrawDocs2Requested-text
PresonstructionDrawDocs2Rcvd-Y/N
*Fields go consecutively down 8 Docs (description plus rcvd y/n field) for
each of 7 possible normally 5 draws (payment request increments)
So in field list I have also fields for:
Draw1Doc1-8Requested and Rcvd;
Draw2Doc1-8Requested and Rcvd;
Etc. through Draw 7 (All possible docs listing and rcvd checkbox,too)
+ a notes field for document comments and special requirements per bank.

*-I don’t know how many docs are going to be requested and tracked
anticipate maximum of 8 different docs for each of 7 draws (pymt requests) so
I feel I have a poorly designed table.

tblDrawCompletionChecklist *Again, I think this is poor table design. (Each
draw or payment request corresponds to levels of construction completion not
all draw schedules are the same. So I have the following fields in the table
–which again, I know is poorly designed:
ID(pk)-autonum
HOAcctNo(fk)-number
Draw1Step1-text
Draw1Step1Complete-Y/N-checkbox
Draw1Step2-text
Draw1Step2Complete-Y/N
Draw1Step3-text
Draw1Step3Complete-Y/N
(I estimated approx. 10 Steps in each of seven draws so, the remaining
fields are Draw1Steps4-10 w/text description and Y/N checklists for each step
all the way through to Draw7Step10Complete. This is a lot of fields and each
one will not be used for every homeowner job completion according to the
particular draw schedule I am adhering to for that loan.)

tblDrawInfoCompletionLinkTable
DrawID(pk)
ID(pk)
*I do not know truly if I need this table. I read that for many to many
relationships I will need linking table. Many of the clients will use a
particular bank so often will have the same draw schedule for each client.
Did not want to have to recreate checklist each time so I thought that a many
to many relationship would be appropriate here.

tblHomeOwnerBankLinkTable
BankID(pk)
HOAcctNo(pk)
*Again this is linking table based solely on reading about many to many
relationships and the need for a linking table. (Many homeowners use the same
bank) with 1 caveat: they won’t all the time share the same draw schedule
(some banks offer different options)

tblBankDrawLinkTable
HOAcctNo(pk)
BankID(pk)
DrawID(pk)
*Again another linking table for a possibly necessary many to many
relationship based on the fact that each bank offers different draw schedules.

tblInspectionandMilestoneDates
InspectionRecord(pk)-autonumber
HOAcctNo(fk)-text
FoundationInspectionOrdered-D/T
FoundationInspectionComplete-Y/N
MonoslabInspectionOrdered-D/T
MonoslabInspectionComplete-Y/N
+ all other known govt. required inspections with D/T field
+ all other known govt. required insp. Completion Y/N cklst.
+ 8 misc. milestone D/T fields for the bank draw inspections
+ 8 misc milestone Y/N Accomplished Completions fields
* Again is poor table design?

tblHomeownerInspectionTable
HOAcctNo(pk)
InspectionRecord(pk)
*Again a linking table because each homeowner has the same basic inspection
schedule with the exception of the draw inspection stages (reason for misc
milestone dates)


Questions:
1.) Do I have the tables set up correctly can you advise me on more
appropriate table structure?
2.) How to establish relationship between the tables?
3.) How to ensure that each HOAcctNo has only 1 Bank and 1Draw Schedule?

Any help is greatly appreciated and I apologize for the long post.

 

[tina]

well, you are making a number of design mistakes regarding normalization and
relationships. a general rule is that any time you find yourself numbering
fields, or groups of fields - that data should be split into a "child" table
which is the "many" side of a 1:n many relationship with the parent table.
and whether a relationship is 1:1 or 1:n, the primary key of the parent
table serves as a foreign key in the child table, BUT the primary key of the
child table is *never* added to the parent table as a foreign key. in other
words, you only use one primary/foreign key pair to link two tables. below
are my suggested revisions.

note that i used the term Lenders instead of Banks, because i assume that
the financer for a particular contract may be a bank, an S&L, a credit
union, etc. and i used the term Clients instead of homeowners, because the
persons described in the table are your clients. neither change is
important - naming tables to best represent the real-world entities just
helps me understand their purpose in the database.

also note that i don't believe i have a clear understanding of exactly what
a "draw" represents, so i'm not really confident that the
tables/relationships design relating to draws is appropriate. if it doesn't
appear to you that it will support your real-world process, you're welcome
to post back with a specific explanation of just what a draw is and how
they're related to lenders and contracts - and i'll have another go at those
tables/relationships.

hth


tblClients

ClientID (primary key, Autonumber)

ClientAcctNo (you only need this field IF it corresponds to a "paper"
account number used by your organization to identify clients)

ClientFirstName

ClientLastName

ClientStreet

ClientCity

ClientState

ClientZip

(make sure that any additional fields in this table describe the *homeowner*
as an entity, and nothing else)

note: i didn't use the ClientAcctNo as the primary key for this table
because you could easily have a "John Smith at 123 Main St in ThisTown" and
a "Mary Smith at 123 High St in ThatTown (or even ThisTown)". both would
yield a ClientAcctNo of "Smith123". this consideration makes the
ClientAcctNo field unacceptable as a primary key candidate.



tblClientPhones

PhoneID (pk, Autonumber)

PhoneTypeID (foreign key from tblPhoneTypes, Number, Long Integer field
size)



tblPhoneTypes

PhoneTypeID (pk, Autonumber)

PhoneType (for instance: home, office, cell, fax)



tblJobSites

SiteID (pk, Autonumber)

SiteStreet

SiteCity

SiteZip



tblLenders

LenderID (pk, Autonumber)

LenderName

LenderStreet

LenderCity

LenderState

LenderPhone (this field should only be used for the general or "public"
phone number for the lender - not the phone number of a specific person who
works for the lender)

(again, make sure that any additional fields in this table describe the
*homeowner* as an entity, and nothing else)



tblLenderRepresentatives

RepID (pk, Autonumber)

LenderID (fk from tblLenders, Long Integer)

RepFirstName

RepLastName



tblLenderRepPhones

RepPhoneID (pk, Autonumber)

RepID (fk from tblLenderRepresentatives, Long Integer)

PhoneTypeID (fk from tblPhoneTypes, Long Integer)

PhoneNumber

PhoneComments (such as best time of day to call this number, etc)



tblPhoneTypes

PhoneTypeID (pk, Autonumber)

PhoneType (for instance: home, office, cell, fax)



tblRepContacts

ContactID (pk, Autonumber)

RepID (fk from tblLenderRepresentatives, Long Integer)

ClientID (fk from tblClients, Long Integer)

ContactDate

ContactSubject

ContactNotes

ContactFollowupDate

note: ordinarily, I might link a specific contact back to a specific
*contract*. but it's possible that you might have a single client with more
than one "open" contract, and a specific discussion may encompass those
multiple contracts. that would require entering a contact record for each
contract - from the same phone discussion. i though that recording the
discussion once per client would be cleaner.

of course, you could talk to the same lender rep about multiple clients
during one conversation, too..

at any rate, I chose to link the contact to a client, rather than a
contract. you can always change that by replacing the ClientID with
ContractID, so the table is linked to tblContracts instead of tblClients.



tblDrawSchedules

DrawSchedID (pk, Autonumber)

LenderID (fk from tblLenders, Long Integer)

(whatever fields describe a specific draw schedule used by a specific
lender. the table must *not* contain any fields about specific clients.)

frankly, i don't know anything about draw schedules. if more than one lender
might have the same draw schedule, then you should replace this table with
the following two:



tblDrawSchedules

DrawSchedID (pk, Autonumber)

(fields that describe a specific draw schedule, independent of any specific
lender or client)



tblLenderDrawSchedules

LDSchedID (pk, Autonumber)

LenderID (fk from tblLenders, Long Integer)

DrawSchedID (fk from tblDrawSchedules, Long Integer)

(this is a linking table between lenders and draw schedules. i don't use the
two foreign key fields as a combination primary key here, because the
primary key will be used to link to other tables - and I personally don't
like to use multiple fields as a foreign key.)



tblContracts

ContractID (pk, Autonumber)

ClientID (fk from tblClients, Long Integer)

SiteID (fk from tblJobSites, Long Integer)

LDSchedID (fk from tblLenderDrawSchedules, Long Integer)

ContractAmount

(any other field that describes the contract, such as the date it was signed
or goes into effect, etc.)

note: LenderID is not included as a foreign key, because each lender draw
schedule is already identified with a specific lender.



tblInspectionTypes

InspTypeID (pk, Autonumber)

InspTypeName (such as "state", "city", "lender")



tblInspections

InspID (pk, Autonumber)

InspTypeID (fk from tblInspectionTypes, Long Integer)

InspName (such as "Foundation", "Monoslab", etc)

note: i don't know if different lenders may have inspections that are
unique to that lender - something nobody else inspects for. if that's the
case, i might re-evaluate this portion of the table design.



tblContractInspections

ConInspID (pk, Autonumber)

ContractID (fk from tblContracts, Long Integer)

InspID (fk from tblInspections, Long Integer)

DateOrdered

DateCompleted (you could use a Y/N field here, rather than D/T. but
recording the completion date may be useful to you for analytical purposes,
for instance: calculating the average length of time between
ordered/completion dates for different inspections, or different types of
inspections. keep in mind that tracking when (or where, or how) something
happens - rather than just acknowledging that it did happen - always
provides more opportunities for aggregate data analysis.)

(any other fields that describe a specific inspection for a specific
contract, such as name of inspector, result of inspection, comments.)



tblDrawDocuments

DocID (pk, Autonumber)

DocName



tblDrawSteps

StepID (pk, Autonumber)

StepName (or perhaps StepDescription)



tblContractDraws

ConDrawID (pk, autonumber)

ConDrawNumber (i'm assuming that a specific draw is just a number in the
draw schedule, such as draw "3" in a seven-draw schedule)

ConDrawDate

ConDrawAmount

note: in this table, each draw for each contact would be listed once. so a
contract with seven draws would have seven records, and a contract with five
draws would have five records, etc.



tblContractDrawSteps

DrawStepID (pk, Autonumber)

ConDrawID (fk from tblContractDraws, Long Integer)

StepID (fk from tblDrawSteps)

DrawStepDateCompleted

note: in this table, each step of each draw would be listed once. so a
specific contract draw with eight steps would have eight records, and a
contract draw with ten steps would have ten records, etc.

further note: ContractID is not included as a foreign key, because each
contract draw is already identified with a specific contract.



tblContractDrawDocuments

DrawDocID (pk, Autonumber)

ConDrawID (fk from tblContractDraws, Long Integer)

DocID (fk from tblDrawDocuments, Long Integer)

DateRequested

DateReceived (you could use a Y/N field here, rather than D/T. see the notes
in tblContractInspections regarding this choice.)



relationships are:



tblClients 1:n tblClientPhones

tblPhoneTypes 1:n tblClientPhones

tblLenders 1:n tblLenderRepresentatives

tblLenderRepresentatives 1:n tblLenderRepPhones

tblPhoneTypes 1:n tblLenderRepPhones

tblLenderRepresentatives 1:n tblRepContacts

tblClients 1:n tblRepContacts

tblLenders 1:n tblDrawSchedules

(the above is for the "one-table" option for draw schedules)

tblLenders 1:n tblLenderDrawSchedules

tblDrawSchedules 1:n tblLendDrawSchedules

(the above is for the "two-table" option for draw schedules)

(note: use the one-table option OR the two-table option, not both)

tblClients 1:n tblContracts

tblJobSites 1:n tblContracts

tblLenderDrawSchedules 1:n tblContracts

tblInspectionTypes 1:n tblInspections

tblContracts 1:n tblContractInspections

tblInspections 1:n tblContractInspections

tblContractDraws 1:n tblContractDrawSteps

tblDrawSteps 1:n tblContractDrawSteps

tblContractDraws 1:n tblContractDrawDocuments

tblDrawDocuments 1:n tblContractDrawDocuments

 

[tjr]

Great Response-I truly appreciate your time and attention to my problem. I'm
new to Access but have read a few lengthy books on the subject, just seemed
such an ambitious task for a 'newbie' I was overwhelmed and still looking at
it from a paper document collection standpoint. Now I have too much paper and
am hoping to simplify the job and increase tracking ability. I will redo all
table designs this weekend and post back with how I did. And you seemed
right on with draws, my apology for lack of better explanation a draw is
where the builder gets a predetermined amount of money during different
predetermined phases of construction and each time you request a draw you
have to submit the documents that are requested by (you were right)the lender
.

One question left for now. I designed a form based on my admitedly
ridiculous table structure to help simply data entry of all things discussed
here per client. I structured the form so that all fields from each and every
table were present on the form starting with HomeOwnerAcct# hoping that I
would be able to enter bank and draw information in one place at the same
time.

With the current (bad) setup when I tab to the bank or draw information
areas on the form my keyboard acts like a piano instead of a keyboard and
just beeps at me. I get an error message or 2 of sorts about no matching
records, Ive even tried to open bank table and enter HomeOwnerAcctNo again
then go back to the form (form testing-newbie style) and no go, still can not
enter info about that clients bank or draw.

Will the new proposed arrangement fix the data entry on a newly designed
form or is there something that I am obviously missing here - yes I know I
have a lot to learn take it easy please-Im in a tough spot in a great NEW job
and would like to keep it by being able to track this stuff accurately.
Afterall, I did read Access 2003 INSIDE OUT and am 1/4 done with Designing
Acess Apps. to get a better grip on this stuff.

I will post in forms discussion group with future questions about forms but
it so closely relates to the previous post I though there would be
forgiveness to inappropriate topic, please?

 

[tina]

let me start by saying that i noticed i listed tblPhoneTypes twice - you
only need that table once! it serves as a supporting table for both
tblClientPhones *and* tblLenderRepPhones. also, sorry about the
double-spacing in the tables list; i wrote all this out in MS Word and
copy/pasted it into my post, so apparently the email program decided it
should be double-spaced - aarrgghhh!

first, i'd say toss out your present form(s). start fresh on form-building
when you have the table/relationships correctly structured.
second, i'd say toss out the idea of

all fields from each and every table...present on the form

over the life of a contract, most of the data entry will be in
tblContractInspections, tblContractDraws, tblContractDrawSteps, and
tblContractDrawDocuments. i might suggest a mainform/subform setup, with the
main form bound to tblContracts and a TabControl with pages to hold various
subforms, such as a subform bound to tblContractInspections, another subform
bound to tblContractDraws and containing sub/subforms bound to
tblContractDrawSteps and tblContractDrawDocuments. note that usually
mainform (parent)/subform (child) relationships mirror the relationships of
parent/child table relationships - including using the primary/foreign key
fields to link the two.

don't be afraid to use separate forms for different phases of the life of
the contract, if that seems appropriate. a lot of your decisions on how to
set up your data entry forms should be based on your users' workflow, or at
least taking that heavily into consideration. and definitely use separate
forms for each of the "supporting" tables, such as tblPhoneTypes,
tblDrawDocuments, tblDrawSteps, etc. i'd probably bind tblLenders to a main
form with the Lender data on the first page of a TabControl, tblLenderReps
bound to a subform on the second page of the TabControl, with a sub/subform
for tblLenderRepPhones.

this is a big job you're undertaking, not only from the table/relationship
structure (which is fairly straightforward), but also from the data entry
standpoint. building a user interface is something of an art in itself.
form/subform setups, especially with nested subforms, can be tricky until
you get the hang of them (and sometimes even then). suggest you start by
laying out the "life" of a contract on paper - from the initial client
intake data all the way through to completion of the contract. determine
when specific data will be "acquired" and whether it's "repeating" data
(like draws) or one-time data (like client info, or contract setup info).
determine what existing data your user needs to "see" at different points,
as well as what data s/he needs to enter. for instance, does the user need
to see the lender's address when s/he is entering the completion data for
step 6 of a specific contract draw? probably not. use this information to
decide how many forms you need, and how to set them up, to best facilitate
easy, efficient data entry by the user. then sketch out the forms to get a
least a rough idea of the layout you want to use, including TabControls and
subforms. in the long run, this will be faster than just jumping into form
design willy-nilly (just like it's more efficient to build a house from
blueprints rather than off the top of your head <g>).

hth

 

[tjr]

Thank you again. No biggie on the tblPhoneTypes, recognized that I only
needed one and made only 1 last night (that means I'm learning I am adding
relationships now and need clarification on one suggestion you made Tina:

Question 1: In the relationship: tblClients 1:n tblClientPhones which fields
am I 'dragging and dropping to? (how to create the relationship?) I don't see
a common field between the 2 tables and I have been assuming all along that
you need a common name to maintain parent/child relationship. Please comment
and help me understand-I am continuing to add other relationships as advised
where it is obvious and have one more question at the end of this table
explanation...

Fields in tblClients: ClientID(pk,autonum), ClientFirstName, ClientLastName,
ClientStreet.

Fields in tblClientPhones: PhoneID(pk, autonum), PhoneTypeID(fk from
tblPhoneTypes, num-longint.)

Question 2: When I started this I had all present clients in a spreadsheet
so I imported the data to the 'main table' now tblClients (was homeowners) so
I had 66 records in the main table. That number has grown exponenentially
since I undertook this endeavor so I really need to get a grip on this fast!

Do I Import the records to all tables so all tables have 66 records from the
start or can I add records as I enter the data that could not be imported?

I know I am not posing this question correctly but my question revolves
around my insecurities in not really knowing that all records are going to
stay related and unsure about having parent table with 66 records then adding
for instance the inspections for the 3rd draw of the 45th record when all
inspections for all clients upto 45th record have not been entered yet (am
entering as I am dealing with each phase) -How am I going to know that this
record is really for the 45th clients 3 draw inspections?- Can it be done
this way and is there a way you can explain it to me when even I have trouble
understanding how to pose the question-am I laughing now at me or with you???
I think it will become clear once I use subforms (played a little prior to
one long form idea) I was just adding records to a subform at first and was
not sure whether they were related properly thought then that 1 homeowner
should have only one record in each table then. But I have a feeling that I
am going to like your 7 draw 7 records theory, I am just stuck on the 1st
question about the relationship and will be making the suggested revisions
with great appreciation as we correspond.

Much 'ado' about...a great job- thanks mucho for all the help!

 

[tjr]

Answered my own question? I added the ClientID field to ClientPhones and
created a 1:n from ClientID in tblClients to ClientID in tblClientPhones?

 

[tina]

okay, i say your second post saying that you solved the "client phones"
question. that was an oops on my part in leaving out the ClientID as a
foreign key in tblClientPhones. but it turned out to be a good learning
experience for you, because 1) you recognized that there was no appropriate
field in tblClientPhones to act as the foreign key, and 2) you correctly
chose the primary key field ClientID from tblClients, to add to
tblClientPhones as the foreign key field. and then you set up the
relationship correctly: *from* tblClients *to* tblClientPhones. good job!


one note on this subject:

I have been assuming all along that
you need a common name to maintain parent/child relationship

you don't need a common *name*, you need a common *field*. don't get me
wrong, the ClientID key *value* from the parent record in tblClients must be
saved to the foreign key field in a record in tblClientPhones, so that the
phone record in the child table is linked to a specific client record in the
parent table. and the primary/foreign key pair must be the same data type,
as Text : Text, DateTime : DateTime....with the anomaly being Autonumber :
Number, Long Integer.
but the primary/foreign key pair do *not* have to have the same fieldnames.
in fact, in my databases, no matter how many tables i have, every single
fieldname in every single table is unique. (you don't have to do that
either, but personally i find it easier to work with the fields in queries,
expressions, and code, when there are no duplicate names.) if i were naming
the fields in tblClients and tblClientPhones for my own database, they'd
probably be

tblClients
cID (pk)
cFirst
cMiddle
cLast
cStreet
cCity
cState
cZip

tblClientPhones
cpID (pk)
cp_ptypeID (fk from tblPhoneTypes)
cpNumber
cpComments

my naming convention makes sense to me, but probably not to anyone else, so
i stick with bare-bones fieldnames in newsgroup posts so as not to confuse
people unnecessarily.

additional comments inline.

Question 2: When I started this I had all present clients in a spreadsheet
so I imported the data to the 'main table' now tblClients (was homeowners) so
I had 66 records in the main table. That number has grown exponenentially
since I undertook this endeavor so I really need to get a grip on this fast!

Do I Import the records to all tables so all tables have 66 records from the
start or can I add records as I enter the data that could not be imported?

all of your tables will *not* have 66 records, so there's no point in trying
to achieve or maintain that "balance". since all these tables are involved
in 1:n relationships, if you ever have any two tables with the exact same
number of records, that will only be a coincidence.

I know I am not posing this question correctly but my question revolves
around my insecurities in not really knowing that all records are going to
stay related and unsure about having parent table with 66 records then adding
for instance the inspections for the 3rd draw of the 45th record when all
inspections for all clients upto 45th record have not been entered yet (am
entering as I am dealing with each phase)

that doesn't matter. if you're populating the tables with pre-existing data,
and if it makes you more comfortable to enter all records' first draw data,
then all records' second draw data, etc - then that's fine, do it that way.
but you can just as easily enter all the available data for one client, then
all the available data for the next client, etc.

-How am I going to know that this
record is really for the 45th clients 3 draw inspections?

if you've set your tables/relationships up correctly, and are adding the
data via a correctly related form/subform setup, then the record of the
third draw inspection for client 45 will automatically be linked back to the
third draw for client 45's contract, and so back to client 45's contract,
and so indirectly (but clearly and absolutely) linked to client 45.

- Can it be done
this way and is there a way you can explain it to me when even I have trouble
understanding how to pose the question-am I laughing now at me or with you???
I think it will become clear once I use subforms (played a little prior to
one long form idea) I was just adding records to a subform at first and was
not sure whether they were related properly thought then that 1 homeowner
should have only one record in each table then. But I have a feeling that I
am going to like your 7 draw 7 records theory, I am just stuck on the 1st
question about the relationship and will be making the suggested revisions
with great appreciation as we correspond.

here's how i would approach populating your database. start with the
"generic" supporting tables:

tblPhoneTypes
tblLenders, then tblLenderRepresentatives, then tblLenderRepPhones (remember
the mainform/subform/subsubform setup i mentioned in previous post)
tblDrawSchedules, then tblLenderDrawSchedules
tblInspectionTypes
tblInspections
tblDrawDocuments
tblDrawSteps

now you're ready to start entering the "real" data about your clients and
their contracts. start with

tblClients, then tblClientPhones
tblJobSites (this is just a listing of physical descriptions of sites)
tblContracts
tblRepContacts

now you can enter the rest of the data, which is all related to specific
contracts:

tblContractInspections
tblContractDraws, then tblContractDrawSteps
tblContractDrawDocuments

once you begin entering "real" data, it's up to you whether you go through a
single contract and enter all the *available* data (for instance, if the
third draw on a contract hasn't been made yet, that data is not available,
obviously), or go through all the contracts and enter the first inspection
where available, then back through all the contracts and enter the second
inspection where available, etc.

hth

 

[Salvm9]

Hello Tina All I want Now That I Find That You re' So Smart Is You Phone
Number,And If You Have A Boyfriend.