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