Database Design – Modeling Subclass Constraints in ER Diagrams

database-designdatabase-diagramserdsubtypes

Scenario description

Can someone help me out on how to model the situation that follows:

  1. There is a superclass (or supertype) called PRODUCTS, which has the subclasses (or subtypes) named FOOD and SHOES.

  2. There exists another superclass denominated CUSTOMERS, with the subclasses denominated SHOE STORE and GROCERY STORE.

  3. I need to represent some rules that indicate that:

    • FOOD can only be delivered to GROCERY STORES.
    • SHOES can exclusively be distributed to SHOE STORES.

Tentative diagrams

I have tried the two diagrams shown bellow:

Diagrams

Diagrams considerations

Top diagram

I really do not like the duplication of (a) the same entity type, i.e., ORDERS, and (b) the relationships around it.

Bottom diagram

I am pretty sure this not the proper notation. Should I instead create the relationship on the superclass level and make a note that the restrictions will be implemented at the programming level?

Best Answer

I think you can make an Orders entity that is a relationship between Products and Customers. The Orders would have subclasses FoodOrders and ShoeOrders, which respectively would be the relationships between the subclasses of Food - GroceryStores and Shoes - ShoeStores.

                                            ┌───────────┐
                                            │ Employees │
                                            └─────┬─────┘
                                                  │
                                                  │
      ┌──────────┐               ┌────────┐       │       ┌───────────┐
      │ Products ├─── contain ───┤ Orders ├─── deliver ───┤ Customers │
      └────┬─────┘               └────┬───┘               └─────┬─────┘
           │                          │                         │
           │                          │                         │
          isA                        isA                       isA
          │ │                        │ │                       │ │
     ┌────┘ └────┐             ┌─────┘ └─────┐            ┌────┘ └────┐
     │           │             │             │            │           │
 ┌───┴──┐    ┌───┴───┐         │             │    ┌───────┴───────┐   │
 │ Food │    │ Shoes │         │             │    │ GroceryStores │   │
 └───┬──┘    └───┬───┘         │             │    └───────┬───────┘   │
     │           │       ┌─────┴──────┐      │            │     ┌─────┴──────┐
     └───────────│───────┤ FoodOrders ├──────│────────────┘     │ ShoeStores │
                 │       └────────────┘      │                  └─────┬──────┘
                 │                     ┌─────┴──────┐                 │
                 └─────────────────────┤ ShoeOrders ├─────────────────┘
                                       └────────────┘
Related Question