Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    Learner Relationship Q

    Hi all, am trying to create an ERD and have become stuck to what I'm sure is a simple solution, so any advice much appreciated. To keep it simple, imagine a scenario whereby a 'Person' can place an 'Order' to a 'Shop' for a 'Delivery' of 'Items'. Now imagine that the type of items that can be purchased can be grouped, according to similar attributes, into three completely different Types. How would I represent this in an ERD diagram (the item type relation)? If the types that could be ordered were all very similar, like bread or cheese or jumper, then I would list a 'Type' entity connected to the 'Order' entity which defined the different category of Types. However as the items to be ordered vastly differ they have there own unique attributes, and one of the three Types actually link off to more entities related to the acquisition of that Type by the 'Shop'.

    I have included a picture exemplifying the above, highlighting the two lone entities "Car" and "Groceries" which can be an item on a delivery, but differ vastly to each other having unique attributes. I am trying to work out how these two would link (relationship) to 'Order'.

    TAI
    Attached Thumbnails Attached Thumbnails Entity Relationship Diagram2.jpg  

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Create a table called OrderCar with the attributes OrderID and CarID. Create another table called OrderGroceries with the attributes OrderID and GroceryID. To gather up all of the information for a particular order you will have to query each of the Order<type> tables to find out what items are on the order.

  3. #3
    Join Date
    Nov 2009
    Posts
    5
    Thanks, but what table would the 'OrderCar' and 'OrderGroceries' table link to, and what would be the relationship (cardinality)? Would the 'OrderID' attribute of these tables be the PK, with a FK called 'OrderID' be placed in the existing 'Order' table? And if so, then am I assuming correctly that the relationship from the 'Order' table to the 'OrderGroceries' and 'OrderCar' be have to be Many-to-Many and therefore require a table created between each (as Many-to-Many is not normalised)?

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    The OrderID of both of the tables are a foreign key to Orders. For each order you would have zero to many OrderGroceries rows and zero to many OrderCar rows. The GroceryID of the OrderGroceries table would be a foreign key to the Groceries table and the CarID of the OrderCar table would be a foreign key to the Car table.

  5. #5
    Join Date
    Nov 2009
    Posts
    5
    Thanks Mark for the explanation!

Posting Permissions

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