[semi OT] mySQL question

[jbv at souslelogo.com]

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: kray at sonsothunder.com
> Website: https://www.sonsothunder.com
> 
>> On May 11, 2023, at 9:55 AM, jbv via use-livecode
>> <use-livecode at 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 at 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