CODEWITHSUNDEEP
javamysqlsqlrdbmsarrays
rosa
rosa

Reputation: 55

mySQL database structure

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!

Upvotes: 1

Views: 302

Answers (4)

feeela
feeela

Reputation: 29932

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)

Upvotes: 0

Brandon
Brandon

Reputation: 298

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

Upvotes: 0

Kerrek SB
Kerrek SB

Reputation: 477070

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.

Upvotes: 3

jkeeler
jkeeler

Reputation: 1008

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.

Upvotes: 0

Related Questions