Table Design-Fundamentals

Greetings, folks! I have a table that looks like this:

TABLE: PAINTSPECS
MachineAssetNumber
MachineName
PaintSpecification
NumberofCoats

Now, I KNOW that my tables are not normalized. That's why I'm here in need
of help from you folks!

Here's my problem: Each machine can have many different PaintSpecifications
applied to it, and each with several coats. It would be fine if each machine
used only one paint and one coat of that paint.

So, how do I design my table to accomodate this? Should I design the table
more like this?:

TABLE: PAINTSPECS2
MachineAssetNumber
MachineName
Coating1
NumberofCoats of Coating1
Coating2
NumberofCoats of Coating2
Coating3
NumberofCoats of Coating3
Coating4
NumberofCoats of Coating4
Remarks

So if I ever got a piece of equipment that got more than 4 coatings, now I
have to update my table to include a 5th pair or more. Doesn't seem very
practical.

Does anyone know how to avoid designing the table this way?

How about ........

TblMachine
MachineID
MachineName
MachineAssetNumber
other fields such as Location, Duty, etc

TblPaint
PaintID
Manufacturer
Color
other specs

TblMachinePaint
MachinePaintID
MachineID
PaintID
NumberofCoats
DateLastPainted

Steve




"Tekbro" wrote in message
...
Greetings, folks! I have a table that looks like this:

TABLE: PAINTSPECS
MachineAssetNumber
MachineName
PaintSpecification
NumberofCoats

Now, I KNOW that my tables are not normalized. That's why I'm here in need
of help from you folks!

Here's my problem: Each machine can have many different
PaintSpecifications
applied to it, and each with several coats. It would be fine if each
machine
used only one paint and one coat of that paint.

So, how do I design my table to accomodate this? Should I design the
table
more like this?:

TABLE: PAINTSPECS2
MachineAssetNumber
MachineName
Coating1
NumberofCoats of Coating1
Coating2
NumberofCoats of Coating2
Coating3
NumberofCoats of Coating3
Coating4
NumberofCoats of Coating4
Remarks

So if I ever got a piece of equipment that got more than 4 coatings, now I
have to update my table to include a 5th pair or more. Doesn't seem very
practical.

Does anyone know how to avoid designing the table this way?

recommend you read up on relational design principles. for more information,
see
http://www.accessmvp.com/JConrad/acc...abaseDesign101

hth


"Tekbro" wrote in message
...
Greetings, folks! I have a table that looks like this:

TABLE: PAINTSPECS
MachineAssetNumber
MachineName
PaintSpecification
NumberofCoats

Now, I KNOW that my tables are not normalized. That's why I'm here in
need
of help from you folks!

Here's my problem: Each machine can have many different
PaintSpecifications
applied to it, and each with several coats. It would be fine if each
machine
used only one paint and one coat of that paint.

So, how do I design my table to accomodate this? Should I design the
table
more like this?:

TABLE: PAINTSPECS2
MachineAssetNumber
MachineName
Coating1
NumberofCoats of Coating1
Coating2
NumberofCoats of Coating2
Coating3
NumberofCoats of Coating3
Coating4
NumberofCoats of Coating4
Remarks

So if I ever got a piece of equipment that got more than 4 coatings, now I
have to update my table to include a 5th pair or more. Doesn't seem very
practical.

Does anyone know how to avoid designing the table this way?

Tekbro,

Did you need to keep track of when each individual coat was applied or do
you just need to track the multiple paints per machine and the date of the
last coat?

--
Dennis

The database that I'm creating is not really for a preventative maintenance
log or anything like that, so keeping track of when the coats are applied is
not really that important (though it might be useful to keep that in mind for
the future). Mainly we just need to identify what each coat is made of. The
live database will probably be labeled something like pre-wash, primer, top
coat, optional coating, instead of just coat1 coat2, coat3, coat4, for
example. And of course, I'd want to keep track of how many coats of each were
applied (No. Coats of Pre-wash, ... etc.)

"Dennis" wrote:

Tekbro,

Did you need to keep track of when each individual coat was applied or do
you just need to track the multiple paints per machine and the date of the
last coat?

--
Dennis

Add another table for pre-wash, primer, top coat, optional coating:
TblCoatingType
CoatingTypeID
CoatingType

to the tables I previously suggested then add CoatingTypeID to
TblMachinePaint. You can omit DateLastPainted if you want.

This will give you exactly what you describe.

Steve



"Tekbro" wrote in message
...
The database that I'm creating is not really for a preventative
maintenance
log or anything like that, so keeping track of when the coats are applied
is
not really that important (though it might be useful to keep that in mind
for
the future). Mainly we just need to identify what each coat is made of.
The
live database will probably be labeled something like pre-wash, primer,
top
coat, optional coating, instead of just coat1 coat2, coat3, coat4, for
example. And of course, I'd want to keep track of how many coats of each
were
applied (No. Coats of Pre-wash, ... etc.)

"Dennis" wrote:

Tekbro,

Did you need to keep track of when each individual coat was applied or do
you just need to track the multiple paints per machine and the date of
the
last coat?

--
Dennis