Manage tables with "one to many" relationship

George Tselios asked on September 29, 2020 10:37

Dear All,

I need to store and manage tubular data in a master - detail form, meaning the resulting database tables will have a "one to many" (1:N) relationship. For example, let us say that I would need to manage and store Countries and their respective Cities.

I would like to know if there is an Application or any other "Out Of the Box" feature in Kentico that would provide the following:

  1. Provide the CRUD interfaces in Kentico Admin for both tables for the authors to manage the data. The interfaces should take under consideration the relationship between the two tables.
  2. A Querying API and Data Objects to query either of the two tables and retrieve data in the portal.
  3. The Querying API should also support caching.

The Custom Table Data Application supports all three of the above features but, to my understanding, does not support relationships between tables.

Thanks in advance,
George

Recent Answers

David te Kloese answered on September 29, 2020 11:32

Hi,

I would recommend you build a Custom module (docs.kentico.com/.../creating-custom-modules)

You can then create custom classes, enforce your requirements and create dedicated code files for (provider and classes). So you can also get strongly typed objects in code. You'll have to setup your own caching but that is probably more optimized if you base it on your own requirements.

If you want you can even create your own constraints on DB level, but you can also handle that in your code.

When using this setup you can quite quickly setup the editing interface for the objects: docs.kentico.com/...#Creatingcustommodules-Buildingthemoduleinterface"

Also this combines automatically with the roles and permission module, so you can setup permissions for different roles on whom can access and modify what.

1 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on September 30, 2020 15:20

As David stated, a custom module with custom classes would be best. Keep in mind though you would want to have your FK and PK setup to be GUID's so you can stage them from one environment to another without loss of data. Using the PrimaryKeyID of a table is not ideal as they can change from environment to environment whereas the GUID persists across environments AND is used for the Staging application to move content from one environment to another.

Here's a reference for building binding classes.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.