[semi OT] mySQL question

Bob Sneidar bobsneidar at iotecdigital.com
Fri May 12 18:00:58 EDT 2023

Just as an aside, I do not bother with joins, if I can help it. If I need data from multiple tables I query each table for just the data I need, and save it in discreet arrays, 1 per table. Of course, my needs are pretty simple. I typically have one parent table (the example suggested calls them Strong Entities) and and I only query data from the "child" tables (Weak Entities) as needed. 

My use case for example is: 


etc. (entities in brackets like SiteContacts are just contacts with the sites they are linked to in a itemized string. Contacts are not dependent on sites but ARE dependent on Customers.)

I only query for one customer at a time, then all the sites for that customer, then all the devices for the selected site, then all the accessories for the selected device. As the user selects each Strong Entity, I query for the Weak Entities as necessary. 

I like doing it this way because having discrete arrays for each entity type makes coding for me much easier, and viewing the data for each table easier. The code necessary for querying for the Weak Entities I need is done in LC Script. It's much easier to grok than a complex SQL statement. 

I understand that there are definitely use cases where joins should be employed, such as finding all the sites who no longer have a customer (something I have since coded to prevent, but not before end users managed to orphan some sites.) 

I think too often people who really understand and are adept at SQL tend to program in SQL instead of the language of the front end. To me this makes the solution much less manageable. Also, I am not that adept at SQL (and hope I never need to be.) :-)

Bob S

More information about the use-livecode mailing list