How can i map this part of the ER to relational schema and what can be the appropriate attributes?

Question

Part of the ER diagram here.

Here, Customer is the super-class, with Private and Business being the sub-classes with a disjoint mandatory participation, and Business customers is a super-class of Silver, Gold and Diamond sub-classes respectively again with a disjoint mandatory participation

A quick explanation: Customers are divided into Business and Private. Business customers have three membership options: Silver:10% Discount, Gold:25% Discount, Diamond:40% Discount. What is the best way to map the above into a Relational Schema? What will be differentiating attributes and generic attributes? Currently, I have come up with the following:

4 tables - PrivateCustomer(From Customer Superclass) and SilverMember, GoldMember and DiamondMember (From Business Superclass)

PrivateCustomer (CuID, CuFirstName, CuLastName, CuDOB, CompanyName, CuAddressLine1, CuAddressLine2, PhoneNumber, CuEmail, CuNotes, CuLocID)

SilverMember (CuID, ????? DiscPerc, DiscAccrued, CuLocID)

GoldMember (CuID, ???? DiscPerc, DiscAccrued, CuLocID)

DiamondMember (CuID, ???? DiscPerc, DiscAccrued, CuLocID)

CuLocID is a Foreign Key to remove transitive dependencies relating to Post Code, City, Country (Location details that are stored in a different table - tblCuLoc)

Answer 1

First of all, I think your ERM is bad design, because you should better use a role pattern instead of putting them into entities. In other words the membership options Silver, Gold and Diamond are no entities.

To answer your question, you have three options to map your classes:

1. All attributes of the class hierarchy goes into one table (Single Table Inheritance).
2. All attributes of each class goes into a separate table (Class Table Inheritance).
3. All attributes of all non abstract classes goes into a separate table (Concrete Table Inheritance).

But honestly you should revise your ERM before. At least you should create a entity for the discount membership (e.g. Discount Group) with the attributes Percentage, Name and a one-to-many relationship between your Business Customer entity and your Discount Group entity. You could also use the role pattern for the customer entity itself, instead using sub classes for them. Here is my proposal as an ERM in the UML to improve the discount membership:

Mapping this to a relational schema by using e.g. the 2. approach (Class Table Inheritance) you would have the following relations:

• customer(id, ...)
• business_customer(id, ...)
• private_customer(id, ...)
• discount_membership(customer_id, group_id, discount_accrued)
• discount_group(id, name, discount_percantage)

To force the multiplicity in discount_membership you should use a unique contraints on the column customer_id and of course the proper foreign key contraints.