CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Jerry
Jerry

Reputation: 685

Improving database structure in one to many relations

I'm creating a database to keep track on various statistics on my self and I'm wondering if there's a better way to store multiple entries for a single date.

E.g. from my table I have AllergyMedicine which can track multiple medicines taken on the same date, is there a better way to do this?

Also the tables Food and Allergy seems unnecessary, is there a better way to group tables?

Any suggestions are appreciated! DB Schema

Upvotes: 1

Views: 2133

Answers (2)

cslotty
cslotty

Reputation: 1797

Of course, if you take more than one medicine on one day, why not isolate that day (=date) in its own table?

So you'll have a table "days" with only dates, that you either prefill (like a calendar) or only fill with those days when you really took that medicine.

That way, you save a lot of space by "centering" the date in one table and relating everything else to it. Which is actually a very precise model of reality.

All your "FoodSnack", "FoodMeal", "AllergyMedicine" etc. with a date in them will become plain N:M mapping tables then.

You could even abstract further, reduce tables and make just three tables:

  • symptoms
  • causes
  • treatment

All of those related to the central "day" table (I wouldn't call it "Date", cause that's a keyword and easily mistaken also), plus related to each other, where applicable.

Upvotes: 1

Neville Kuyt
Neville Kuyt

Reputation: 29639

I find it helps to state the problem in a semi structured way, as below. 

The system monitors one or more **persons**.
Each person consumes zero or more **items**. Each consumption has an attribute of date and time. 
Items can be **food**, or **medicines**.
Food can be of the types **snack**, **fruit** or **meal**.
A meal has a **type**.
A person may report **symptoms**. Each report will cover a period of time, and be reported at a specific date/time.
Symptoms may be associated with zero or more **allergies**.

I do not believe that "date" is an entity in your schema - it's an attribute of events that occur, e.g. consuming something, or noticing a symptom.

If the statements above are true, the schema might be:

Persons

  • ID
  • name
  • ...

FoodItemType

  • ID
  • Name

FoodItem

  • ID
  • Name
  • FoodItemTypeID (FK)

Medicine

  • ID
  • Name

FoodConsumption

  • PersonID
  • FoodID
  • ConsumptionDateTime

MedicineConsumption

  • PersonID
  • MedicineID
  • ConsumptionDateTime

Symptom

  • ID
  • Name
  • ....

SymptomObservation

  • PersonID
  • SymptomID
  • SymptomStartDateTime
  • SymptomEndDateTime
  • SymptomReportDateTime

Allergy

  • ID
  • Name

AllergySymptom

  • AllergyID
  • SymptomID

Upvotes: 1

Related Questions