Table Design-Fundamentals

[Tekbro]

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?

 

[Steve]

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
(e-mail address removed)

 

[tina]

recommend you read up on relational design principles. for more information,
see
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

hth

 

[Dennis]

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?

 

[Tekbro]

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.)

 

[Steve]

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
(e-mail address removed)