Twinny, thanks for the clear article about Database Normalization.
Seth, I've tried to keep the problem clear/small by using this fictional database which I described in the 1st post. I did this because the database contains confidential data from the company I'm working for. But I've discussed it and removed the confidential part so I can show you guys the actual database I'm working on .
The goal of the database is:
A employee can CREATE, EDIT and VIEW a Work Instruction.
Each Work Instruction contains several steps, "Work Steps".
Each Work Step contains: - a Step Name
- a Department Name
- a Work Station Operation name
- a Work Station Operation number
- a Machine Model
- a Machine Variant
- an English description
- a Dutch description
- a Step Revision Number
- a maximum of 4 different kind of Parts which are used
- a maximum of 4 pictures of the used Parts
- a big Picture of the action being performed
I've already set up a Form for the user to CREATE a Work Step:
The layout of the form matches the actual layout of one page of the Work Instruction document. Each page consists of max. 2 Steps.
On this Form you can see multiple Attachment Objects to add Pictures. I'm going to replace these with Image Objects to keep the database compact.
These are the database items:
Name: tblDepartment
Primary Key: DeptID
Department [text]
Name: tblOperations
Primary Key: OperationID
Operation [number]
Description [text]
Work Station [number]
Foreign Key: DeptID [number]from tblDepartment
Name: tblVariantList
Primary Key: VarID
Variant [text]
varDescription [text]
Name: tblVariant_per_Model
Primary Key: variantID
Foreign Key: ModelID [number] from tblModels
Name: tblModels
Primary Key: ModelID
Model [text]
Name: tblParts
Primary Key: PartID
SAPnumber [number]
PartDescription [text]
PartPicture [text] (link to image of part)
Name: tblSteps
Primary Key: stepID
StepName [text]
DescriptionEN [text]
DescriptionNL [text]
Foreign Key: DeptID
Foreign Key: OperationID
Foreign Key: VariantID
Foreign Key: ModelID
Picture [text] (link to image of action]
Foreign Key: Part1
Qty1 [number]
Foreign Key: Part2
Qty2 [number]
Foreign Key: Part3
Qty3 [number]
Foreign Key: Part4
Qty4 [number]
The main problem (I think) is that I want to display 4 record values (four Parts from tblParts) in one record (one StepID).
If I normalise the database and create a query like this:
ID.....PartID.....Qty.....PartPicture
1..........2..........10.......C:\abcdef2.jpg
2..........1..........15.......C:\abcdef1.jpg
3..........3..........13.......C:\abcdef3.jpg
4..........4..........17.......C:\abcdef4.jpg
1..........1..........25.......C:\abcdef1.jpg
etc.
How does the Img Control know which part to display for a single StepID? If the Img Source of Part 1 looks for StepID 36 it finds 4 different part pictures but it needs to show only the picture of part 1.
I hope the database information clearifies the situation a bit.