Best practice for table design

Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:
Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially is
tblWorkOrders. At some point, further details MAYbe added to tblInspection
or tblIssuer. I think that the lookup tables speak for themselves. Thank
you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in message
...

Debra,

Mail me your back acct details, IOU you ten bucks! Playing with the odds
doesn't always pay.

Nikos

Debra Farnham wrote:

I can provide more exact details (i.e. my current table design if

necessary)

.... the way I'm seeing it though, its going to be difficult to avoid

empty

fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
...


The database is maintaining a specific type of permit .... some details
apply to some of the permits and not others. (Pricing and charges for
example only apply to some of the services covered by the permit but not

all


services apply to all permits).

There are no standard rates for any of the services that the permit may
cover.

Some of the details will be added in long after the permit is issued

(i.e.

who installed it, who inspected it, the labour and material costs, etc.)

It is rare that all of the details will apply to all of the permits.

Some customers will do installations themselves and will be responsible

for


their own costs in which case the permit will only allow for the work to

be


completed and includes such details as address, permit applicant and

permit


number.

I hope this provides the detail necessary to answer my initial query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in message
.. .


Debra,

It's impossible for one to comment on a design they haven't seen at

all!

However, ten bucks says "six one to one relationships" with "blank
fields in some of them" is an unnormalized database. If you post some
details of what you are trying to achieve (the overall concept) and

your

current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:


Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would keep the

database


normalized in that it would eliminate blank fields and keep related

data


together. Now that I have had time to reflect on it, I'm wondering if
perhaps I should have created one huge table. Even with the tables

nicely


broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have designed this
database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them out
prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
...
Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:
Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially is
tblWorkOrders. At some point, further details MAYbe added to
tblInspection
or tblIssuer. I think that the lookup tables speak for themselves.
Thank
you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in message
...

Debra,

Mail me your back acct details, IOU you ten bucks! Playing with the odds
doesn't always pay.

Nikos

Debra Farnham wrote:

I can provide more exact details (i.e. my current table design if

necessary)

.... the way I'm seeing it though, its going to be difficult to avoid

empty

fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
...


The database is maintaining a specific type of permit .... some
details
apply to some of the permits and not others. (Pricing and charges for
example only apply to some of the services covered by the permit but
not

all


services apply to all permits).

There are no standard rates for any of the services that the permit
may
cover.

Some of the details will be added in long after the permit is issued

(i.e.

who installed it, who inspected it, the labour and material costs,
etc.)

It is rare that all of the details will apply to all of the permits.

Some customers will do installations themselves and will be
responsible

for


their own costs in which case the permit will only allow for the work
to

be


completed and includes such details as address, permit applicant and

permit


number.

I hope this provides the detail necessary to answer my initial query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in message
.. .


Debra,

It's impossible for one to comment on a design they haven't seen at

all!

However, ten bucks says "six one to one relationships" with "blank
fields in some of them" is an unnormalized database. If you post some
details of what you are trying to achieve (the overall concept) and

your

current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:


Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would keep the

database


normalized in that it would eliminate blank fields and keep related

data


together. Now that I have had time to reflect on it, I'm wondering
if
perhaps I should have created one huge table. Even with the tables

nicely


broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have designed
this
database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

Debra,

You can't possibly be *that* blonde if you're using Access:-) I also get
the wrong message sometimes and I'm not at all blonde, I assure you, so
it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You might
find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:
Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them out
prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
...

Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:

Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially is
tblWorkOrders. At some point, further details MAYbe added to

tblInspection

or tblIssuer. I think that the lookup tables speak for themselves.

Thank

you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in message
...


Debra,

Mail me your back acct details, IOU you ten bucks! Playing with the odds
doesn't always pay.

Nikos

Debra Farnham wrote:


I can provide more exact details (i.e. my current table design if

necessary)


.... the way I'm seeing it though, its going to be difficult to avoid

empty


fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
. ..



The database is maintaining a specific type of permit .... some

