Lookup vs. select object and multiselect option Topic is solved

[justme636]

Hi there!

I'm setting up a new database application with nuBuilder and struggeling with a basic question (that actually I haven't noticed before, funny...): When to use a select object and when a lookup object? From a plain technical point of view, they seem to do the same: Saving a foreign key. Of course, I know that the lookup object shows more information than just a single value (as the select object does) and offers creating/editing/deleting the record of the foreign key. However, I could also open a second table and create a new record directly, which can then be chosen with in the first table via select object, couldn't I? So apart from the - let's say - "usability advantages" the lookup object offers, are there any - rather technical/basic - limitations or advantages of each object to consider, before deciding to use either lookup or select object for table relations?

To this question, I noticed that the select object offers the multiselect option for saving multiple foreign keys. I suppose, the FKs are stored in a single JSON column. First I thought this would be a great technical advantages of select objects, since I don't need to create the "third table" for n:n relations. I've searched a bit, to get more information on this very convenient solution. All in all, I've found rather negative feedback for this solution bypassing the thrid table:

First:

"Note that JSON columns can’t have a default value, be used as a primary key, be used as a foreign key, or have an index."
(https://www.sitepoint.com/use-json-data ... databases/)

Obviously, they can and it works. But regarding database architecture and normalization:

"You should not store an array of foreign keys. That's not a valid relational data design."
(from: https://stackoverflow.com/questions/713 ... -data-type)

So, I wonder a bit: When storing multiple foreign keys in a single columns is regarded as no good practice, should I use it at all? What was the idea for implementing this feature?

[kev1n]

Hi,

In addition to what you've already mentioned, the Lookup object consists of a code and description field, and PHP code can be executed in the After Browse event, for example, to populate other objects on the form.

Concerning the multiselect option in select objects for saving multiple foreign keys, while it may appear convenient to store multiple foreign keys in a single JSON column, there are potential drawbacks to consider:

Data Structure Complexity: Storing multiple foreign keys in a single column complicates the data structure and may make it harder to query and maintain the data.

Query Performance: Queries involving JSON data can be less efficient compared to traditional relational queries, especially as the dataset grows larger.

Data Integrity: It may be more challenging to enforce referential integrity constraints when storing multiple foreign keys in a single column.
Normalization: Violates the principles of database normalization, which can lead to data redundancy and inconsistency.

That "feature" allowing storage of multiple foreign keys in a single column might have been implemented for simplicity, offering a quick solution for managing many-to-many relationships without creating a separate junction table. However, this approach may not align with best practices in database design, potentially leading to issues with data integrity and performance.
If the current behaviour doesn't meet your specific requirements, you have the flexibility to change this behaviour using PHP or JavaScript to save/load data from a third table.

[justme636]

Thank you for the detailed explanation to my questions!

So for "lookup vs. select" I would recap: In their technical functionality (storing FKs) they are interchangeable. The difference, however, lies in their usability and customization. Here the lookup offers browse/filter view, create new records, description and additional code.

Regarding the multiselect option with storing multiple foreign keys as an array, I already thought that it was implemented rather as a quick and simple solution. I know there is a limit to where simplicity and practicability beat following strictly each and every rule. So, for offical/productive data and tasks I would use a third junction table. However, as quick workaround and for "internal" tasks, the mutliselect option with an foreign key array is still a good option.

Problem solved! Thanks again!