Breaking 1NF?

Frunkie · #1 (**permalink**) 10-31-07, 08:34

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

Pat Phelan · #2 (**permalink**) 10-31-07, 08:53

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

Frunkie · #3 (**permalink**) 10-31-07, 09:03

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

Frunkie · #4 (**permalink**) 10-31-07, 09:22

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?

Pat Phelan · #5 (**permalink**) 10-31-07, 09:49

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

Frunkie · #6 (**permalink**) 10-31-07, 10:02

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.

r937 · #7 (**permalink**) 10-31-07, 10:04

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

Frunkie · #8 (**permalink**) 10-31-07, 10:30

I saw that too, after my last post and created a composite PK already.

I hate dupes.

Thanks Rudy.

Internet Services

Web Development

Digital Marketing

Consumer Tech