Answered

Custom intermediary table (N:N relationship) in CDS - What is best practice?

(0) Share

Report

Posted on by Medoomi

523

I thought I had my ducks in a row when I created the three tables to form a N:N relationship within Common Data Services.

Table A and Table C both used Autonumbers for the Primary Column (given that when creating the N:N relationship, these needed to be auto-created as well as unique).

Table B (the intermediary table) also used autonumbers for the Primary Column (this also needed to be auto-created)

Am I doing it the best way?

The reason I ask is because I recently found out (to my horror) that the counter on the autonumbers had regressed after migrating the environment manually (I used the data import function in Dynamics), and the autonumbers were handing out duplicates of numbers previously found in the system.

Thanks!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello again @Medoomi ,

I am old school with respect to database design and I manually create the Primary and Foreign keys. A sample structure is shown in the screenshot. In PowerApps, when a many to many relationship is created using the OOB method, the junction table is hidden and cannot be accessed or customized.

In my experience, there are often fields that apply only to the junction table, for example like grade or class location or time of a particular class in the screenshot. These fields do not belong in either the Students table or the Classes table and the Junction table is ideal for them. I also like to use my own ID column for every table, make it a whole number and increment it by 1 whenever I add a new record to that table. I have done this consistently since I began using Access and have continued to do it in CDS even though there are shortcuts provided by PowerApps. I have been using essentially the same database that I constructed in 2003 and have migrated it through from MS Access to Dataverse (CDS). It has been robust and has not failed me for over 14,000 patients with 45,000 visits. The basic principles of database design have not changed. Please see how to design the tables here: https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-Step-3-Keys-and/ba-p/188640 and the follow up with applying the principles to PowerApps here: https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Implementing-a-One-to/ba-p/200521

Was this reply helpful? Yes No
Medoomi 523 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp
Thank you so very much for the links & explanation.
I also like to create primary & foreign keys manually & like to keep the primary key as an incremented autonumber.
Doing this however within CDS, has meant using "autonumber" as the primary key... which brought me to my point of panic, when I found the CDS autonumber field had regressed, and was handing out values that already existed in that field in the database.
I can change the autonumber type to "date prefixed number," which I think would add a level of robustness for the future, but my main question is whether my initial choice of "autonumber" for the primary key was appropriate, and if so, whether the number regression I experienced is a bug in CDS.

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Medoomi

I don't use an autonumber. It seems counterintuitive that it is a text type field and not an integer. I simply create a whole number field and call it ID. When I add a new record, I use First(Sort(tablename, ID, Descending)).ID+1 as the Default property of the ID field. If I am using it on a single form that I use for entering new data as well as editing existing data, I set the default property of the textbox in the ID card as

Coalesce(Parent.Default, First(Sort(table, ID, Descending)).ID+1)

The way it is written, old numbers are never reused, delegation is not a problem since First(Sort( Descending, will always find the last number in the ID field regardless of the number of records in the table.

With regards to Coalesce(), when the form is in New mode, the Parent.Default is null so it puts in my formula and when the form is in Edit mode, the Parent.Default exists and is kept as the value for the Textinput box.

Was this reply helpful? Yes No
Medoomi 523 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp
Thank you so much again for your reply. I like your code & it's elegance & will consider using it in the future. It certainly looks robust as well as simple.
Given that the autonumber data type seems intended for the same purpose (to be used as a primary key), should I not report the number regression I experienced as a bug?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Couldn't hurt.

Was this reply helpful? Yes No