Database Design Tips-CodePudding

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.

CodePudding user response：

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.