CODEWITHSUNDEEP
databaserelational-databaseone-to-many
user2038583
user2038583

Reputation: 11

how to implement one to many and many to many relation in database

I have a data for rubber belts where one compound has many chemicals and these many chemicals can be combined in any combination to form a new compound(one chemical in many compounds). I have created one table for compounds and one for chemicals. How do i form a relational table without huge repeat of data?

Upvotes: 1

Views: 100

Answers (2)

user1914530
user1914530

Reputation:

No doubt there's a huge number of tutorials on the net if you do a search. You should do some research or get a good database text. To help you out here's an example of your many to many relationship realised as one to many tables with an associated CompoundChemical table.

CREATE TABLE Compound
(
  CompoundId INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
);

CREATE TABLE Chemical
(
  ChemicalId INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
)

CREATE TABLE CompoundChemical
(
  CompoundId INT NOT NULL,
  ChemicalId INT NOT NULL,
  PRIMARY KEY (CompoundId, ChemicalId),
  FOREIGN KEY fk1 (CompoundId) REFERENCES Compound(CompoundId),
  FOREIGN KEY fk2 (ChemicalId) REFERENCES Chemical(ChemicalId)
)

Upvotes: 2

Dragan Radivojevic
Dragan Radivojevic

Reputation: 2002

Adding table structures will help but you can probably go with something like this

Creating one table for compounds and one for chemicals is good but you'll most probably need one more table because there is no fixed number of chemicals in each compound.

Maybe table named Substances that has following columns that reference chemicals and compunds. (ChemicalID, CompoundID)

Upvotes: 0

Related Questions