help with database modeling

Question

I have two tables: one for areas (like science, sport, education), and another for professions (like scientist, designer, golf player). There is a foreign relationship between the two tables, which works without any problems at the moment.

But now I need another table to put "number of workers", "average age", "years in the company" (this list is possibly different for each profession). What is the best way to do this? Create another table? What would be the parent? Basically, it is a third statement.

CREATE TABLE group ( id smallint(5) unsigned NOT NULL auto_increment, area varchar(30), PRIMARY KEY (id) ) CREATE TABLE job ( ref int(10) unsigned NOT NULL auto_increment, jobid smallint(5) unsigned NOT NULL, job varchar(50), PRIMARY KEY (ref) ) ALTER TABLE job ADD CONSTRAINT FK_job FOREIGN KEY (jobid) REFERENCES group(id) ON UPDATE CASCADE ON DELETE CASCADE;

Can you please expand on the purpose of your system data? Also, please post the create table of your existing tables. Thanks! — Kevin Peno
– Kevin Peno, Commented Apr 22, 2011 at 15:52
i want to make a drop-down select by category. At the moment only lack the third category — anvd
– anvd, Commented Apr 22, 2011 at 15:56

Tyler Ferraro · Accepted Answer · 2011-04-22 16:14:01Z

1

From what I understand I would set up a third table as follows

Table: Employee First_Name varchar(30) Last Name varchar(30) Age (int(3)) Employment Date (DATE) Active (Yes/No) JobFK (Points to emprego.PK)

With this kind of setup you can use joins on your tables to calculate how many workers are in the same profession. The average age of those employees, and how long they have been with the company. Given more information about your current tables I could even describe the sql queries for that information.

edited Apr 22, 2011 at 16:14

answered Apr 22, 2011 at 15:53

Tyler Ferraro

3,7821 gold badge23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

anvd Over a year ago

first area > job > employee. I am not sure about the constrain. It can exists professions with particularities.

Tyler Ferraro Over a year ago

You need to be more specific with your code then. English also helps. The above table should work if you constrain the right foreign key to the appropriate primary key on another table. As I see it. An employee has a Job. A Job exists in a Profession. Employee.JobFK -> Emprego.PK Emprego.FK -> Agrupamento.PK

anvd Over a year ago

i edited my code to English. The point of the question is: for example. A golf player doesn't have a contract with a company (suppose), and a doctor have.

Collectives™ on Stack Overflow

help with database modeling

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related