If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#3
|
|||
|
|||
Table Design-Fundamentals
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? |
#4
|
|||
|
|||
Table Design-Fundamentals
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 |
#5
|
|||
|
|||
Table Design-Fundamentals
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 |
#6
|
|||
|
|||
Table Design-Fundamentals
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 |
Thread Tools | |
Display Modes | |
|
|