What's the best way to normalise this database?

Question

First of all thank you for reading my problem and I hope that you can help me.

So I'm creating an API to use later on in my Android App, but I'm having doubts about the correct way that I need to create the database that I'm working with. The way I've got the database setup now is like this:

But this does not look good and doesn't help with what I want to do in my API. I want my users to able to input symptoms and then my API outputs illnesses based on the input of the users.

So, I think I need to change my database design but what should it look like? I've been struggling over this for the past day and I can't seem to find the correct answer.

Again, thanks for reading!

Your horizontal list of comma delimited values should be vertical; i.e. one value per row, linked by a one-to-many relationship table. See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — Alex K., Jun 17 '16 at 12:22
create symptom table with symptonId,symptonName and illnessSympton table which will have illnessId and symptomId as foreign key — undefined_variable, Jun 17 '16 at 12:29

score 1 · Answer 1 · answered Jun 17 '16 at 12:27

1

Actually you can have three tables: 1. Illness Table 2. Symptom Table 3. IllnessSymptom Table

1.Illness Table will have IllnessID,Illname 2.Symptom table will have SymptomID,SymptomName 3.IllnessSymptom Table will have IllnessSymptomID,IllnessID,Symptom which will relate Illness and Symptom

You can make your API fetch data by joining these table

So the query would be like

 SELECT I.IllnessName IS
 INNER JOIN Illness I ON IS.IllnessID=I.IllnessID 
 INNER JOIN Symptom S OM IS.SymptonID=S.SymptonID
 WHERE S.SymptomName=@YourInputIllness

Hope this answers your query! :)

answered Jun 17 '16 at 12:27

Apsar

65
4

Thank you for your answer! But does this work when you´ve got multiple symptoms for 1 illness? That would mean that rows in IllnessSymptom table would be the same right? Or am I reading this wrong? – dunct Jun 17 '16 at 15:35
Btw, I don't really get how your query works, can you tell me a bit more of it? – dunct Jun 17 '16 at 20:17
No. You will have different combinations of IllnessID and SymptomsID in your IllnessSymptom table. – Apsar Jun 22 '16 at 08:53

dimlucas · Accepted Answer · 2016-06-17T21:17:10.070

There are two ways you can improve the structure of your database. The first one is simpler but the second one is more strict and completely normalized:

Way 1

Create an illness table:

CREATE TABLE illness(
    id INTEGER NOT NULL AUTO_INCREMENT,
    illnessName VARCHAR(255) UNIQUE NOT NULL,
    PRIMARY KEY(illnessId)
);

Then create a table that uses each ilness' unique id to match it with its symptoms in a 1:n relationship.

CREATE TABLE illness_symptom(
    illnessId INTEGER NOT NULL,
    symptom VARCHAR(255),
    FOREIGN KEY (illnessId) REFERENCES illness(id)ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY(illnessId, symptom)
);

The dual primary key ensures that no symptom is included twice for the same illness.

The fact that the symptom is a string makes it less strict than the following method which is the best:

WAY 2

The illness table remains the same as in way 1:

CREATE TABLE illness(
id INTEGER NOT NULL AUTO_INCREMENT,
illnessName VARCHAR(255) UNIQUE NOT NULL,
PRIMARY KEY(illnessId)
);

Create a whole separate table for storing every possible symptom:

CREATE TABLE symptom(
    id INTEGER NOT NULL AUTO_INCREMENT,
    symptomName VARCHAR(255) UNIQUE NOT NULL,
    PRIMARY KEY(id)
);

The create a third table that matches the id of the illness with the id of the symptom:

CREATE TABLE illness_symptom(
    illnessId INTEGER NOT NULL,
    symptomId INTEGER NOT NULL,
    PRIMARY KEY(illnessId, symptomId),
    FOREIGN KEY(illnessId) REFERENCES illness(id),
    FOREIGN KEY(symptomId) REFERENCES symptom(id)
);

Again the dual primary key ensures that an illness does not include the same symptom more than once

EDIT

After creating the tables you can join them to get match each illness with its symptoms like this:

SELECT i.id, i.illnessName AS illnessName, s.symptomName AS symptomName
FROM (illness AS i JOIN illness_symptom AS is ON i.id=is.illnessId) JOIN symptom AS s ON is.symptomId=s.id
GROUP BY i.id;

An example output would something like this:

1 | Bronchitis | stuffiness

1 | Bronchitis | fatigue

1 | Bronchitis | thightness in the chest

2 | Whiplash | headache

2 | Whiplash | dizzyness

2 | Whiplash | concentration problems

You can read more about inner join here

Thank you for your answer! This looks like the answer above but explained a little better, but does this mean you would have a lot of rows in illness_symptom table when 1 illness has multiple symptoms? — dunct, Jun 17 '16 at 15:37
@DuncanT Yes that's right. It will have multiple lines each for exactly one symptom — dimlucas, Jun 17 '16 at 19:23
I'm sorry, I'm having trouble creating the query to output the illnesses with the correct symptoms. I'm sorry for asking too much! :) — dunct, Jun 17 '16 at 20:22
@DuncanT I edited my answer to include the query and further explanation about it — dimlucas, Jun 17 '16 at 21:17

What's the best way to normalise this database?

2 Answers2