details

apply to some of the permits and not others. (Pricing and charges for
example only apply to some of the services covered by the permit but

not

all



services apply to all permits).

There are no standard rates for any of the services that the permit

may

cover.

Some of the details will be added in long after the permit is issued

(i.e.


who installed it, who inspected it, the labour and material costs,

etc.)

It is rare that all of the details will apply to all of the permits.

Some customers will do installations themselves and will be

responsible

for



their own costs in which case the permit will only allow for the work

to

be



completed and includes such details as address, permit applicant and

permit



number.

I hope this provides the detail necessary to answer my initial query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in message
.. .



Debra,

It's impossible for one to comment on a design they haven't seen at

all!


However, ten bucks says "six one to one relationships" with "blank
fields in some of them" is an unnormalized database. If you post some
details of what you are trying to achieve (the overall concept) and

your


current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:



Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would keep the

database



normalized in that it would eliminate blank fields and keep related

data



together. Now that I have had time to reflect on it, I'm wondering

if

perhaps I should have created one huge table. Even with the tables

nicely



broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have designed

this

database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

I know I'll use it from time to time. Although I know I should, I don't
usually design on paper except maybe for a sketch or two when I'm getting
started, and I use Print Screen sometimes when I need to keep Relationships
in front of me or something like that.

"Nikos Yannacopoulos" wrote:

Debra,

You can't possibly be *that* blonde if you're using Access:-) I also get
the wrong message sometimes and I'm not at all blonde, I assure you, so
it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You might
find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:
Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them out
prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
...

Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:

Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially is
tblWorkOrders. At some point, further details MAYbe added to

tblInspection

or tblIssuer. I think that the lookup tables speak for themselves.

Thank

you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in message
...


Debra,

Mail me your back acct details, IOU you ten bucks! Playing with the odds
doesn't always pay.

Nikos

Debra Farnham wrote:


I can provide more exact details (i.e. my current table design if

necessary)


.... the way I'm seeing it though, its going to be difficult to avoid

empty


fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
. ..



The database is maintaining a specific type of permit .... some

details

apply to some of the permits and not others. (Pricing and charges for
example only apply to some of the services covered by the permit but

not

all



services apply to all permits).

There are no standard rates for any of the services that the permit

may

cover.

Some of the details will be added in long after the permit is issued

(i.e.


who installed it, who inspected it, the labour and material costs,

etc.)

It is rare that all of the details will apply to all of the permits.

Some customers will do installations themselves and will be

responsible

for



their own costs in which case the permit will only allow for the work

to

be



completed and includes such details as address, permit applicant and

permit



number.

I hope this provides the detail necessary to answer my initial query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in message
.. .



Debra,

It's impossible for one to comment on a design they haven't seen at

all!


However, ten bucks says "six one to one relationships" with "blank
fields in some of them" is an unnormalized database. If you post some
details of what you are trying to achieve (the overall concept) and

your


current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:



Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would keep the

database



normalized in that it would eliminate blank fields and keep related

data



together. Now that I have had time to reflect on it, I'm wondering

if

perhaps I should have created one huge table. Even with the tables

nicely



broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have designed

this

database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

Hi Bruce!

Good to know (a) it may be useful to someone besides myself, and (b) I'm
not alone in breaching one of the basic rules!

Regards,
Nikos

BruceM wrote:
I know I'll use it from time to time. Although I know I should, I don't
usually design on paper except maybe for a sketch or two when I'm getting
started, and I use Print Screen sometimes when I need to keep Relationships
in front of me or something like that.

"Nikos Yannacopoulos" wrote:


Debra,

You can't possibly be *that* blonde if you're using Access:-) I also get
the wrong message sometimes and I'm not at all blonde, I assure you, so
it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You might
find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:

Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them out
prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
...


Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:


Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially is
tblWorkOrders. At some point, further details MAYbe added to

tblInspection


or tblIssuer. I think that the lookup tables speak for themselves.

