CODEWITHSUNDEEP
phpmysql
Kevin Michael
Kevin Michael

Reputation: 31

Advice regarding DB design for DB with varying/dynamic number of fields

I am building a drug information web application using PHP/MySQL. I do not know how do deal with the need for varying numbers of fields. For example, side effects. One drug may have three side effects, and another 30. I have thought of two potential solutions, both seem awful:

  1. Have 30 columns, e.g. side_effect_1, side_effect_2, etc., and have a lot of null values in those columns for most drugs.

  2. List all the side effects in one column, comma delimited, and then figure out how to deal with that (I guess as some sort of nested array? Wouldn't that end up being an array within the array of whatever query PHP is sending?)

I'm a newbie, but I swear I've spent hours trying to find solutions online, to no avail. There does seem to be a sentiment out there that this issue (dynamic number of fields) may be best handled by a NoSQL database. Though most of my fields will have nice, neat, one-to-one relationships, there are a handful (side effects, indications, contraindications) that will vary wildly in their number. Any advice would be greatly appreciated.

Upvotes: 0

Views: 30

Answers (1)

Fleshgrinder
Fleshgrinder

Reputation: 16303

You are dealing with two distinct kinds of data here. On the one hand you have the drugs, and on the other the side effects. This directly gives you the answer to the question: you need two tables. Now to connect those two you need another that takes care of doing exactly that.

┌───────┐    ┌───────────────────┐    ┌──────────────┐
│ drugs │    │ drug_side_effects │    │ side_effects │
├───────┤    ├───────────────────┤    ├──────────────┤
│ id    │    │ drug_id           │    │ id           │
│ name  │    │ side_effect_id    │    │ name         │
└───────┘    └───────────────────┘    └──────────────┘

Not only can you have n side effects per drug now, you can also ensure referential integrity and avoid duplicates (or more) in your storage.

Upvotes: 2

Related Questions