SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question is partial foreign key a violence of normal form

    I want to add tags to a discussion. Some considerations
    • tag - discussion is M:N
    • a tag is identified by it's textual name AND the project in which it is used. So compound PK (name, project_id)
    • discussions belongs to one given project. so 1:N
    The problem is that tag_discussion has a project_id as part of the foreign key to table tag. But a discussion belongs already to a project, so this feels like a redundancy. Does this break a normal form?



    I can avoid this situation by using only part of the PK of tag, namely name. So the foreign key to tag is partial then. At least In oracle this is problematic, if understand this source correctly.


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by plaatspunt View Post
    Does this break a normal form?
    no

    although i am curious why you want the PK of tag to include the project

    special tags for each project? what's wrong with one set of tags for all projects?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    your second layout is the way to go.

    why do you have project_id in the tag table? are you trying to limit the tags available to a project?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    gah, sniped again!
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    although i am curious why you want the PK of tag to include the project
    why do you have project_id in the tag table? are you trying to limit the tags available to a project?
    No, no limit. I need to quickly see all tags that are in use in a given project. If I don't couple tagname and project, then
    • either I need to do a really complex search on all tables that can have a tag,
    • or use a trigger that listens to tag CUD-operations and logs them in a table (project_id, tagname)
    Also I don't want users to see tags from other projects, as they might totally be inapopriate. So I am forced to relate tagname and poject_id explicitly, don't I?

    Before 'refactoring' I had

    tag
    ==================
    PK: name (VARCHAR)

    tag_project
    ==================
    PK tagname (VARCHAR) // FK tag.name
    project_id (INT) // FK project.project_id

    Are my foreign key constraints now broken in mysql by using image 2?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by plaatspunt View Post
    Are my foreign key constraints now broken in mysql by using image 2?
    prolly

    why not create a test database and run your CREATE statements to see?

    make sure they are InnoDB tables, and mysql will tell you if the FKs are right or not

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •