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):
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.
Hope this helps,
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.
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
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.
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
AssetCode 123 Row 1: LookupValue Red
Row 2 : LookupValue B