CODEWITHSUNDEEP
sqldatabasedatabase-design
intweb
intweb

Reputation: 3

Many of many to many?

I have a big Excel table (please see pic for sample of table). I need to transfer it to a MySQL database.

It seems like I have ended up with a lot of many to many tables. (please see pic of DB Structure of the table)

For whatever reason it just doesn't feel right, are there better ways to build DB Structure of the table?

the table

my Structure

Alternative structure:

enter image description here

Alternative structure 2:

Upvotes: 0

Views: 128

Answers (1)

lurker
lurker

Reputation: 58244

I would structure this way:

Drugs(id, drug_name, drug_date)
Ingredients(id, ingredient_type_id, ingredient_name, form)
IngredientTypes(id, ingredient_type)
DrugIngredients(id, drug_id, ingredient_id, additional_ingredient_id)

DrugIngredients-Drugs is many-to-one, DrugIngredients-Ingredient is many-to-many (on two different columns), and Ingredients-IngredientTypes is many-to-one. In Ingredients, the form column could be an enumeration type.

Upvotes: 1

Related Questions