Help with database design/model.

[Jeroen3131]

Hello all,

I'm struggling with finding a correct design layout for my Acces databse.

This is currently what I have:

A Form where the user can select 4 different parts in comboboxes. After selecting the cost of each part should appear in the textbox. When the button is pressed the values will be stored to tblOrders (the form is bounded to it).


This table contains all the existing parts. These values are the rowsources for the comboboxes.


All the Form values are stored with a OrderID in this table:


This is the Query I want to run to see which parts are used in each orderID. Clearly this wont work because there are multiple columns with identical names.


Can someone please point me in the correct direction to turn this database into a proper one.

Kind regards,

Jeroen3131

[twinnyfo]

Jeroen3131,

It does not appear that your database tables are properly normalized. Please refer to this article for additional information: Database Normalization.

The basics: Your form should not have four combo boxes to select four different items on one order. You should have all items for one order as separate records, but as one OrderID.

I am currently unable to see your images at work, but your descriptions make it clear that your first step should be to normalize your data tables so you can better manage your orders and items.

[twinnyfo]

Perhaps, list your tables in the thread, listing the field name, field type and any additional notes concerning that field. List the pertinent data for these tables and we can assist with design.

We have helped many others (and I have been helped) to redesign DBs through this forum).

[Seth Schrock]

Unless you have a reason for doing so, calculated fields (like the cost of a part) should not be stored in your tblOrders. The exception to this rule would be that the cost changes so you would need to lock in the cost at the time of purchase.

Also, what would happen if someone purchased five parts? For this reason, it is better to have a subform so that you can have unlimited number of parts that a person can order. Both of these ideas are covered in Database Normalization and Table Structures.

So a proper design would have the following tables:

• tblProducts
• tblOrders
• tblOrderDetails

More tables can also be used to store customer information, product categories, etc. tblOrders would contain information about a sale, such as purchase date, cashier, customer, etc. tblOrderDetails would be related to tblOrders and tblProducts using the Primary Key to Foreign Key link and would contain the information about each part being sold such as the quantity sold, the product ID (could be selected from a combo box), etc. If your costs don't change, then this table wouldn't contain a cost field. If you do need the ability to lock in the price, then it would have the price field.

If you download the Northwind sample database, you can see how all this works.

[Jeroen3131]

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:

1. a Step Name
2. a Department Name
3. a Work Station Operation name
4. a Work Station Operation number
5. a Machine Model
6. a Machine Variant
7. an English description
8. a Dutch description
9. a Step Revision Number
10. a maximum of 4 different kind of Parts which are used
11. a maximum of 4 pictures of the used Parts
12. 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.....Part Picture
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.

[Seth Schrock]

Ok, I have few questions to clear some things up. So a single step on the form you provided shows a big picture window, four part comboboxes, four quantities, one english description, one dutch description, and four small pictures. Fairly obvious that the four quanities, parts and small images coincide with each other, but what does the big picture on the left side do? Based on your question about the Image control knowing which part to display, I'm guessing that it shows just the picture for the first part?

As far as the normalized table design goes, here is how the steps and parts would related to each other:

[U]tblSteps[/U]
StepID: PK
StepName
[I]rest of fields...[/I]

[U]tblStepParts[/U]
StepPartID: PK
StepID: FK
PartID: FK
Qty

[U]tblParts[/U]
PartID: PK
PartPicture
PartDescription
[U]rest of fields...[/U]

I think that you know how these would be related. Just match up the names. Now your query would pull all the fields from tblStepParts and the Part picture from tblParts.

SELECT SP.StepID, SP.PartID, SP.Qty, P.PartPicture
FROM tblStepParts As SP INNER JOIN tblParts As P 
ON SP.PartID = P.PartID

You would then create a form that has the Qty textbox, a combo box bound to the PartID field that displays the description from tblParts, and an image control display the picture. Add this form to the main form as a subform. You would create the Master/Child link between the parent and subforms on the PartID field. You can then enter the qty, select the part from the combobox and then your picture will automatically populate without any code.

Going back to your question about how the image control knows which image to pull, you can actually link it so that the image displayed in the big image control corisponds to whichever record you have selected in the subform.

[zmbd]

wow,
Wish I'd been here sooner; however, we have a seriously broad question in the original post and now it's growing branches.

OP: Help with database design/model.
Posts #2 thru #6 seem to answer the original question.
At Post#6 we get a secondary question started. I'll be splitting the thread at this point.

Link to split: http://bytes.com/topic/access/answer...ubforms-parent

To help keep this thread useful, I suggest that we narrow the topic down to proper table and database design and normalization.