-1

I must have an efficient way to connect two records from a table.

The table holds records of Laboratory tests which have a start date and a duration in days. I got a request from my client that they want to have a connection between the tests (e.g. Test2 starts after Test1 ends) and if the first test is postponed therefore also the second test will be postponed. Also they would like to have tests inserted where they can specify that the test2 starts with test1.

How do I implement it in Dataverse tables?

I tried using modelling a table "Connection" where Test1, Test2, and Connection Type are the primary Keys and Startdate is a column. But I have no idea how to deal with the option that there is no connection.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Stephan Pörnbacher
  • 3
  • 2

1 Answers1

0

You have a Test table

Test
----
Test ID
Start date
Duration in days

Where Test ID is the auto-incrementing integer blind primary key.

You also have a Connection table

Connection
----------
Connection ID
Starting Test ID
Connecting Test ID
Connection Type (start-start, end-start)

Where Connection ID is the auto-incrementing integer blind primary key. You also have unique indexes on (Starting Test ID, Connecting Test ID) and (Connecting Test ID, Starting Test ID).

You can access the Test table directly, or you can access the Test table through the Connection table.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • How do I deal with the tests following an other one? Since the start date has to change depending on duration and start date of previous tests. Just updateing it if a connection is changing? Do the unique indexes help me in beeing sure that there are no recursive connections made? – Stephan Pörnbacher Dec 13 '22 at 12:56
  • Yes, just update the start date when the previous start date or duration changes. No, the unique indexes don't help you be sure that there are no recursive connections. That's one purpose of the blind primary keys., If you duplicate a primary key in a chain, you're recursing. – Gilbert Le Blanc Dec 13 '22 at 16:29