foreign key doubt - mysql

[Felito]

hi

one simple question please. I have three tables.

 
**GROUP**

    id    area
    1 	Multimedia
    4 	Education
        
**Employment**
      

      id   foreign key(GROUP)     job
       1 	1 	                designer
       2 	4 	                professor
       3 	1 	                copy
        

and now suppose that i have this

**SpecificProperties**

    id  foreign key (Employment)      properties
    1   1                             type of contract
    2   1                             number of jobs 
    3   2                             duration contract (no type of contract)
    4   2                             number of jobs

the designer and the professor in this case have the property number of jobs. So basically the question is, i need repeat the number of jobs for each profession? there is any way to avoid? or i can have to number of jobs 8 foreign keys to 8 different jobs for example ? one for designer, other for professor, other for copy, and so on? in this way the first number of jobs have in the foreign key 1 || 2 || 3

something like this:

  **SpecificProperties**
    
        id  foreign key (Employment)      properties
        1   1                             type of contract
        2   1||2||3||4                    number of jobs 
        3   2                             duration contract (no type of contract)

thanks

[oddz]

Looks like a standard m:n relationship:

Properties
- id
- property

EmploymentsProperties
- id
- employment_id (employment fk)
- property_id (property fk)

[r937]

EmploymentsProperties
- id
- employment_id (employment fk)
- property_id (property fk)

please, don't do that

a many-to-many table does ~not~ need its own id column

[Felito]

point of situation. i think that a many-to-many possible is not the best solution.

Please help me to reformulate the hierarchy of the database.

I have:
user (id, name, age, salary, profession)
professional area (id, area, number of members)
job (id, job)
statistics (average salaries, average age, and other stuff )

i am totally confused with that

What i pretend is just a simple interface like this:

[oddz]

i think that a many-to-many possible is not the best solution.

Why not, the data model represents a m:n relationship.

please, don't do that

a many-to-many table does ~not~ need its own id column

It may not now but who knows in the future – it may be needed as foreign key in future feature enhancements.

[Felito]

i made a mistake in my first post. i Simple cant have a list of items, i need to store info in the items, so it represents a column and not a row.

i don't have the hierarchy well done, it is impossible to make any progress, without solve the design first

[r937]

It may not now but who knows in the future – it may be needed as foreign key in future feature enhancements.

nice attempted backpedal

[oddz]

i made a mistake in my first post. i Simple cant have a list of items, i need to store info in the items, so it represents a column and not a row.

So you are saying that the property "number of jobs" would have different data associated with it based on job? Is the "different data" for each property/job relation known (static) or will a user be able to add info for a property/job relation through an interface?

nice attempted backpedal

I thought so

[Felito]

i will try to explain what i pretend. The user when register insert your name, profession, salary, age and another stuff.

With this data i need to generate some reports, separated by profession in a dropdown menu.

so, arts >>> designer / painter / webdesigner >> average salary, avarage age, and so on.

it is more clear? thanks