DDD and many-to-many or one-to-many relationships

xotox · 2015-07-31 16:48:22

Hi!
i'm playing around with and discovering mORMot's DDD features using PODOs as DTOs.

Scenario:
There are companies...

TDTOCompany = class(TSynPersistent)
private
  fName: RawUTF8;
published
  property Name: RawUTF8 read fName write fName;
end;

... and employees ...

TDTOEmployee = class(TSynPersistent)
private
  fName: RawUTF8;
published
  property Name: RawUTF8 read fName write fName;
end;

We have many-to-many realtionships between companies and employees. Our pivot-link-table has an additional field "email".

Some demo data:

Company | Employee    | Email
--------------------------------------------
Google  | Bob         | bob@google.com
ebay    | Bob         | bob@ebay.com
Google  | Linda       | linda@google.com
ebay    | John        | john@ebay.com

One and the same Bob is working at google and ebay, having two different email addresses.

How should that DTO class for the pivot table look like? I know about TSQLRecordMany at the ORM layer, and this DTO definition is obviously wrong:

TDTOLinkCompanyEmployee = class(TSynPersistent)
private
  fCompany: TDTOCompany;
  fEmployee: TDTOEmployee;
  fEmail: RawUTF8;
published
  property Company: TDTOCompany read fCompany write fCompany;
  property Employee: TDTOEmployee read fEmployee write fEmployee;
  property Email: RawUTF8 read fEmail write fEmail;
end;

How such relationships should be defined in my DTOs?
Add TDTOLink... array to TDTOCompany and TDTOEmployee?
And another one: is TDDDRepositoryRestFactory.ComputeSQLRecord() capable of creating the right TSQLRecordMany classes in such a case? I guess it isn't, so the mapping DTO<->TSQLRecord has to be done manually.
It feels quite unnatural if I try to define this szenario using some kind of TSQLRecordMany syntax in the DTOs.
Do you have any hints?
King regards.

ab · 2015-07-31 23:20:42

If you use DDD, you use aggregates, so you do NOT define one to many or many to many relationships.

Each node should store all its data in a single aggregate.

The idea is to use a TSynAutoCreateFields class, not a TSynPersistent, which will allocate and own all of its nested fields.
For emails, you could define a TRawUTF8DynArray, or a TEmail = TSynPersistent and register a TEmailObjArray = array of TEmail.

TDTOLinkCompanyEmployee = class(TSynAutoCreateFields)
private
  fCompany: TDTOCompany;
  fEmployee: TDTOEmployee;
  fEmail: TRawUTF8DynArray;
published
  property Company: TDTOCompany read fCompany write fCompany;
  property Employee: TDTOEmployee read fEmployee write fEmployee;
  property Email: TRawUTF8DynArray read fEmail write fEmail;
end;

See http://synopse.info/files/html/Synopse% … #TITLE_552

anvar · 2015-10-13 09:01:39

But in this case in database created one table with more records? Then if in our each Company and Emploee contains 1000 or more record?

ab · 2015-10-13 12:14:02

You are starting your design from the db whereas in DDD you should start from the business logic.

anvar · 2015-10-13 13:10:24

I want to see how my business logic displayed in the database. This will be one, two or three table in Database?

hg.tp.castro · 2016-01-26 12:25:56

Hello,

anvar

Did you get any solution to your question?

ab · 2016-01-26 13:13:40

See http://synopse.info/files/html/Synopse% … ml#TITL_29
and http://synopse.info/files/html/Synopse% … l#TITL_124

For DDD, the idea and process is detailed at http://synopse.info/files/html/Synopse% … l#TITL_167
In this case, there is a single table per aggregate in the database.
This is where a NoSQL database (or even SQLite3) comes to mind.

hg.tp.castro · 2016-01-26 14:15:24

Thanks for the help, but I still have doubts ...

Imagine the following case:

TDetail = class(TSynPersistent)
private
  FField1: RawUTF8;
  FField2: RawUTF8;
published
  property Field1: RawUTF8 read FField1 write FField1;
  property Field2: RawUTF8 read FField2 write FField2;
end;

TDetailsObjArray = array of TDetail;

//Aggregate
TMaster = class(TSynAutoCreateFields)
private
  FMasterID: Integer;
  FDetails: TDetailsObjArray; 
published
  property MasterID: Integer read FMasterID write FMasterID;
  property Details: TDetailsObjArray read FDetails write FDetails; //Dynamic arrays will be stored as BLOB fields in the database
end;

How to perform some "update" the database level, type:

RDBMS, we would have:

update tb_detail set
  tb_detail.field1 = 'A'
where tb_detail.masterid=1

