ER diagram relation

Question

I'm having a bit of trouble designing an ER diagram for a bike shop. The shop contains many bike parts (wheel, gear, brakes etc.) which have different attributes. I have therefore made each part as an entity in order to model the different attributes of theirs. They all contain a quantity attribute, name and price which is made by using inheritance. However, now when I have all these entities they should be mapped to the 'Bike' entity which is a collection of all the parts and the 'Stock' entity where all the parts are listed as well as their preferred amount and minimum amount.

My problem is that I'm not sure how to map the parts to the 'Bike' and 'Stock' entity. In the figure below I've made two different designs. Which one of them is correct, if any at all? Can I model it in a smarter way? (I have removed the attributes for simplification)

Solution 1 Solution 2

enter image description here

DrabJay DrabJay · Accepted Answer · 2014-02-18T19:49:00

I think you are looking at a Bill of Materials type schema, where you could have a Part super-type and as many sub-types as you wish to hold specific details for particular types of Part. The Bill of Materials contains a quantity to hold the number of child Parts required to make the parent Part e.g 2 wheels, 1 frame. This goes all the way up to Bike, which is just another type of Part. The Part can then link into your entities for managing Stock and Inventory.

               +-----------------+
               | BOM             |
               +-----------------+
               | parent_part_id  |
               | child_part_no   |
               | quantity        |
               +-----------------+
                     |     |         
                     |     |         +-------------+
                     |     |         | STOCK       |
                 +-------------+     +-------------+
                 | PART        |-----| ...         |
                 +-------------+     +-------------+
                 | part_id     |
                 | part_type   |     
       +---------| ...         |---------+
       |         +-------------+         |
       |                |                |
       |                |                |
       |                |                |
+-------------+  +-------------+  +-------------+ 
| WHEEL       |  | GEAR        |  | BIKE        |
+-------------+  +-------------+  +-------------+
| part_id     |  | part_id     |  | part_id     |
| ...         |  | ...         |  | ...         |
+-------------+  +-------------+  +-------------+

ER diagram relation

2 Answers