CODEWITHSUNDEEP
mysql
Bumpy
Bumpy

Reputation: 97

Two table primary reference to one primary key on another table

Im here with a little problem with my inventory and I've been pondering with idea of this little schema here. Is it possible or is there another way I can achieve this kind of schema below? enter image description here

I can cheat my way through this problem by just having my application identify what itemID should be inserted into the tbl_stocks but I fear when the ID of either medicine or bandage is updated, that would leave an issue in my tbl_stocks. Any suggestions on how to achieve my target?

Upvotes: 0

Views: 161

Answers (1)

Varon
Varon

Reputation: 3916

Normally you have 2 entries in such a table that can became different shapes.

So add a reference for both tables to tbl_Stocks one column Medicine_ID VARCHAR(10) NULL and a Bandage_ID VARCHAR(10) NULL So of curse you have a null column in this table for every entry, but later you can easy join this table.

If more and more tables come as expected you need a crosstable for each "Shape" .. One Medicine_ID x tbl_Stocks and one Bandage_ID x tbl_Stocks. This is fast and a good design, but also brings more maintenance effort.

Upvotes: 1

Related Questions