Multiple foreign key to 1 tabe

0
Hello Team   I have a situation where i have 2 columns in tableA and both are foreign keys of tableB for example TableA has columnX, and columnY ColumnX is referenced to a row in tableB ColumnY is referrenced to another column in tableB   How do i create this in Mendix   Thank you
asked
5 answers
1

Dear Mohammad,

In Mendix, Foreign keys/references are managed through associations, which are at the entity-entity (Table-Table) and not at the attribute (Column) level.

In you case you will create three entities (Tables):

  1. Entity-1 : To store ColumnX 
  2. Entity-2: To store CoumnY
  3. Entity-3 : Equivalent to your TableB

Then you will crate two associations of type one-one (1-1) or one-many (1-*) or many-one (*-1) based on your business need.

  1. Between Entity3-Entity1
  2. Between Entity3-Entity2

 

Hope this helps,

Shekhar

answered
1

Shekhar’s explanation is ok, but possibly the solution might be simpler (depending on what you are trying to accomplish). You have tableA and tableB and can create multiple associations between them. See this example where your tableA is Department and tableB is Employee:

A department has one employee that is assigned as DepartmentManager.

A department also has several employees that are assigned as DepartmentStaff.

So one employee can have both associations with Department, or only one of the two, or none.

answered
0

Hi

 

Thank you. So from Shekhar’s explanation, it is clear that we cannot create associations at column level. The provided solution will work but it is quite complex

 

How about Tim’s solution. This is still at entity level, but will it work in my case?

in fact, situation is as follows

LookupType

code        description

color       color

Block      Block

 

lookup value

color     Red

color     Blue

color     green

Block    A

Block    B

 

AssetProperty

Lot(color)            Block

Green                  A

Red                     A

answered
0

Hi depending on the amount of records it might have, there is also an option to go completely de-normalized.

Remember, associations between more entities and number of records would also give you performance issues in the long run. In which case, it is simpler to decide the solution based on the amount of data. 

answered
0

Hi

 

what if i create it as follows

in that case, and AssetProperty can have multiple rows, rather than multiple columns

As such, i can have data as follows

Asset                                 AssetProperty

AssetCode 123                Row 1: LookupValue Red

                                           Row 2 : LookupValue  B

 

answered