Thank


you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in message
.. .



Debra,

Mail me your back acct details, IOU you ten bucks! Playing with the odds
doesn't always pay.

Nikos

Debra Farnham wrote:



I can provide more exact details (i.e. my current table design if

necessary)



.... the way I'm seeing it though, its going to be difficult to avoid

empty



fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
. ..




The database is maintaining a specific type of permit .... some

details


apply to some of the permits and not others. (Pricing and charges for
example only apply to some of the services covered by the permit but

not


all




services apply to all permits).

There are no standard rates for any of the services that the permit

may


cover.

Some of the details will be added in long after the permit is issued

(i.e.



who installed it, who inspected it, the labour and material costs,

etc.)


It is rare that all of the details will apply to all of the permits.

Some customers will do installations themselves and will be

responsible


for




their own costs in which case the permit will only allow for the work

to


be




completed and includes such details as address, permit applicant and

permit




number.

I hope this provides the detail necessary to answer my initial query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in message
...




Debra,

It's impossible for one to comment on a design they haven't seen at

all!



However, ten bucks says "six one to one relationships" with "blank
fields in some of them" is an unnormalized database. If you post some
details of what you are trying to achieve (the overall concept) and

your



current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:




Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would keep the

database




normalized in that it would eliminate blank fields and keep related

data




together. Now that I have had time to reflect on it, I'm wondering

if


perhaps I should have created one huge table. Even with the tables

nicely




broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have designed

this


database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

Hi Nikos

I agree with Bruce on his response.. It does appear to be something quite
useful. Although, I will likely continue to design on paper first. I teach
the stuff and I suppose it's best to lead by example.

Sure wish someone could help with my initial problem though. I am still
seriously doubting the design.

Thanks again

Debra


"Nikos Yannacopoulos" wrote in message
...
Hi Bruce!

Good to know (a) it may be useful to someone besides myself, and (b) I'm
not alone in breaching one of the basic rules!

Regards,
Nikos

BruceM wrote:
I know I'll use it from time to time. Although I know I should, I don't
usually design on paper except maybe for a sketch or two when I'm
getting
started, and I use Print Screen sometimes when I need to keep
Relationships
in front of me or something like that.

"Nikos Yannacopoulos" wrote:


Debra,

You can't possibly be *that* blonde if you're using Access:-) I also get
the wrong message sometimes and I'm not at all blonde, I assure you, so
it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You might
find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:

Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them
out
prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
...


Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:


Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially is
tblWorkOrders. At some point, further details MAYbe added to

tblInspection


or tblIssuer. I think that the lookup tables speak for themselves.

Thank


you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in message
.. .



Debra,

Mail me your back acct details, IOU you ten bucks! Playing with the
odds
doesn't always pay.

Nikos

Debra Farnham wrote:



I can provide more exact details (i.e. my current table design if

necessary)



.... the way I'm seeing it though, its going to be difficult to
avoid

empty



fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
. ..




The database is maintaining a specific type of permit .... some

details


apply to some of the permits and not others. (Pricing and charges
for
example only apply to some of the services covered by the permit
but

not


all




services apply to all permits).

There are no standard rates for any of the services that the
permit

may


cover.

Some of the details will be added in long after the permit is
issued

(i.e.



who installed it, who inspected it, the labour and material costs,

etc.)


It is rare that all of the details will apply to all of the
permits.

Some customers will do installations themselves and will be

responsible


for




their own costs in which case the permit will only allow for the
work

to


be




completed and includes such details as address, permit applicant
and

permit




number.

I hope this provides the detail necessary to answer my initial
query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in
message
...




Debra,

It's impossible for one to comment on a design they haven't seen
at

all!



However, ten bucks says "six one to one relationships" with
"blank
fields in some of them" is an unnormalized database. If you post
some
details of what you are trying to achieve (the overall concept)
and

your



current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:




Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would keep
the

database




normalized in that it would eliminate blank fields and keep
related

data




