Hacker News new | ask | show | jobs
by fnl 5043 days ago
> And no, using arrays is not an automatic violation of first normal form.

One of the conditions of the 1NF is that each row and column contain one and only one value. While a NULL might be disputable, using multiple values as shown in the two examples (tags, items/price/quantity) are a clear violation of the 1NF. Only if the array data were truly "one value" (e.g., a vector) it would not be a violation. (I.e., I am not trying to contradict your statement, but I think it helps to add a bit of clarification.)
2 comments
ocharles 5043 days ago
If you never need to query 'inside' the array, then I don't think that's a violation of 1NF at all. If you treat the entire array as an atomic value, then I would think that's still 1NF.
link
guan 5043 days ago
Even if you do need to query inside the array, how is this different from something like WHERE x like 'prefix%'? Is that a violation of 1NF too?
link
ocharles 5043 days ago
Arguably, yes, because you haven't really broken your data down as much as you need to; but this is a fairly contentious issue - just see http://en.wikipedia.org/wiki/First_normal_form#Atomicity. If you are frequently needing to query inside data, you may well gain from moving that 'prefix' into a separate column, or more drastically rethinking your data model if that's appropriate.
link
guan 5042 days ago
In this example prefix is something the user typed in. So textcolumn LIKE '%blahblah%'.
link
jeffdavis 5043 days ago
"One of the conditions of the 1NF is that each row and column contain one and only one value."

There is no useful differentiation between atomic and non-atomic values. A timestamp is made up of a date and a time, a string is an array of characters, a real number has a sign and magnitude, etc.

I suppose you could just use boolean only, which could be regarded as "atomic".

link