----- Original Message ----- From: "vijaya raj" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, August 02, 2004 7:11 AM Subject: help-in-normalizing
> hi friends > i am trying to create a data model for my new database. > i have some problems in normalising my data model. > i have two tables with many-to-many relation. > how to i normalize them to one-to-many form. > > ---------------------- > molecule_info > ---------------------- > mol_id > mol_name > ----------------------- > > ----------------------- > plant_info > ----------------------- > plant_id > plant_name > ------------------------ > > here, i one mol_name is found in more than one plant_name. > the same way, one plant_name has more than one mol_name. > how do i make them one-to-many. > The standard way of normalizing a many-to-many relationship is to turn it into two one-to-many relationships via a third table. This third table is usually called either an "association" table or an "intersection" table. In your case, the "intersection" table would look like this: plant_molecule ----------------- plant_id molecule_id The primary key of the plant_molecule table would be the *combination* of the plant_id and the molecule_id. Also, plant_id would be a foreign key pointing back to the plant_info table and molecule_id would be a foreign key pointing back to the molecule_info table. Therefore, the tables would look something like this, using bogus data: PLANT_INFO plant_id plant_name --------- -------------- 1 orchid 2 hibiscus 3 sycamore MOLECULE_INFO molecule_id molecule_name ------------- ------------------- A potassium dichromate B sodium calcite C benzine D cellulose E potassium nitrate PLANT_MOLECULE plant_id molecule_id --------- -------------- 1 A 1 D 2 D 2 B 2 C 3 E 3 D The intersection table lists the combinations of primary keys that actually exist. If you don't remember what the ids represent, you can always join the intersection table back to the one or both of the original tables to see what they mean. By the way, the intersection table can also be used to store additional information, provided it is about the *combination* shown in its row. For example, if you want to show the quantity of each molecule in each plant, it would be appropriate to add that to the intersection table like this: PLANT_MOLECULE plant_id molecule_id quantity --------- -------------- -------- 1 A 3.0E12 1 D 4.7E09 2 D 2.1E05 2 B 9.8E22 2 C 0.8E08 3 E 0.1E31 3 D 6.2E03 Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