together. Now that I have had time to reflect on it, I'm
wondering

if


perhaps I should have created one huge table. Even with the
tables

nicely




broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have designed

this


database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

Debra,

I believe I speak for both Bruce and myself when I say neither of us
meant you should no longer design on paper; we do need a good example,
rare as it may be!

As for your original problem, I would love to help if I can, but it
would require me to understand the process you are modeling, which I
fear I am far from. Do you believe it's worth your time to try and put
it all down on "paper", while still not guaranteed to get any useful
feedback?

Regards,
Nikos

Debra Farnham wrote:
Hi Nikos

I agree with Bruce on his response.. It does appear to be something quite
useful. Although, I will likely continue to design on paper first. I teach
the stuff and I suppose it's best to lead by example.

Sure wish someone could help with my initial problem though. I am still
seriously doubting the design.

Thanks again

Debra


"Nikos Yannacopoulos" wrote in message
...

Hi Bruce!

Good to know (a) it may be useful to someone besides myself, and (b) I'm
not alone in breaching one of the basic rules!

Regards,
Nikos

BruceM wrote:

I know I'll use it from time to time. Although I know I should, I don't
usually design on paper except maybe for a sketch or two when I'm

getting

started, and I use Print Screen sometimes when I need to keep

Relationships

in front of me or something like that.

"Nikos Yannacopoulos" wrote:



Debra,

You can't possibly be *that* blonde if you're using Access:-) I also get
the wrong message sometimes and I'm not at all blonde, I assure you, so
it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You might
find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:


Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them

out

prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
. ..



Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:



Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially is
tblWorkOrders. At some point, further details MAYbe added to

tblInspection



or tblIssuer. I think that the lookup tables speak for themselves.

Thank



you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in message
.. .




Debra,

Mail me your back acct details, IOU you ten bucks! Playing with the

odds

doesn't always pay.

Nikos

Debra Farnham wrote:




I can provide more exact details (i.e. my current table design if

necessary)




.... the way I'm seeing it though, its going to be difficult to

avoid

empty




fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
l...





The database is maintaining a specific type of permit .... some

details



apply to some of the permits and not others. (Pricing and charges

for

example only apply to some of the services covered by the permit

but

not



all





services apply to all permits).

There are no standard rates for any of the services that the

permit

may



cover.

Some of the details will be added in long after the permit is

issued

(i.e.




who installed it, who inspected it, the labour and material costs,

etc.)



It is rare that all of the details will apply to all of the

permits.

Some customers will do installations themselves and will be

responsible



for





their own costs in which case the permit will only allow for the

work

to



be





completed and includes such details as address, permit applicant

and

permit





number.

I hope this provides the detail necessary to answer my initial

query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in

message

bl...





Debra,

It's impossible for one to comment on a design they haven't seen

at

all!




However, ten bucks says "six one to one relationships" with

"blank

fields in some of them" is an unnormalized database. If you post

some

details of what you are trying to achieve (the overall concept)

and

your




current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:





Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would keep

the

database





normalized in that it would eliminate blank fields and keep

related

data





together. Now that I have had time to reflect on it, I'm

wondering

if



perhaps I should have created one huge table. Even with the

tables

nicely





broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have designed

this



database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

Nikos ... you make a very valid point.

I read a thread of 70+ articles about the very subject last night and have
decided to re-design. As most of the fields that would likely be nulls are
yes/no I decided to put all data from the one to one relationships into one
table.

I'm not happy about all the blank fields, but at least now it's easily
updateable, querying is less effort as is reporting.

Thanks again for taking the time to sift thru this.

Debra

"Nikos Yannacopoulos" wrote in message
...
Debra,

I believe I speak for both Bruce and myself when I say neither of us
meant you should no longer design on paper; we do need a good example,
rare as it may be!

As for your original problem, I would love to help if I can, but it
would require me to understand the process you are modeling, which I
fear I am far from. Do you believe it's worth your time to try and put
it all down on "paper", while still not guaranteed to get any useful
feedback?

