Entity with composite primary key

Support
#1

Hi,

In my application, I have a table that maintains all codes in the system. The table has 3 main columns: category, code and description, and is used in all drop downs.
The attached file shows an example of some data in this table. The same code can be used across different categories, for example A means Category A in category “CustomerGroup” and means Adjustment in group “TransactionType”.
So the primary key is the combination of Category and Code. Moreover, the drop down fields in other entities should be attributes of type association with the code linked to the code field of the codelkup.
For example, the CustomerGroup field in the customers table should have values such as A, B, C… and the TransactionType in the transactions table should store values such as A, X, Y.
How to implement this in Cuba? Is this feasible?
Of course, one idea is to have a separate ID field for the codes table, and use it as a primary key and foreign key in other tables. But that would defeat the purpose of having a “code” field.

Any hints will be highly appreciated.

Thanks,

CodeLkupExample

#2

Hello,

What about using BaseStringIdEntity as a base class, code as a single-column primary key and adding a unique index on category and code? Also, add a condition on category in the options datasources for references.

#3

Hi Konstantin,

The above would solve it despite the face that this would force the code to be unique per table, and not unique per Category (since this is set as the primary key now).

Thanks.

#4

You are right, it won’t work, I missed the point that CODE must be unique in this case.

So if you want to have a foreign key to such reference table, the primary key must be composite, and referencing table will contain both CATEGORY and CODE for each reference. Is it what you want? Or you may sacrifice the foreign key and use just CODE for reference?