1

I have to connect Supertype Entity Called

  • Users ( User_ID (PK) , User_Password , Registration_Date , .. etc ) .

to Each one of the following entities :

  • Employees ( Employee_ID (PK) , Fname , Lname , Birthdate .. etc )
  • Customers ( Customers_ID (PK) , Fname , Lname , Birthdate .. etc )
  • Suppliers( Supplier_ID (PK) , Fname , Lname , Birthdate .. etc ) ..

How To Do It ( For relational database ) Using Ms-Access ?

Wadda7
  • 69
  • 1
  • 2
  • 4
  • 1
    Normally, customers and suppliers can be companies, which don't have a first name, last name, or birth date. Are you sure you're modeling this correctly? – Mike Sherrill 'Cat Recall' Aug 21 '12 at 09:00
  • What are you trying to do here? Is there a reason why you cannot include a field (column) user ID in each of the tables? Also, if customers and suppliers are people and not companies as per @Catcall, why is everything not in one People table? At the least, why are Suppliers and Customers not in the same table? – Fionnuala Aug 21 '12 at 10:48

1 Answers1

2

There are generally 3 strategies for representing inheritance in the relational databases:

  1. Everything in one table.
  2. Concrete types in separate tables.
  3. All types in separate tables.

The (3) is probably most common and most "clean" even though it can involve a fair bit of JOINing. In your case, you'd have FKs in child tables (referencing the parent) and enforce the presence1 and exclusivity2 of the child through the application logic. It is possible to enforce these things declaratively through the DBMS supporting circular and deferred FKs, but not in MS Access.

You might want to take a look at this post for more info.

1 So user cannot be just user - it must be either employee, customer or supplier.

2 So user cannot be (for example) a customer and a supplier at the same time.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167