Regards,
Nikos

Debra Farnham wrote:
Hi Nikos

I agree with Bruce on his response.. It does appear to be something
quite
useful. Although, I will likely continue to design on paper first. I
teach
the stuff and I suppose it's best to lead by example.

Sure wish someone could help with my initial problem though. I am still
seriously doubting the design.

Thanks again

Debra


"Nikos Yannacopoulos" wrote in message
...

Hi Bruce!

Good to know (a) it may be useful to someone besides myself, and (b) I'm
not alone in breaching one of the basic rules!

Regards,
Nikos

BruceM wrote:

I know I'll use it from time to time. Although I know I should, I
don't
usually design on paper except maybe for a sketch or two when I'm

getting

started, and I use Print Screen sometimes when I need to keep

Relationships

in front of me or something like that.

"Nikos Yannacopoulos" wrote:



Debra,

You can't possibly be *that* blonde if you're using Access:-) I also
get
the wrong message sometimes and I'm not at all blonde, I assure you,
so
it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You
might
find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:


Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them

out

prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
. ..



Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:



Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially
is
tblWorkOrders. At some point, further details MAYbe added to

tblInspection



or tblIssuer. I think that the lookup tables speak for themselves.

Thank



you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in
message
.. .




Debra,

Mail me your back acct details, IOU you ten bucks! Playing with
the

odds

doesn't always pay.

Nikos

Debra Farnham wrote:




I can provide more exact details (i.e. my current table design if

necessary)




.... the way I'm seeing it though, its going to be difficult to

avoid

empty




fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
l...





The database is maintaining a specific type of permit .... some

details



apply to some of the permits and not others. (Pricing and
charges

for

example only apply to some of the services covered by the permit

but

not



all





services apply to all permits).

There are no standard rates for any of the services that the

permit

may



cover.

Some of the details will be added in long after the permit is

issued

(i.e.




who installed it, who inspected it, the labour and material
costs,

etc.)



It is rare that all of the details will apply to all of the

permits.

Some customers will do installations themselves and will be

responsible



for





their own costs in which case the permit will only allow for the

work

to



be





completed and includes such details as address, permit applicant

and

permit





number.

I hope this provides the detail necessary to answer my initial

query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in

message

bl...





Debra,

It's impossible for one to comment on a design they haven't
seen

at

all!




However, ten bucks says "six one to one relationships" with

"blank

fields in some of them" is an unnormalized database. If you
post

some

details of what you are trying to achieve (the overall concept)

and

your




current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:





Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would
keep

the

database





normalized in that it would eliminate blank fields and keep

related

data





together. Now that I have had time to reflect on it, I'm

wondering

if



perhaps I should have created one huge table. Even with the

tables

nicely





broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have
designed

this



database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

Now there's a brave girl! Actually you are setting one more good
example... I haven't seen people decide to redesign very often.

Good luck!

Debra Farnham wrote:
Nikos ... you make a very valid point.

I read a thread of 70+ articles about the very subject last night and have
decided to re-design. As most of the fields that would likely be nulls are
yes/no I decided to put all data from the one to one relationships into one
table.

I'm not happy about all the blank fields, but at least now it's easily
updateable, querying is less effort as is reporting.

Thanks again for taking the time to sift thru this.

Debra

"Nikos Yannacopoulos" wrote in message
...

Debra,

I believe I speak for both Bruce and myself when I say neither of us
meant you should no longer design on paper; we do need a good example,
rare as it may be!

As for your original problem, I would love to help if I can, but it
would require me to understand the process you are modeling, which I
fear I am far from. Do you believe it's worth your time to try and put
it all down on "paper", while still not guaranteed to get any useful
feedback?

Regards,
Nikos

Debra Farnham wrote:

Hi Nikos

I agree with Bruce on his response.. It does appear to be something

quite

useful. Although, I will likely continue to design on paper first. I

teach

