CODEWITHSUNDEEP
sqlsql-serverdatabase
Ronald
Ronald

Reputation: 1542

MSSQL - Two tables with more than one relationship

enter image description here

Given a product, my intention is to be able to create a conversion table that will serve as a lookup when converting from one unit of measure (FromUomId) to another (ToUomId). Both IDs can be found in the UoM table.

The conversion values will be supplied by the user, and will be stored in the FromUnit and ToUnit fields respectivley. For instance given a source UoM of Kilos and a target UoM of Grams the FromUnit field will contain 1 and ToUnit field will contain 1000.

However, while it make sense to me, I find the relationship to be odd in that there are two relationships that exist between Conversion and UoM tables.

My question is, is it acceptable to have this kind of multiple relationship?

Upvotes: 2

Views: 1068

Answers (1)

dani herrera
dani herrera

Reputation: 51735

The answers is yes it is acceptable and this is the right way to do it.

When you write joins remember to assign alias to UoM table:

Select *
from Conversion C
inner join UoM as UoMfrom on UoMfrom.UomId = c.FromUomId
inner join UoM as UoMto on UoMto.UomId = c.FromUomId

Notice than, because UoM table appears for twice in sentence, you must assign a different alias for each occurrence.

Upvotes: 2

Related Questions