mORMot Open Source

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Best way to map a many-to-many relationship with a cross-table

Hi there!

I have three tables in my database, e.g.:

- TStudent (ID, Firstname, Lastname, Age)
- TProf (ID, Firstname, Lastname, Age, Title)
- TLecture (a cross table between TStudent and TProf): in this table I want to save only two references to TStudent and TProf,

What would be the best way to map the cross-table?

1. TLecture
    published
       StudentID: TID;
       ProfID: TID;
    end;

2. TLecture
    published
       Student: TStudent;
       Prof: TProf;
    end;

In the 2d one, what is the best way to load the Student and Prof properties?

Thank you very much for your answer!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,205

Website

Re: Best way to map a many-to-many relationship with a cross-table

2th variation would allow to use CreateJoined() constructor, with automatic allocation and filling of the Student and Prof fields.

Otherwise, this is the same at storage level (the ID is stored).

Also consider a 3rd possibility: copy some fields of the student and professor information into the TLecture, in addition to their ID.
Unnormalizing (or sharding) data is a common practice in NoSQL, and ORM.