the stuff and I suppose it's best to lead by example.

Sure wish someone could help with my initial problem though. I am still
seriously doubting the design.

Thanks again

Debra


"Nikos Yannacopoulos" wrote in message
...


Hi Bruce!

Good to know (a) it may be useful to someone besides myself, and (b) I'm
not alone in breaching one of the basic rules!

Regards,
Nikos

BruceM wrote:


I know I'll use it from time to time. Although I know I should, I

don't

usually design on paper except maybe for a sketch or two when I'm

getting


started, and I use Print Screen sometimes when I need to keep

Relationships


in front of me or something like that.

"Nikos Yannacopoulos" wrote:




Debra,

You can't possibly be *that* blonde if you're using Access:-) I also

get

the wrong message sometimes and I'm not at all blonde, I assure you,

so

it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You

might

find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:



Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them

out


prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
. ..




Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:




Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially

is

tblWorkOrders. At some point, further details MAYbe added to

tblInspection




or tblIssuer. I think that the lookup tables speak for themselves.

Thank




you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in

message

...





Debra,

Mail me your back acct details, IOU you ten bucks! Playing with

the

odds


doesn't always pay.

Nikos

Debra Farnham wrote:





I can provide more exact details (i.e. my current table design if

necessary)





.... the way I'm seeing it though, its going to be difficult to

avoid


empty





fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
. gbl...






The database is maintaining a specific type of permit .... some

details




apply to some of the permits and not others. (Pricing and

charges

for


example only apply to some of the services covered by the permit

but


not




all






services apply to all permits).

There are no standard rates for any of the services that the

permit


may




cover.

Some of the details will be added in long after the permit is

issued


(i.e.





who installed it, who inspected it, the labour and material

costs,

etc.)




It is rare that all of the details will apply to all of the

permits.


Some customers will do installations themselves and will be

responsible




for






their own costs in which case the permit will only allow for the

work


to




be






completed and includes such details as address, permit applicant

and


permit






number.

I hope this provides the detail necessary to answer my initial

query.


Thank you

Debra



"Nikos Yannacopoulos" wrote in

message


.gbl...






Debra,

It's impossible for one to comment on a design they haven't

seen

at


all!





However, ten bucks says "six one to one relationships" with

"blank


fields in some of them" is an unnormalized database. If you

post

some


details of what you are trying to achieve (the overall concept)

and


your





current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:






Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would

keep

the


database






normalized in that it would eliminate blank fields and keep

related


data






together. Now that I have had time to reflect on it, I'm

wondering


if




perhaps I should have created one huge table. Even with the

tables


nicely






broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have

designed

this




database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra

I will also stress that I am not suggesting you change your approach of
designing on paper. I will suggest that in future postings it would be
sufficient to describe just the essential fields in your tables, maybe
something like this (generic situation, not related to your database):

tblContacts
ContactID (primary key, or PK)
LastName, FirstName, etc.

tblAccounts
AccountID (PK)
ContactID (foreign key, or FK)
Date, etc.

A reader can assume that your contacts also have addresses, phone numbers,
and so forth, but it doesn't really affect the design. You can save yourself
some effort in posting, and somebody reading your post will have an easier
time sorting out the specific question. Also, some of your tables (such as
tblDistribution) do not seem to have a primary key, in which cases it is
especially helpful to identify foreign keys.
Good luck.

"Debra Farnham" wrote:

Nikos ... you make a very valid point.

I read a thread of 70+ articles about the very subject last night and have
decided to re-design. As most of the fields that would likely be nulls are
yes/no I decided to put all data from the one to one relationships into one
table.

I'm not happy about all the blank fields, but at least now it's easily
updateable, querying is less effort as is reporting.

Thanks again for taking the time to sift thru this.

Debra

"Nikos Yannacopoulos" wrote in message
...
Debra,

I believe I speak for both Bruce and myself when I say neither of us
meant you should no longer design on paper; we do need a good example,
rare as it may be!

