Should I break 1NF?

I am storing graphical objects in SQL Server.

A Line, Rectangle, Ellipse, Image and Text object can all be represented spatially on the drawing surface by 2 elements StartPoint and EndPoint    (Point= X INT > 0,Y INT > 0). Without type support , it decomposes to the columns X,Y,Xi,Yi.

I had a lot of fun with the the front end  (C# windows forms) class structures (nice little factory pattern) and decided to add a free hand drawing object. 

The front end coding was fairly trivial.  Every 20ms, get the mouse coordinate (Point: X,Y). After this object is “finished”, I am left with an array of Points.  On average about 100 points per free hand object are generated (2 seconds of mouse down). 

So....... Should I tell 1NF to go jump and store a long string? “12,45:23,56:“ or be a good DBA and store a point per row?

This object is not business critical, so I might not have to store the object but I am preparing for the worst.

Print | posted on Sunday, February 06, 2005 6:53 AM


# re: Should I break 1NF?

left by Graz at 2/6/2005 9:53 AM Gravatar
Is there any benefit to storing them individually? Will you ever query individual points? Do the individual points have any value outside of the other points? I'd argue that storing individual points for an object would be akin to storing individual bits for an integer. That each point only has context inside the larger object unlike an address where the zip code has value in and of itself. Plus it'd be really easy to slap them in a varchar and let the client pull the string apart :)

# re: Should I break 1NF?

left by DavidM at 2/6/2005 10:13 AM Gravatar
No, No, and No.

No matter what design I go for, the client's interface with the DB will be a CSV of points for this object. May as well leave it in the DB that as well... Unless anybody else can argue differently

Thanks Graz.

# re: Should I break 1NF?

left by Jay (aka ehorn) at 2/7/2005 12:13 AM Gravatar
>>CSV of points for this object. May as well leave it in the DB that as well

I like the idea of storing a point per row here.

Have you considered using xml as a messaging layer?

Maybe collect the points in an arraylist(points struct) or something like that.

build an xml chunk, message it to the db, shred it into 1 point per row - retrieval and rendering you have a much simpler model for regening the graphic - you end up with a simple collection of the coordinates into an arraylist of points and retrieve them back into your custom collection when regening.

I should note that I have become partial to xml over csv's though for persisting collections of data to the db :)

# re: Should I break 1NF?

left by Jay (aka ehorn) at 2/7/2005 12:40 AM Gravatar
I hit the submit button to soon :)

Question to you, What are the disadvantages of normalizing the points ?

I think ultimately, it comes down to how someone decides to manage the persistence, retrieval and coding of collections of data overall.

My preferences are: (where they make sense, and I think this is one):

- normalized collection structures
- avoidance of csvs (xml as messaging framework)

But, it typically boils down to how and where to message and decompose and persist collections.

my 2 pennies..

# re: Should I break 1NF?

left by mkbosmans at 2/8/2005 1:22 AM Gravatar
So, how does this problem relate to 1NF?

# Is that really the question?

left by Brett at 2/8/2005 3:25 AM Gravatar
I don't think (and that's happening with more and more frequency) that you are breaking the rules.

You have an Object key, the form of the object, which are the points. If you had another "property" say color, I'd put that in a separate column. If you added that to your string, then I'd say that's a 15 yard penalty.

And can you beleive that penalties didn't bother the pats?

PENALTIES No. and Yards
PATS 7-47

I guess 3 INTs are hard to overcome

Comments have been closed on this topic.