Results 1 to 8 of 8

Thread: Breaking 1NF?

  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Breaking 1NF?

    Hi everyone,

    if I have a table with a field where I wanted to allow multiple values to describe the item in the table, would this be breaking 1NF?

    What I want to do is search on this column. In an example, if I entered an item say a year or two ago and I know that I have entered it into the db but I cannot remember the name by which to retrieve the data, I was thinking to use common words by which I would be able to search.

    There is no identity amongst all of the columns from which to make a PK. The best identity I can get would be from the names or values I enter into this column. I think that what I am looking for is kind of like "tags" on that delicio.us site. Same principal.


    Thanks,

    Frank

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Yes, that definitely breaks 1NF. As such, I'd be agin it (that would be hill-speak, doncha know).

    So if your present design looks something like:
    Code:
    --  ptp  20071031  Bad example, breaks 1NF
    
    CREATE TABLE widgets (
       widgetID		INT
       CONSTRAINT XPKwidgets
          PRIMARY KEY (widgetID)
    ,  foo			DATETIME
    ,  bar			VARCHAR(50) DEFAULT 'A place to find Brett'
    ,  commaSeparatedTags	VARCHAR(255)
       )
    I would recommend using something like:
    Code:
    --  ptp  20071031  Better example, meets 1NF
    
    CREATE TABLE widgets (
       widgetID		INT
       CONSTRAINT XPKwidgets
          PRIMARY KEY (widgetID)
    ,  foo			DATETIME
    ,  bar			VARCHAR(50) DEFAULT 'A place to find Brett'
       )
    
    CREATE TABLE widgetTags (
       widgetID		INT
       FOREIGN KEY (widgetID)
          REFERENCES widgets (widgetID)
    ,  tag			VARCHAR(50)
       )
    -PatP

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks for the reply Pat.

    I have not yet modeled anything. I have been in a situation many times where I was searching my db, knowing full well that what I need is there but couldn't find it because I don't remember the name I used. I figured I could get past this by using tags.

    Thanks for the example Pat..

    Frank

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I forgot to ask.. I noticed that you did not use a PK in the widgetTags table. Would you see a problem in using an auto-int for a PK in that table?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    You certainly could use a surrogate key for the widgetTags table, but do you really need one? This is a case where the table exists solely for the purpose of normalizing the tags... One tag by itself is of little or no value, there is no sequence to the tags, and I'm assuming that all operations on widgetTags would be "all or nothing" (in other words, you wouldn't modify just one tag by itself).

    There is nothing wrong with adding the surrogate key, and it could prove helpful in certain conditions, but I can't see a compelling reason to add it from the way that I envision widgetTags being used.

    -PatP

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by Pat Phelan
    You certainly could use a surrogate key for the widgetTags table, but do you really need one? This is a case where the table exists solely for the purpose of normalizing the tags... One tag by itself is of little or no value, there is no sequence to the tags, and I'm assuming that all operations on widgetTags would be "all or nothing" (in other words, you wouldn't modify just one tag by itself).

    There is nothing wrong with adding the surrogate key, and it could prove helpful in certain conditions, but I can't see a compelling reason to add it from the way that I envision widgetTags being used.

    -PatP
    No, I suppose I really don't need one. The only reason I tend to give all of my tables PKs is to conform to a normal form standard but your right.. the table exists only for normalizing the tags and nothing more. Well, I just learned something new.

    Thanks again Pat.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ALTER TABLE widgetTags
    ADD CONSTRAINT widgetTags_PK PRIMARY KEY (widgetID,tag)

    stops you from entering the same tag for the same widget more than once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I saw that too, after my last post and created a composite PK already. I hate dupes.

    Thanks Rudy.

Posting Permissions

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