Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2008
    Posts
    17

    Unanswered: HELP : primary/foreign key

    I have the following table structure:

    Scenario 1 is the current situation. I have 3 tables : family, tools and permission.
    tbl_family contains roles (father, mother, son)
    tbl_home contains tools (tv, laptop) and privileges (use, reinstall etc)
    tbl_permission defines permission (fater, tv, use) etc

    I wanted to redesign the tables and use ID as it should. In scenario 2 I started, tbl_family is done (by adding field family_id) but how do I define in tbl_home the primary key??

    Would tools_id, tools, privilege be primary key all three? and the same for tbl_permission!

    hleeeeeeeeeep
    Attached Thumbnails Attached Thumbnails scenario_1.jpg   scenario_2.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you should perhaps start over

    each table should be about something

    so a "home" table should be about the home, not about the stuff like a tv that's in the home

    perhaps the tv and laptop might be instances of a "tool" as you say, in which case there should be a tools table

    what is the difference between privilege="use" and permission="use"

    this is a homework assignment, isn't it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    17
    No it is just for experience. I want to add ID (int) to each table, because I wanted to use key/value for dropdownlist.

    tbl_home is having tools such as tv which might have 1 or more privileges (watch, adjust etc). In tbl_permission is the family - home relation is defined including possible privileges (permission) for each tool.

    is it more clear now?? So my problem is that in tbl_permission I can't have 3 primary keys (ID, FAMILY, TOOL)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by internationalist
    I want to add ID (int) to each table, because I wanted to use key/value for dropdownlist.
    so you prefer this --

    <select name="mycodes">
    <option value="1">one</option>
    <option value="2">two</option>
    <option value="3">buckle</option>
    <option value="7">my shoe</option>
    </select>

    to this --

    <select name="mycodes">
    <option value="one">one</option>
    <option value="two">two</option>
    <option value="buckle">buckle</option>
    <option value="my shoe">my shoe</option>
    </select>

    if so, the difference is only a few bytes in the packet that is sent from the web page to your application, hardly a sufficient reason to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2008
    Posts
    17
    I know you are right about that, but I just wanted to know how to solve this issue. The current situation is as follow:
    http://www.dbforums.com/attachment.p...7&d=1200313234

    and I want to give each table an ID, and solve the issue in permission table, but how??

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem in permission table is that you need the full PK from home table, i.e. both columns, as the FK

    it is indeed that simple

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2008
    Posts
    17
    Could you provide a simple database design, please?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Home (tools, privilege)
    Family (position)
    Permission (tools, privilege, position)

    legend: PK, FK
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2008
    Posts
    17
    hmm am I not clear?? once again look at current situation http://www.dbforums.com/attachment.p...7&d=1200313234
    It is exactly as you have recommended. What I want is something like:

    Family (family_Id, position)
    Home (home_Id, tools, privilege)
    Permission (permission_Id, home_Id?, family_Id)

    It is tbl_Home and tbl_Permission that I am not sure how to solve!
    Last edited by internationalist; 01-14-08 at 18:11.

  10. #10
    Join Date
    Oct 2007
    Posts
    3
    would this work?

    family (family_Id*, home_id*, position)
    home (home_Id*)
    tools (home_id*, tools_id*, tool_name)
    toolsactions (home_id*, tools_id*, action_id*, action_name)
    familypermission (family_id*, home_Id*, tools_id*, action_id*)

    '*' is primary keys

    this query

    Code:
    SELECT
        family.position,
        IF(familypermission.action_id IS NULL, 'NOT ALLOWED', 'ALLOWED') AS permission
        CONCAT(toolsactions.action_name, ' ', tools.tool_name) AS action
    FROM
        family
        INNER JOIN
        home
            ON
            home.home_id = family.home_id
        INNER JOIN
        tools
            ON
            tools.home_id = home.home_id
        INNER JOIN
        toolsactions
            ON
            toolsactions.home_id = home.home_id AND
            toolsactions.tools_id = tools.tools_id
        LEFT JOIN
            familypermission
            ON
            familypermission.family_id = family.family_id AND
            familypermission.home_id = home.home_Id AND
            familypermission.tools_id = tools.tools_id AND
            familypermission.action_id = toolsactions.action_id
    on

    Code:
    FAMILY TABLE
    
    family_id    home_id    position
    -----------------------------
    1            1          FATHER
    2            1          MOTHER
    3            1          CHILDREN
    
    HOME TABLE
    
    home_id
    ---------
    1
    
    TOOLS TABLE
    
    home_id    tools_id    position
    -----------------------------
    1          1           TV
    
    TOOLSACTIONS TABLE
    
    home_id    tools_id    action_id    action_name
    ----------------------------------------
    1          1           1             FIX
    1          1           2             WATCH
    1          1           3             ABUSE
    
    FAMILYPERMISSION TABLE
    
    family_id    home_id    tools_id    action_id
    ----------------------------------------
    1            1          1           1
    2            1          1           2
    3            1          1           3
    would result something like

    Code:
    position   permission    action
    --------------------------------
    FATHER     ALLOWED       FIX TV
    FATHER     NOT ALLOWED   WATCH TV
    FATHER     NOT ALLOWED   ABUSE TV
    MOTHER     NOT ALLOWED   WATCH TV
    MOTHER     ALLOWED       WATCH TV
    MOTHER     ALLOWED       ABUSE TV
    CHILDREN   NOT ALLOWED   FIX TV
    CHILDREN   NOT ALLOWED   WATCH TV
    CHILDREN   ALLOWED       ABUSE TV

    or a code like

    Code:
    SELECT
        CONCAT(family.position, ' IS ', IF(familypermission.action_id IS NULL, 'NOT ALLOWED', 'ALLOWED'), ' TO ', CONCAT(toolsactions.action_name, ' ', tools.tool_name)) AS theresult
    FROM
        family
        INNER JOIN
        home
            ON
            home.home_id = family.home_id
        INNER JOIN
        tools
            ON
            tools.home_id = home.home_id
        INNER JOIN
        toolsactions
            ON
            toolsactions.home_id = home.home_id AND
            toolsactions.tools_id = tools.tools_id
        LEFT JOIN
            familypermission
            ON
            familypermission.family_id = family.family_id AND
            familypermission.home_id = home.home_Id AND
            familypermission.tools_id = tools.tools_id AND
            familypermission.action_id = toolsactions.action_id
    on

    Code:
    FAMILY TABLE
    
    family_id    home_id    position
    -----------------------------
    1            1          FATHER
    2            1          MOTHER
    3            1          CHILDREN
    
    HOME TABLE
    
    home_id
    ---------
    1
    
    TOOLS TABLE
    
    home_id    tools_id    position
    -----------------------------
    1          1           TV
    
    TOOLSACTIONS TABLE
    
    home_id    tools_id    action_id    action_name
    ----------------------------------------
    1          1           1             FIX
    1          1           2             WATCH
    1          1           3             ABUSE
    
    FAMILYPERMISSION TABLE
    
    family_id    home_id    tools_id    action_id
    ----------------------------------------
    1            1          1           1
    2            1          1           2
    would result something like

    Code:
    theresult
    --------------------
    FATHER IS ALLOWED TO FIX TV
    FATHER IS NOT ALLOWED TO WATCH TV
    FATHER IS NOT ALLOWED TO ABUSE TV
    MOTHER IS NOT ALLOWED TO FIX
    MOTHER IS ALLOWED TO WATCH TV
    MOTHER IS NOT ALLOWED TO ABUSE TV
    CHILDREN IS NOT ALLOWED TO FIX TV
    CHILDREN IS NOT ALLOWED TO WATCH TV
    CHILDREN IS NOT ALLOWED TO ABUSE TV
    Last edited by dhanuxz; 01-16-08 at 07:08.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dhanuxz
    would this work?
    Code:
    ...
        INNER JOIN
        home
            ON
            home.home_id = home.home_id
    not that part, no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2007
    Posts
    3
    aah, here' s the bug fix...

Posting Permissions

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