Trying to reference two or more tables

[Gideon2]

Hi leaders, trust your day is going well??

I have an shopping cart app going on but l am stuck at a challenge: 

I have several tables among which are important in this discussion are OrderDetails, Products and Modifiers (like a Product Subtype)....

I have a column "OrderDetails_Product" in Order Details table that is reference to the "Product" table so it works perfectly in such where a product is selected, it shows in the OrderBuilding tab

 

....the problem lies with the modifier( Product Subtype) , it fails to display same way the Product display ...the product that display unique numbers are the modifier , while the ones that displays the name are the product... I have thought the only way is to reference both tables( Products and Modifier) which seems impossible for me to do now... Is there other ways to go about this??

[TeeSee1]

You cannot reference two different tables in a single column.

I would either create two separate columns for each of Products and Modifiers or combine them into one table and add a field to distinguish the two types.

The choice depends on how two types are related and how you need to handle them in various business scenarios (purchase, production, inventory, etc...) so you need to consider carefully what you need to accomplish.

I would also typically use a simple ref type rather than ENUM of ref..