[semi OT] mySQL question

[Ken Ray]

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