2. PHPRunner General questions
  3. 1:1 relationships - best handling in PHPRunner
This topic is locked

1:1 relationships - best handling in PHPRunner

 		6/2/2023 4:23:16 AM
PHPRunner General questions
mbintex author

Hi at all,

following this article

Relationsships

I ask myself what the best handling of 1:1 relationsships in PHPRunner projects would be. Use case could be a bunch of property fields, that are necessary on the one hand, but are seldomly used and filled with data - for example extensive settings for the app.

The master details in PHPRunner-standard can be well used for 1:n relations with their new button, inline adding and list view. But in case of 1:1 I wouldn´t need the new button and a form view instead of the list.

What would be the best practise for this in PHPRunner?

Thinking of

  • automatic adding of related rows (no new button) and the list view as a cards view mimicking a sub form
  • adding the 1:1 data in a modal with link or custom button
  • adding the 1:1 data in "further data/settings/whatever" in a completely separate screen
admin 6/6/2023

If you are really looking for a "best" way - do not use 1:1 relationships. From the database theory point of view, 1:1 relationship is just splitting a single table into multiple tables and it doesn't really provide much benefit.

A better option is just to use a single table for this purpose and use a bunch of views ( both database views and custom views in PHPRunner ) to present the data in several different ways.

mbintex author 6/6/2023

Following reasons lead me to this idea:

a) settings fields in settings table are likely to become more and more
b) on the other hand many users if not most users will never choose any of the offered settings
c) even if a settings field stays empty, I guess it´ll consume a little space and uses a little fetch and query time
d) spreading all the settings in separate tables like "print settings", "module settings" etc. might not only be good for the user, but for me as the developer too, to keep the oversight

When we are at it, what do you think about using Json fields for many settings and "exploding" them to fields for query in PHPRunner? Like this

SELECT
JSON_EXTRACT(json_column, '$.surname') AS surname,
JSON_EXTRACT(json_column, '$.middlename') AS middlename,
JSON_EXTRACT(json_column, '$.familyname') AS familyname
FROM your_table;

and then for writing to the database constructing the JSON with PHP like this

$json = json_encode($data);