0

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:

intweb
  • 3
  • 2
  • I wouldn't have expected quite that many tables. Are the "additional ingredients" different than "main ingredients" and merit their own table? Or why not have just one "ingredients" table? So I would expect a drug table (by name and drug id), an ingredients table (by ingredient name and id), a form table (form type and form id), and perhaps an ingredient type table. Then you'd have a main drug information table that pulls it all together with a date column. – lurker Jun 04 '18 at 16:58
  • every column has very special type of pharmaceutical ingredients (its for pharmaceutics ) PLUS will be search by every column(type of ingredients), its about 10 difference types in a real pharmaceutical table. other words its not possible to mix all difference type of ingredients . very hope i disturbed clearly situation, im new here. – intweb Jun 04 '18 at 17:25
  • So there is no overlap in main ingredients and additional ingredients at all? If so, then, sure, make them separate tables. But my other comments still apply. If there's overlap, you could still have them in one table and even have a column indicating whether it's special in some way. But certainly whether an ingredient is additional can be determined by whether it appears in the "additional ingredient" column of a drug. it wouldn't have to be a separate table regardless. – lurker Jun 04 '18 at 17:33
  • created Diagram of your suggest (if I got properly ), Please check pic. attached to the post , I've put "form" column into Ingredient table as well. – intweb Jun 04 '18 at 18:26
  • That looks close. Your `ingredients` table has an `ingredients` column, `varchar(5)`. Is that the name of the ingredient? I don't see the `form` column you mentioned. – lurker Jun 04 '18 at 19:44
  • i misprinted, "varchar(5)" has to be "varchar (50)" ,, i put "form" column together with "Ingredients" be course now we have "Type Ingredients", i should renamed tables "Type Ingredients" as "Type Columns" and etc. other words now there is no "form" in the Structure any more, only one more row in "Type Ingredients" aka ("Type Columns") , like this table can have unlimited many to many columns, – intweb Jun 05 '18 at 00:35
  • Ah, that makes more sense. I wondered how you managed to fit an ingredient name in just 5 characters. :) – lurker Jun 05 '18 at 00:57

1 Answers1

1

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.

lurker
  • 56,987
  • 9
  • 69
  • 103
  • if I got right, i guess you missed a field "Ingredient" in table "Ingredients" BUT i think i got your idea about "DrugIngredients-Ingredient is many-to-many (on two different columns)" : many to many by "ingredient_id" and "additional_ingredient_id", I created diagram your variant , in attached pic. About "enumeration type." for "form" so it can hold only one value , seems SET can be converted into array in code. but that makes a search on "form" difficult . Please correct me if im not right. – intweb Jun 05 '18 at 00:38