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'.
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.
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)?
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.