As for your original problem, I would love to help if I can, but it
would require me to understand the process you are modeling, which I
fear I am far from. Do you believe it's worth your time to try and put
it all down on "paper", while still not guaranteed to get any useful
feedback?

Regards,
Nikos

Debra Farnham wrote:
Hi Nikos

I agree with Bruce on his response.. It does appear to be something
quite
useful. Although, I will likely continue to design on paper first. I
teach
the stuff and I suppose it's best to lead by example.

Sure wish someone could help with my initial problem though. I am still
seriously doubting the design.

Thanks again

Debra


"Nikos Yannacopoulos" wrote in message
...

Hi Bruce!

Good to know (a) it may be useful to someone besides myself, and (b) I'm
not alone in breaching one of the basic rules!

Regards,
Nikos

BruceM wrote:

I know I'll use it from time to time. Although I know I should, I
don't
usually design on paper except maybe for a sketch or two when I'm

getting

started, and I use Print Screen sometimes when I need to keep

Relationships

in front of me or something like that.

"Nikos Yannacopoulos" wrote:



Debra,

You can't possibly be *that* blonde if you're using Access:-) I also
get
the wrong message sometimes and I'm not at all blonde, I assure you,
so
it's got nothing to do with it anyway.
I'm relieved to hear you didn't actually type all that, and it's
definitely good practice to start your design on paper - which I never
do:-( I actually use this:

Sub Export_Table_Fields_List()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Design.txt" For Output As #1
Print #1, "Table Design for Access Project:" & vbTab & CurrentDb.Name
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 4) "MSys" Then
Print #1,
Print #1, "Table:" & vbTab & tbl.Name
For Each fld In tbl.Fields
Print #1, fld.Type & vbTab & fld.Size & vbTab & fld.Name
Next
End If
Next
Close #1
End Sub

to save the basic design in a text file after I have put it together,
which comes handy sometimes when making changes during the development
phase. I also have another similar one for queries:

Sub Export_Query_Design()
vName = Left(CurrentProject.Name, Len(CurrentProject.Name) - 4)
Open "c:\temp\" & vName & " Query Design.txt" For Output As #1
Print #1, "Query Design for Access Project:" & vbTab & CurrentDb.Name
For Each qry In CurrentDb.QueryDefs
Print #1,
Print #1, "Query:" & vbTab & qry.Name
Print #1, qry.SQL
Next
Close #1
End Sub

which I use to check if a table or field is used in any query. You
might
find use for it sometime.

Regards,
Nikos

Debra Farnham wrote:


Hey Nikos!

You'll never believe how blonde I am.

I just reread your post that I responded to early early this morning.

Gosh I feel like an idiot.

Guess I now owe you for making you look at my silly post.

BTW - I did copy and paste.

I always design my tables and fields in a spreadsheet and print them

out

prior to beginning design in Access.. *S*

Debra


"Nikos Yannacopoulos" wrote in message
. ..



Debra,

What can I say, other than apologize for making you go through the
trouble of typing all this (I hope you just copied / pasted from
somewhere!)... my previous post was just my admitting I was wrong in
guessing a bad design, but apparently I wasn't clear enough.
This looks fine with my limited understanding of your project.

Regards,
Nikos

Debra Farnham wrote:



Hi Nikos

Here's my current table design

tblWorkOrders
dtmDateofPermit
strWorkOrderNumber
lngApplicantID
strRoadOpening
strPermitNumber
memServiceLocations
lngCityID
memAdditionalInstructions
ysnDrawingApproved
curEstimatedCost
strPONumber
lngIssuedBy

tblApplicant
autApplicantID
strApplicantName
strAddress
lngCityID
strPhone
strPostalCode

tblCity
autCityID
strCity

tblMainToLot strSize
ysnWUCInstall
ysnCustomInstall
ysnPressureTest
ysnServiceLot

tblWorkOrderDetails
strWorkOrderNumber
ysnUseof Hydrant
ysnAnnual
lngSizeofConnection

