One to Many Relationships with Join Table

[Braden]

Is it possible to create a one-to-many relationship while using join tables?  For instance, I could have a table called "File" that stores the information for all of the files stored in my application.  I could then have several different tables that all have files and thus all have join tables between themselves and the File table, with the File Id being the primary key.  If I put all of these tables into the designer, without using join tables, the relationships register correctly.  However, once I turn the join table into a join table in the designer, the relationship between the two end tables turns into a many-to-many with no way to change it.

[Doroteya]

Hello Braden,

Generally, OpenAccess ORM uses join tables when it needs to model many-to-many relationships.

Regarding the scenario you implement, it seems that you have already created the structure of the database and you are using our Database First approach, in order to create the model in your application.

However, we were unable to understand completely the design of the schema and it would be really helpful from your side, if you could provide some kind of sample visual representation that would illustrate it.

Thank you in advance for the cooperation. We are looking forward to solving the issue with you.


All the best,
Doroteya
the Telerik team

[Braden]

So, I start off with a File table that stores file info accross the application:

create table File
(
     Id int identity(1,1) primary key,
     Key varchar(256),
     Name varchar(256)
)

I want to reference this table from more than 1 location, and in this particular instance, I have a table (let's call it "Record") in which each record can have more than 1 File, but a File can only belong to 1 record.

create table Record
(
     Id int identity(1,1) primary key,
     SomeData varchar(256)
)

create table RecordFile
(
     FileId int not null primary key foreign key references File(Id),
     RecordId int not null foreign key references Record(Id)
)

Based on the above, RecordFile is my join table between Record and File.  Since RecordId is a foreign key, it should be represented as a Record can have many RecordFiles (and thus Files).  And since FileId is both a foreign key and a primary key, it should be represented as a File can have 1 RecordFile (and thus 1 Record).  However, when OpenAccess generates the relationship, it still represents it as many-to-many.

[Doroteya]

Hello Braden,

Thank you for the detailed information.

Generally, the described representation is the expected one for OpenAccess ORM. According to our design, if the database model contains a table which consists of foreign keys only, it will be mapped as a join table in the domain model and the association between the domain classes connected through it will be visualized as a many-to-many association.

In the given case, you can achieve a more accurate representation of the domain model using the following steps:
1. Open the .rlinq file in Visual Designer and go to Model Schema Explorer
2. In the Tables node, select the RecordFile table and press F4 to open its details in the Property window
3. Set IsJoinTable to False and save the model
4. Drag-and-drop the RecordFile table on the surface of the designer to create a domain class for it
5. Delete the initial many-to-many association and save the domain model

With that workflow you will make only visual changes in the model that will not lead to eventual changes in the storage model. You can verify that by starting the Update Database from Model wizard and selecting Migrate Database.

I hope you find that feasible. If you have additional questions or experience difficulties, do not hesitate to get back to us.


Regards,
Doroteya
Telerik