How to store multiple categories for a column

[hapax]

I need to create a table something like this:

Patient table
----------
ID
Name
Categories

The thing is, for each of these records there can be multiple Categories. Something like this:

ID: 321
Name: Bob
Categories: Flu, Diabetes, Cancer

How can I do this? I need a separate Categories table, I'm sure, but how do I store multiple Categories in my Patient.Categories column?

 

[gmmastros]

Usually it is like this...

PatientTable
------------
PatientId
Name

Categories
------------
CategoryId
CategoryDescription

PatientCategories
-----------------
PatientId
CategoryId

If a patient has multiple categories, they would have multiple rows in the PatientCategories table. The PatientCategories table should have a primary key on BOTH columns. Since there is likely to be more patients than categories, you should probably list the PatientId first.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Ex:

[tt]
Patient
PatientID Name
--------- ------------
1 George
2 Fred
3 Barney

Categories
CategoryId Name
---------- -------------
1 Flu
2 Diabetes
3 Foot-In-Mouth itis

PatientCategories
PatientId CategoryId
--------- ----------
1 2
1 3
2 1

[/tt]

Based on the data shown, you can tell that George has Diabetes and "Foot-In-Mouth itis", Fred has the Flu, and Barney appears to be healthy.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hapax]

Much thanks to you both. I knew this was a pretty common thing but didn't remember how.

 

[markros]

You can google on 'junction table'

PluralSight Learning Library