Hi,

Im abit confused on a scenario I have so here it goes.

Im trying to design a database where I create "Work Orders" for jobs that need to be done around the depot.

These Work orders contain various attributes like Worktype, Location,Date etc..

Sometimes it is required to have certain "Tools" and "Materials" to do these jobs. There is no cost i need to take into account as it is dealt internally.


My problem is that each work order can contain many tools or materials and that many tools or materials can be associated to many work orders.


I have used a tool called "Case Studio" to design my ERD but when created a many to many realtionship it auto created 2 new tables.

I have attached the ERD so you can see what i mean. Is there anyone who can help me see where i am going wrong?

Many Thanks

Aboo

what do you see as wrong? the ERD looks fine to me

to implement a many-to-many relationship, an intermediate association or relationship table is required, which has columns that are FKs to the respective many-to-many tables, and a composite PK for the relationship table

Hey R937, Thanks for your reply, im just having trouble understanding.


Well if i create this database using the entities in the ERD. What would happen if i just stored the OrderID in the Materials Table with the QTY and the CustomerID?

Woudl this not achieve the same results in access?

No because then the key of Materials would presumably have to be (MaterialCode, OrderId, CustomerId) and you would therefore have to populate a Qty for each combination of MaterialCode, OrderId and CustomerId. Is that what you intended? If so, then your current diagram is wrong because it only has ONE Qty per MaterialCode.

This is an example of what data modellers call a dependency: "x determines Qty". To be sure you model your database correctly you need to identify dependencies before you start.

The rules being applied by your CASE tool are the principles of Normalization. Roughly speaking, the relevant rule is that the determinant for every (non-trivial) dependency should be a super key (the "determinant" is what I referred to as "x" above). If you aren't familiar with that idea then it would be a good idea to study a decent book on database modelling to help you understand what your CASE tool is trying to do.

Hi dportas,

Again thank you for your response.
I think your right. My ERD is wrong. I want to have multiple QTY for each material, ie could have 500 Screws related to this OrderID.
Then again, wouldnt it be many to many as i can place another order for some screws again? (MANY screws can be on MANY Orders?)

All i want is to create a database where i can see what "materials" and/or "Tools" a Order associated to and Which customer Ordered it.

Have i totally missed the target?

i don't think CustomerID should be part of the PK of the WorkOrder table

the Qty columns need to move out of the Materials and Tools tables, and into the Entity6 and Entity9 tables

r937,

If i dont have it as a Foreign key how will i know which customer has placed that order? how will a customer be linked to the order?

aboo.

ah, but CustomerID should be a FK in WorkOrder -- just not part of the PK

Thanks for your help r937,

Im gonna give it ago today, see how it goes. I will post later today see how it pans out.