mySQL database structure

Question

I'm kinda stuck in a tricky situation with the mySQL DB design for my webservice.The DB had initially this Structure:

   CREATE TABLE IF NOT EXISTS `Disease` (
   `Name` varchar(20) NOT NULL,
   `Age` int(10) unsigned NOT NULL,
   `Descriptin` text NOT NULL,
   `Sex` varchar(10) NOT NULL,
   `Ethnicity` varchar(20) NOT NULL,
    PRIMARY KEY (`Name`,`Sex`,`Ethnicity`),
    KEY `Sex` (`Sex`),
    KEY `Ethnicity` (`Ethnicity`)
     ) 

  ALTER TABLE `Disease`
   ADD CONSTRAINT `Disease_ibfk_1` FOREIGN KEY (`Sex`) REFERENCES `Sex` (`Sex`)     ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Disease_ibfk_2` FOREIGN KEY (`Ethnicity`) REFERENCES `Ethnicity` (`Ethnicity`)     ON DELETE CASCADE ON UPDATE CASCADE;

So basically Disease(Name,Age,Description,Sex, Ethnicity ) Where Sex and Ethnicity are foreign keys to two tables named Sex and Ethnicity because they can have more than one value.

Now to the question I need to add another Column called Symptoms which will be multivalued but I cant declare it as a foreign key, what i need is this:

example of a row

Disease(Name="DiseaseName",Age="40",Description="is caused by...",Sex="male",Ethnicity="Caucasian",Symptoms"Headache,sorethroat,fever")

So basically i need Symptoms to contain a String of Array but apparently I cant do it since its an RDBMS

Thanks all for you time and efforts!

Kerrek SB · Accepted Answer · 2011-08-20 23:46:03Z

3

Don't do that. Instead, normalize your data model: Make a new table "Symptoms", constrained with foreign key "Disease", and make one record for each symptom.

Whenever you start thinking about putting collections of data into a single field, you're effectively trying to build your own mini database inside the database. Thinking that you can outperform and outwit your RDBMS is optimistic at best and most likely leads to unmaintainable code later on.

By the way, does Sex really have to be looked up in a separate table? For micro-categories like that you might like to consider some sort of enum type.

edited Aug 20, 2011 at 23:46

answered Aug 20, 2011 at 23:33

Kerrek SB

480k96 gold badges904 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rosa Over a year ago

Yes i have actually implemented two other columns Sex and Ethnicity the way you said, the issue is that they don't solve my need. Is there any other way around it?

Kerrek SB Over a year ago

@Rosa: Well, how about an enum?

rosa Over a year ago

Actually just adopted enum for Sex :) and it works thanks! But again for the original question it wont work, since using enum you can store one value per record

Kerrek SB Over a year ago

@Rosa: You mean for the symptoms? No, sure, you cannot use enums for symptoms, least of all because there isn't a bounded, predetermined set of symptoms. As I said, make a separate "Symptoms" table and refer each symptom to a disease.

jkeeler · Accepted Answer · 2011-08-20 23:37:47Z

0

You can accomplish the "array of strings" you're looking for by normalizing your data. Add a new key column to your 'Disease' table. Then create a child table called 'Symptom'. Insert a record for each string with a foreign key back to the 'Disease' table parent record.

answered Aug 20, 2011 at 23:37

jkeeler

1,0186 silver badges18 bronze badges

1 Comment

rosa Over a year ago

Let me explain what i want to accomplaish: A query on table Disease will match the Sex,Ethnicity and age of a patient and the "list" of symptoms he has and then return the name of the disease.How could i do this? Is it possible?

Brandon · Accepted Answer · 2011-08-21 00:15:28Z

0

In case you didn't notice you misspelled Description in your table creation query.

answered Aug 21, 2011 at 0:15

Brandon

2982 silver badges10 bronze badges

1 Comment

rosa Over a year ago

actually the original is in Italian, just changed it in English here for better comprehension, Thanks anyways.

feeela · Accepted Answer · 2011-08-21 00:21:59Z

0

You need a m:n relation:

Table: Disease
- Name
- Sex
- Ethnicity
Table: Symptoms
- ID
- Name
Table: Disease_has_Symptoms
- Name (FK to Disease)
- Sex (FK to Disease)
- Ethnicity (FK to Disease)
- ID (FK to Symptoms)

(Maybe it's easier to add an ID-column to Disease and reference that inside Disease_has_Symptoms)

answered Aug 21, 2011 at 0:21

feeela

30k7 gold badges61 silver badges73 bronze badges

Collectives™ on Stack Overflow

mySQL database structure

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related