Re: Best practice for table design

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <nyannacoREMOVETHISBIT@xxxxx> wrote in message
news:uFGtGokSFHA.3492@xxxxxxxxxxxxxxxxxxxxxxx


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" <nyannacoREMOVETHISBIT@xxxxx> wrote in message
news:e3JD$ojSFHA.904@xxxxxxxxxxxxxxxxxxxxxxx



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" <dfarnham@xxxxxxxxxxxxxx> wrote in message
news:ewBjvCaSFHA.3972@xxxxxxxxxxxxxxxxxxxxxxx




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" <nyannacoREMOVETHISBIT@xxxxx> wrote in message
news:uH$dQZZSFHA.2964@xxxxxxxxxxxxxxxxxxxxxxx




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





.

Relevant Pages

  • Re: Best practice for table design
    ... 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 ... strWorkOrderNumber ... tblInspectionDetails ... strInstallerFirstName ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Best practice for table design
    ... seriously doubting the design. ... strWorkOrderNumber ... tblInspectionDetails ... strInstallerFirstName ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Best practice for table design
    ... my previous post was just my admitting I was wrong in guessing a bad design, but apparently I wasn't clear enough. ... strWorkOrderNumber ... further details MAYbe added to tblInspection ... There are no standard rates for any of the services that the permit may ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Best practice for table design
    ... 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, ... strWorkOrderNumber ... tblInspectionDetails ... strInstallerFirstName ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Best practice for table design
    ... Hey Nikos! ... I always design my tables and fields in a spreadsheet and print them out ... >> strWorkOrderNumber ...
    (microsoft.public.access.tablesdbdesign)