Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    178

    How do I Resolve Many to Many?

    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
    Attached Thumbnails Attached Thumbnails ERDTest2.jpg  
    Last edited by aboo; 01-18-09 at 09:49.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2006
    Posts
    178
    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?

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by aboo
    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.

  5. #5
    Join Date
    May 2006
    Posts
    178
    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?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2006
    Posts
    178
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, but CustomerID should be a FK in WorkOrder -- just not part of the PK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2006
    Posts
    178
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •