[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
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
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
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