Re: [semi OT] mySQL question
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:
Customer
[CustomerFiles]
Sites
Devices
Accessories
{DeviceFiles]
Service
[ServiceDevices]
Subnets
[SiteContacts]
[SiteFiles]
Contacts
Files
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
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
Re: [semi OT] mySQL question
Hello Ken, Thank you so much for your answer. Your solution confirms what I had more or less in mind. I will give it a try asap. Best, jbv Le 2023-05-11 15:33, Ken Ray a écrit : I would recommend a table for People, a table for Companies, and a table for Employment that acts as a linking table between the first two. Something like (at its simplest): people id INT(11) first_name VARCHAR(255) last_name VARCHAR(255) companies === id INT(11) name VARCHAR(255) employment id INT(11) person_id INT(11) company_id INT(11) job_descrip VARCHAR(255) To get a list of everyone that worked at a particular company, you'd do: SELECT * FROM people INNER JOIN employment ON (people.id [1] = employment.person_id) INNER JOIN companies ON (employment.company_id = companies.id [2]) WHERE companies.name="XYZ, Inc." To get a list of all companies where a person has worked, you'd do: SELECT * FROM companies INNER JOIN employment ON (companies.id [2] = employment.company_id) INNER JOIN people ON (employment.person_id = people.id [1]) WHERE people.first_name="John" AND people.last_name="Smith" Make sure to index the 'id' and '_id' columns, and you can use the employment.job_descrip to hold info on the particular job an individual had at a specific company. This is all off the top of my head, so it may need a little massaging, but you get the idea. Ken Ray Sons of Thunder Software, Inc. Email: k...@sonsothunder.com Website: https://www.sonsothunder.com On May 11, 2023, at 9:55 AM, jbv via use-livecode wrote: Hi list, This is a question for mySQL experts, which I am not. I have a table of individuals. I have another table of companies. Many individuals have worked in more than 1 company with different jobs descriptions. I need to build my DB so that, with single requests, I can get a list of either : - all individuals who worked in a specific company with the job description of each one - all companies in which a specific individual has worked, along with the job description he had in each company. The main problem is that the tables might become huge with the time (several Gb), so searching the tables might take forever if not carefully built. I guess I will need to use a third table for the job descriptions and a combination of fulltext indexes and foreign keys might do the trick, but my limited skills don't allow me to find the best solution... Any help will be much appreciated. Thank you in advance. jbv ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode Links: -- [1] http://people.id [2] http://companies.id ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: [semi OT] mySQL question
I would recommend a table for People, a table for Companies, and a table for Employment that acts as a linking table between the first two. Something like (at its simplest): people id INT(11) first_name VARCHAR(255) last_name VARCHAR(255) companies === id INT(11) nameVARCHAR(255) employment id INT(11) person_id INT(11) company_id INT(11) job_descrip VARCHAR(255) To get a list of everyone that worked at a particular company, you'd do: SELECT * FROM people INNER JOIN employment ON (people.id = employment.person_id) INNER JOIN companies ON (employment.company_id = companies.id) WHERE companies.name="XYZ, Inc." To get a list of all companies where a person has worked, you'd do: SELECT * FROM companies INNER JOIN employment ON (companies.id = employment.company_id) INNER JOIN people ON (employment.person_id = people.id) WHERE people.first_name="John" AND people.last_name="Smith" Make sure to index the 'id' and '_id' columns, and you can use the employment.job_descrip to hold info on the particular job an individual had at a specific company. This is all off the top of my head, so it may need a little massaging, but you get the idea. Ken Ray Sons of Thunder Software, Inc. Email: k...@sonsothunder.com Website: https://www.sonsothunder.com > On May 11, 2023, at 9:55 AM, jbv via use-livecode > wrote: > > Hi list, > > This is a question for mySQL experts, which I am not. > > I have a table of individuals. > I have another table of companies. > > Many individuals have worked in more than 1 company with > different jobs descriptions. > > I need to build my DB so that, with single requests, I > can get a list of either : > - all individuals who worked in a specific company with > the job description of each one > - all companies in which a specific individual has worked, > along with the job description he had in each company. > > The main problem is that the tables might become huge with > the time (several Gb), so searching the tables might take > forever if not carefully built. > > I guess I will need to use a third table for the job descriptions > and a combination of fulltext indexes and foreign keys > might do the trick, but my limited skills don't allow me > to find the best solution... > > Any help will be much appreciated. > Thank you in advance. > > jbv > > ___ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
[semi OT] mySQL question
Hi list, This is a question for mySQL experts, which I am not. I have a table of individuals. I have another table of companies. Many individuals have worked in more than 1 company with different jobs descriptions. I need to build my DB so that, with single requests, I can get a list of either : - all individuals who worked in a specific company with the job description of each one - all companies in which a specific individual has worked, along with the job description he had in each company. The main problem is that the tables might become huge with the time (several Gb), so searching the tables might take forever if not carefully built. I guess I will need to use a third table for the job descriptions and a combination of fulltext indexes and foreign keys might do the trick, but my limited skills don't allow me to find the best solution... Any help will be much appreciated. Thank you in advance. jbv ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
