SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation foreign key doubt - mysql

    hi

    one simple question please. I have three tables.
    Code:
     
    **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:

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

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Looks like a standard m:n relationship:

    Properties
    - id
    - property

    EmploymentsProperties
    - id
    - employment_id (employment fk)
    - property_id (property fk)
    The only code I hate more than my own is everyone else's.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oddz View Post
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:


  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Felito
    i think that a many-to-many possible is not the best solution.
    Why not, the data model represents a m:n relationship.

    Quote Originally Posted by r937
    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.
    The only code I hate more than my own is everyone else's.

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by oddz View Post
    It may not now but who knows in the future it may be needed as foreign key in future feature enhancements.
    nice attempted backpedal
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by 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.
    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?

    Quote Originally Posted by r937
    nice attempted backpedal
    I thought so
    The only code I hate more than my own is everyone else's.

  9. #9
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •