SitePoint Sponsor

User Tag List

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

    there is any problem with this design?

    Hi.

    I have this ERR (it is bigger but for the problem is irrelevant).
    The users can be company's or single users (persons).
    In the job_offers i have two foreign keys, so i used them as PK.



    It has any problem with this model? the relation between users and company it is identified? at the moment they are represented as identified.
    thanks

  2. #2
    SitePoint Evangelist speda1's Avatar
    Join Date
    Jan 2002
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So a user has many companies and a company has many job offers. Is the join between user and companies necessary or can a single user access all companies?

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Right now you've encoded it such that a user can have multiple companies, so the relation user:company is 1:N. I'd suggest you make that relation N:M so a user can have multiple companies, and a company can have multiple users, as that's more realistic.
    To do this you need an extra table with the PK of users and the PK of job_offers as its PK.

    If you don't want to do that: yes, your scheme is incorrect. The fact that users_id_user is a PK in the company table is in the PK doesn't make any sense, and that your PK in the job_offers table makes even less sense (did you notice that a company can have no more than 1 offer the way you've set it up!?).

    I'd say that users_id_user in the company table should be in the primary key, but in the foreign key, and then put company_id_company in as an FK in the job_offers table and create and id_job_offer as the PK in that table.

    PS. Please make up your mind about using singular or plural. Right now you have a table "users" (plural) with a field "id_user" (singular), a table "company" (singular), and "job_offers" (plural).
    If you keep mixing it like this it come will come back to bite you in the behind one day.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i will try to explain what i intend. can exists two types of users. Company users and single users (persons).

    The single users can be students or workers. Company users can have multiple job_offers but each job_offer only belongs to a single company. The relation is one to one between users and company because the company user "siemens" only can have a year of foundation or a description of activity.

    So, the table users have a group of fields that can be shared between two types of users- two single users (students and workers) and company users.

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    If the relation between users and company is 1:1, company should have a field users_id_user that is the PK of that table, and is an FK to users.id_user

    The way you've set it up now is not really 1:1, although you did try by putting the users_id_user in the company table, but that only makes things awkward ...
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    If the relation between users and company is 1:1, company should have a field users_id_user that is the PK of that table, and is an FK to users.id_user...
    i have what you suggest, users_id_user is PK of company and foreign key of users table.

    What i tried is a generalization basically. The table users have fields shared between single user and company user, single user have fields shared between workers and students, and finally workers and particular fields and students too.

    do you think that is a bad practice my logic?

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Felito View Post
    i have what you suggest, users_id_user is PK of company and foreign key of users table.
    Okay, I meant users_id_user should be the only PK of the company table. You don't need the company_id and it only makes things confusing.

    The rest of your logic for sharing fields is sound, yes
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for your help


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
  •