-1

As part of an assessment I am trying to build an E-R diagram for an E-Commerce site. There is a Customer and Producer as part of the e-commerce site as players. So, I thought of creating a table - Legal Entity with LegalEntityID as a primary key and customerUserID, producerID as foreign keys. The other attributes will be EntityType (Individual or Corporate), Role (Customer, Producer), Name, Address, Phone number. What do you think are the advantages and disadvantages of this setup? I can think of individuals v Corporate, then to maintain CustomeruserID and PRoducerID create another table as Customer / Producer and use primary key as CustomerUserID and ProducerID? This will make it complicated? or a create a Role table with Role ID as primary key and then another table - LegalEntityrole with LegalentityRole ID as the primary key with Role ID, LegalEntityID as the foreign keys. What do you guys think?

Abdemanaf Rangwala
  • 1

1 Answers1

0

This sounds like a scenario that would make great use of an inheritance structure.

I'd suggest you have the Legal Entity table with the required fields and then your Customer and Producer tables each inherit from the Legal Entity table by including a LegalEntityId as a foreign key. By doing this you don't need to have the Role table.

enter image description here

Nathan Cramer
  • 64
  • 3
  • Thanks Nathan, I am thinking of using LegalEntity table and avoid the Customer / Producer table, this will avoid maintaining multiple tables. Further, the attributes are : 1. Customer User ID 2. Producer ID 3. Product ID 3. Product External ID 4. Order ID 5. Order Date 6. Customer Name 7. Customer Address 8. Customer City 9. Customer Counntry 10. Producer Name 11. Producer Address 12. Producer city 13. Producer country 13. Unit price 14. Unit on qty 15. Total price 16. Total qty 17. Product description – Abdemanaf Rangwala Jun 20 '20 at 13:49
  • More tables are not always a bad thing. Just make sure your ERD remains in 3rd normal form since it's for an assignment. – Nathan Cramer Jun 22 '20 at 07:05