How to perform this "update" in a Blob field / Text?

How to perform this "update" in a NoSQL database?

ab · 2016-01-26 14:24:53

Note that dynamic arrays, once stored using mORMot's DDD entity process, would be stored as a TEXT field containing JSON in the database, not BLOB. This is usually very fast, especially with SQLite3.
Only for MongoDB it would be stored a JSON/BSON array of objects.

Please check the concept of "Aggregate" again.
Everything you need should be within the Aggregate.
You read the aggregate, update the details, then write the aggregate back.
In practice, it would not be slow, since your process would in all cases be bound to the Aggregate.

hg.tp.castro · 2016-01-26 15:44:23

I agree, "Everything you need should be within the Aggregate."

But "You read the aggregate, update the details, then write the aggregate back.", does not apply to a DBA for example, working directly with SQL database / RDBMS without having any contact with the application, nor with the business rules (corrective maintenance data level of a database).

The scenario where you would like to apply DDD, would be an ERP, where a SQL / RDBMS in my point of view would be indispensable, precisely because many changes are not the responsibility of application.

The fact that a SQL / RDBMS indispensable requirement for the project, it would be a limitation for DDD mORMot?

I have a way to keep everything I need within the Aggregate and store in separate tables in the database?

ab · 2016-01-26 15:57:45

It is not mandatory.
It is how it is prepared to work with the mORMotDDD.pas unit, and the TDDDRepository* classes.

You could still use directly the low-level ORM, and support regular RDBMS relationship.
See http://synopse.info/files/html/Synopse% … l#TITLE_77
Or even write your own SQL, using SynDB e.g. to create the JSON which may be directly assigned to your objects, if you wish.
If you need exact integrity reference in your project at database level, then you could do this.

But be aware that you would loose one of the idea/benefit of DDD, which is persistence ignorance, as detailed in the doc.
IMHO such persistence agnosticism is awesome, and worth it.
Here, in production, we have the ability to switch from SQLite3 to PostgreSQL or MongoDB, by just changing a parameter in the settings file of a service. This is priceless.
And in practice, we found out that a SQLite3 in-process database is fast, safe and much easier to work with than alternatives. Even with DB of several GB, writing an aggregate with a huge set of values (15KB of JSON) is less than 100 micro seconds...
Integrity could be safely managed at business logic level.

hg.tp.castro · 2016-01-26 17:48:24

Ok, Integrity could be safely managed at business logic level.

"But be aware that you would loose one of the idea/benefit of DDD, which is persistence ignorance, as detailed in the doc.
IMHO such persistence agnosticism is awesome, and worth it."

But when you need to give maintenance (directly in the database) in fields Blob/Text? For example, it found that the field "X", all the details of sales for a particular month are wrong. I have to pass the field value "X" from "A" to "B".

Como processar tal atualização em um banco NoSql como MongoDB?

ab · 2016-01-26 17:55:07

For MongoDB, or other RDBMS which supports JSON (like PostgreSQL or latest SQLite3), you may use directly some low-level commands to perform the update directly at DB level, for such nested fields.

But IMHO maintenance should also be performed at business logic (Domain) level.
If you need performance (e.g. modify a lot of aggregates at once), use a mORMot TSQLRestBatch, passing all data through the Domain code. In practice, it is very fast.
Or, in your special case, you may change the field content on the fly, when reading the data from the DB, still at Domain level.

mORMot Open Source

#1 2015-07-31 16:48:22

DDD and many-to-many or one-to-many relationships

#2 2015-07-31 23:20:42

Re: DDD and many-to-many or one-to-many relationships

#3 2015-10-13 09:01:39

Re: DDD and many-to-many or one-to-many relationships

#4 2015-10-13 12:14:02

Re: DDD and many-to-many or one-to-many relationships

#5 2015-10-13 13:10:24

Re: DDD and many-to-many or one-to-many relationships

#6 2016-01-26 12:25:56

Re: DDD and many-to-many or one-to-many relationships

#7 2016-01-26 13:13:40

Re: DDD and many-to-many or one-to-many relationships

#8 2016-01-26 14:15:24

Re: DDD and many-to-many or one-to-many relationships

#9 2016-01-26 14:24:53

Re: DDD and many-to-many or one-to-many relationships

#10 2016-01-26 15:44:23

Re: DDD and many-to-many or one-to-many relationships

#11 2016-01-26 15:57:45

Re: DDD and many-to-many or one-to-many relationships

#12 2016-01-26 17:48:24

Re: DDD and many-to-many or one-to-many relationships

#13 2016-01-26 17:55:07

Re: DDD and many-to-many or one-to-many relationships

Board footer