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 = 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 > <use-livecode@lists.runrev.com> 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