A database View together with the table as entities in Aurelius

BIZ TMS Aurelius
1

In a relational database based on mysql I have about 200 tables and 5 "primary" tables are used by most of the other tables. By "primary" tables I mean tables like a Tcustomer table where a lot of other tables point to this table.
In many cases I am using (read/write/insert/delete) a table's data together with 1-2 fields from the "primary" table it points to but the "primary" table's data do not change
I was thinking of using associations for the field that points to the primary table row like the following:

    [Association([TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('cid', [TColumnProp.Required], 'cid')]
    fcustomer: Tcustomer;

But this includes overhead as the full object is received from the database. I need it, so Lazy property does not helps here.
One possible solution is to create a database view that it is just a join of the two tables with only the fields needed by the primary table eg

create view CustomerRequest as
(
  select r.*, c.name as CustomerName
  from Requests r
  left join customer c on c.cid = r.cid
);

The above view can be an Aurelius entity with "CustomerName" marked as NoInsert, NoUpdate and I believe it will work with insert/update/delete just as with a table.
The problem is that I am loosing the create/update feature of Aurelius as the view can not be updated as it happens with the tables. So probably I can have an entity with the table "Requests" also.
My questions follow:

  1. Is the above a good solution or do you propose something else?
  2. How can I exclude an entity from being updated in its structure if the class is changed (and I know that this entity is a database view)

Do you propose something different?
Or, is it possible to have a "join list" attribute in a tlist element to point to other table with the definition we have for the associations and return a number of fields from the joined table ?
Something like the following:

    [JoinFields([TJoinFields.Required],'fieldname1,fieldname2')]
    [JoinColumn('cid', [TColumnProp.Required], 'cid')]
    fjoinfields: Tjoinlist<string,variant>;

An other solution could be to have an attribute named "View" as we have the "Table" where table creation/update will not happen.
Thank you in advance

2

View approach is possible. Another option is to use multi-model design, having separated classes with less fields, in a different model, and simply use that different model to retrieve data.
Multi-model approach is also used to logically separate the classes that should be used to update the database, and those which won't.

2 Likes
3

My opposition for this situation to the use of Multi-model approach is the caching of retrieved entities that could be changed by the one model and not updated by changes of the other model.
But it can be achieved as different model too. Thank you.

1 Like
4

Remember that you can always use abstract entities to group some common entity mapping in a single ancestor class.

1 Like