tblSizes autSizeID
strSize

tblInspectionDetails
strWorkOrderNumber
ysnInspectOnly
lngSizeID
ysnTap&InspectOnly
lngSizeID
ysnPressureTest
ysnChlorinate
ysnFlush

tblLotToBuilding
strWorkOrderNumber
ysnWUCInstall
ysnCopper
ysnCustInstall
lngSize
ysnPVC
ysnNewService
ysnRenewal

tblDistribution
strWorkOrderNumber
lngInstallerID
lngForemanID
dtmCompleted
strRequisitionNumber
ysnAbandonedService
dtmDateAbandoned

tblInstallers
autInstallerID
strInstallerFirstName
strInstallerLastName

tblForemen a
utForemanID
strForemanFirstName
strForemanLastName

tblFinance
strWorkOrderNumber
curLabour
curMaterial
curTrucking
memOther
curOtherCost
strDebit
strCredit
strInvoiceNo

tblCharges
strWorkOrderNumber
ysnSize
cur929729865
curPressureTest
cur929729906
cur929729881
curDeposit
curCurbBoxDeposit
curVISIDeposit
cur909820350
strChequeNumber

tblIssuer
autIssuerID
strIssuerFirstName
strIssuerLastName

tblInspection
strWorkOrderNumber
ysnMaterial
strDepth
ysnPressureTested
ysnChlorinated
ysnDeficiences
lngInspectorID
dtmInspectionDate
memComments

tblInspectors
autInspectorID
strInspectorFirstName
strInspectorLastName

The only table that will ALWAYS have data in all fields initially
is
tblWorkOrders. At some point, further details MAYbe added to

tblInspection



or tblIssuer. I think that the lookup tables speak for themselves.

Thank



you for taking the time to review my design.

Debra


"Nikos Yannacopoulos" wrote in
message
.. .




Debra,

Mail me your back acct details, IOU you ten bucks! Playing with
the

odds

doesn't always pay.

Nikos

Debra Farnham wrote:




I can provide more exact details (i.e. my current table design if

necessary)




.... the way I'm seeing it though, its going to be difficult to

avoid

empty




fields whether it's one table or one hundred tables.

Thanks again

Debra

"Debra Farnham" wrote in message
l...





The database is maintaining a specific type of permit .... some

details



apply to some of the permits and not others. (Pricing and
charges

for

example only apply to some of the services covered by the permit

but

not



all





services apply to all permits).

There are no standard rates for any of the services that the

permit

may



cover.

Some of the details will be added in long after the permit is

issued

(i.e.




who installed it, who inspected it, the labour and material
costs,

etc.)



It is rare that all of the details will apply to all of the

permits.

Some customers will do installations themselves and will be

responsible



for





their own costs in which case the permit will only allow for the

work

to



be





completed and includes such details as address, permit applicant

and

permit





number.

I hope this provides the detail necessary to answer my initial

query.

Thank you

Debra



"Nikos Yannacopoulos" wrote in

message

bl...





Debra,

It's impossible for one to comment on a design they haven't
seen

at

all!




However, ten bucks says "six one to one relationships" with

"blank

fields in some of them" is an unnormalized database. If you
post

some

details of what you are trying to achieve (the overall concept)

and

your




current design, I'm sure you'll get some good advice here.

HTH,
Nikos

Debra Farnham wrote:





Hello all

Windows 2K
Access 2K

I have designed a database which contains about six one to one
relationships. My original thinking was this design would
keep

the

database





normalized in that it would eliminate blank fields and keep

related

data





together. Now that I have had time to reflect on it, I'm

wondering

if



perhaps I should have created one huge table. Even with the

tables

nicely





broken down, I can't avoid blank fields in some of them.

I'm really just looking for advice as to whether I have
designed

this



database in the best manner possible.

Any comments/suggestions would be welcomed.

Thank you in advance for taking the time to respond.

Debra