CODEWITHSUNDEEP
mysqlsqldatabase-designdata-modeling
rendon
rendon

Reputation: 2363

How to implement two entities with attributes in common, in MySQL?

I am designing a database model of a doctor's office and pharmacy and the requirements ask to store data about customers and patients, a customer is somebody who buys in the pharmacy and a patient who see the doctor, a customer can be patient at the same time and viceversa. How to manage this situation? If I create one entity for each one then, there is the probability that a person would be on both tables. Any suggestions?

Upvotes: 2

Views: 662

Answers (2)

Icarus
Icarus

Reputation: 63956

If the data for customer and patient is exactly the same, you can have a single table with personal information and a type column that could either be a bit or an integer. This type column will tell you whether the record is for a patient or a customer. I don't see the repeated records on the case where a patient is also a customer as a big issue but if you want to avoid this situation, you'll have to create a linking table. Something like:

person
(id, name, address, ...)

And

person_type( person_id, type_id)

A person that's both a customer and a patient will have 2 entries in this table; one for each type (customer/patient).

Upvotes: 2

Whetstone
Whetstone

Reputation: 1199

Two methods:

  1. One table called persons table, has files 'is_customer' and 'is_patient' that can be true/false

  2. Three tables, one for people which stores all of their relevant data (names, address, phone number, etc) with a unique ID, one table for patients which is just a table of unique id's and references to the people table, and one table for customers which is just a table of unique ids and references to the people table.

Upvotes: 2

Related Questions