1

I am working on a health app and I've created a database (MySQL), which stores symptoms and diseases.

There should be a N:M relationship between the symptoms and diseases, so each symptom could have multiple diseases and vice versa. Symptoms should also have common and less common diseases and because of that, they should have a ranking (1 to infinity). How should I design/add this new feature?

Current table structure:

disease: d_id, d_name

symptom: s_id, s_name

Engo
  • 899
  • 3
  • 19
  • 49

2 Answers2

3

  1. You require an ordinary Associative table to resolve the logical n::m relationship. The PK of such tables is the two PKs of the parent tables. That gives the required row uniqueness, etc.

  2. Second, it has data columns, so it is no longer an Associative table, it becomes an ordinary table, a binary relation (two parents).

  3. I understand that in the symptoms-per-disease or diseases-per-symptom result sets, the rows will be ranked, but the column containing the value in the table is not rank. It is an indicator of weight given to each symptom::disease. The rank is the order in the result set, from 1 to no-of-rows, it is derived, it will change all the time. The weight is a stable value in the database, from 1 to infinity.

I recommend you give all the constraints names.

Could you show me a query example regarding the constraint names?

Sure. Simply use explicit CONSTRAINT clauses, after the columns, rather than placing each constraint with a single column. You have to do that in the case of compound Keys, such as this one. The naming convention I use for FK constraints is:

    <parent>_<verb_phrase>_<child>_fk

  • The relevance of named constraints, as well as this naming convention, will become clear to you when you have many tables, when you are administering the various objects in the database.

  • The Verb Phrase comes from the data model (had the data been modelled), it describes the Action between the subject table and the object table.

In SQL the DDL looks like this (you will have to translate for your NONsql):

    CREATE TABLE symptom_disease (      -- associative table
        s_id      INT  NOT NULL, 
        d_id      INT  NOT NULL, 
        weight BIGINT  NOT NULL,        -- plus a data column

        CONSTRAINT UC_PK                -- named constraints
            PRIMARY KEY ( s_id, d_id ),

        CONSTRAINT symptom_indicates_symptom_disease_fk
            FOREIGN KEY            ( s_id ) 
                REFERENCES symptom ( s_id ), 

        CONSTRAINT disease_is_indicated_by_symptom_disease_fk
            FOREIGN KEY            ( d_id ) 
                REFERENCES disease ( d_id ) 
        )

For the PK, you want U Unique and C Clustered. I have chosen ( s_id, d_id ) on the assumption that you will have more queries that look up diseases per symptom (than symptoms per disease). If the converse is true, change the clustering to ( d_id, s_id ).

Community
  • 1
  • 1
PerformanceDBA
  • 32,198
  • 10
  • 64
  • 90
  • 2
    Your explanation is truly amazing! Thank you very much for your time and effort! :) – Engo May 21 '15 at 09:56
  • 1
    @Engo. Thanks. It is my pleasure. If you are interested in learning this level of info, go to my profile, inspect my answers, and read anything the interests you. – PerformanceDBA May 21 '15 at 10:04
  • 1
    I have created a new question related to this question. If you are interested: http://stackoverflow.com/questions/30374031/how-to-add-new-relations-and-making-weight-dependent-in-mysql-database Thank you! – Engo May 21 '15 at 12:46
1

You need another table, lets call it symptom_disease which shows the relation between a symptom and a disease. In another word each row of this table will show that a symptom is related to a disease with a specified rank. So look at this:

symptom_disease (s_id,d_id,rank);

In which s_id is foreign key to symptom.s_id and d_id is a foreign key to disease.d_id.

Lets have an example. Consider we have tow diseases like this:

|   d_id   |   name   |
|    1     |   cold   |
|    2     |   cancer |

And tow symptoms:

|   s_id   |    name    |
|    1     |Stomach Ache|
|    2     |   headache |

Now look at symptom_disease table:

|   s_id   |    d_id    |    rank    |
|    1     |    1       |     0      |
|    2     |    1       |     0      |
|    1     |    2       |     5      |

thees rows shows that stomach ache relates to cold by rank of 0 and to cancer by rank of 5; also headache relates to cold by rank of 0.

Saeed
  • 7,262
  • 14
  • 43
  • 63
  • **Is this the right query?** `CREATE TABLE symptom_disease( s_id INT, d_id INT, rank INT, FOREIGN KEY(s_id) REFERENCES symptom(s_id), FOREIGN KEY(d_id) REFERENCES disease(d_id) );` – Engo May 20 '15 at 14:57
  • 2
    Good answer. Two changes (a) don't call it Rank, call it Weight. Rank has a different meaning, eg. in the result set, disease would be ranked by symptom, or vice versa. Here, in the data row, it is a Weight. (b) give him the CREATE TABLE commands. – PerformanceDBA May 20 '15 at 16:34
  • 1
    @Engo. That command is correct, but incomplete. (a) You need `PRIMARY KEY (s_id, d_id)` (b) I recommend you give all the constraints names. – PerformanceDBA May 20 '15 at 17:49
  • Could you show me a query example regarding the constraint names? I am new to this area.. – Engo May 20 '15 at 17:55
  • look at this link, it will help you making foreign keys and naming their constraints: http://www.w3schools.com/sql/sql_foreignkey.asp – Saeed May 21 '15 at 06:37