help with database modeling

[felito]

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;

 

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

I think this cannot be considered a subcategory of group or job.

 

Any idea?

[malinens]

table job_properties with prop_id, ref, prop_name, prop_value...

[felito]

thanks. can you be more specific and complete in your answer please?