Best practice for table design

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,

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

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 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 Debra,
One-to-one relationship is very rare. Often, it is implemented for reasons
other than normalization. After reading the description of your problem, as
rare as one-to-one relationship is, your situation is one of them. Google
*subclassing in Access*, you will find many sources of information. Also
take a look at:

http://www.mvps.org/access/tables/tbl0013.htm

HTH,
Immanuel Sibero




"Debra Farnham" wrote in message
...
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

Thank you kindly Immanuel!

I have visited the link you provided (as I have numerous times in the past -
not sure why I didn't think of that on my own).

However, I have done exactly as described and now feel confident in that I
have created my tables using the best model possible.

Thanks again for pointing me in the right direction.

Debra


"Immanuel Sibero" wrote in message
...

Hi Debra,
One-to-one relationship is very rare. Often, it is implemented for reasons
other than normalization. After reading the description of your problem,
as
rare as one-to-one relationship is, your situation is one of them. Google
*subclassing in Access*, you will find many sources of information. Also
take a look at:

http://www.mvps.org/access/tables/tbl0013.htm

HTH,
Immanuel Sibero




"Debra Farnham" wrote in message
...
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

You're welcome and good luck.


Immanuel Sibero



"Debra Farnham" wrote in message
...
Thank you kindly Immanuel!

I have visited the link you provided (as I have numerous times in the
past -
not sure why I didn't think of that on my own).

However, I have done exactly as described and now feel confident in that I
have created my tables using the best model possible.

Thanks again for pointing me in the right direction.

Debra


"Immanuel Sibero" wrote in message
...

Hi Debra,
One-to-one relationship is very rare. Often, it is implemented for
reasons
other than normalization. After reading the description of your problem,
as
rare as one-to-one relationship is, your situation is one of them.
Google
*subclassing in Access*, you will find many sources of information. Also
take a look at:

http://www.mvps.org/access/tables/tbl0013.htm

HTH,
Immanuel Sibero




"Debra Farnham" wrote in message
...
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

Immanuel

Do you have any idea how many one to one relationships isn't real good?

I think I have six of them in my database but they are all separate entities
as Rebecca describes in the sub-classing entities article.

I don't want to use subforms as I would have too many so I am using a single
query with all the necessary fields. The problem this has brought about, is
that I cannot later edit data in the form in fields that sit in a table
where no record was created initially (no record in the One side of the
relationship is the message I receive).

I hope this makes sense.

Any ideas?

Thanks again

Debra
"Immanuel Sibero" wrote in message
...

You're welcome and good luck.


Immanuel Sibero



"Debra Farnham" wrote in message
...
Thank you kindly Immanuel!

I have visited the link you provided (as I have numerous times in the
past -
not sure why I didn't think of that on my own).

However, I have done exactly as described and now feel confident in that
I
have created my tables using the best model possible.

Thanks again for pointing me in the right direction.

Debra


"Immanuel Sibero" wrote in message
...

Hi Debra,
One-to-one relationship is very rare. Often, it is implemented for
reasons
other than normalization. After reading the description of your
problem,
as
rare as one-to-one relationship is, your situation is one of them.
Google
*subclassing in Access*, you will find many sources of information.
Also
take a look at:

http://www.mvps.org/access/tables/tbl0013.htm

HTH,
Immanuel Sibero




"Debra Farnham" wrote in message
...
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,

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

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