Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Can someone tell me what the technique is called when you store fields and values split in a database delimited by a comma for example. Ive seen it being used but wasnt sure what it was called.

id | options
1 | option1=this, option2=that, option3=other
2 | option1=this, option2=that, option3=other
3 | option1=this, option2=that
share|improve this question
7  
How about "a really bad idea"? –  Paul Tomblin Aug 13 '09 at 0:47
1  
@womble: this doesn't necessarily belong on SO. it's part of a sysadmin's job to stop programmers doing dumb things (as they are so often wont to do). and that includes educating them about why the thing they are proposing to do is, in fact, a bad idea. –  cas Aug 13 '09 at 1:14
    
It's also a part of a sysadmin's job to go to the bathroom now and then, but I'm not about to start asking questions here about the best wiping techniques. –  womble Aug 13 '09 at 2:36

10 Answers 10

up vote 8 down vote accepted

it's called a comma-separated list of attribute-value pairs, and is a perfectly valid thing to do...
...in rare and exceptional circumstances!

share|improve this answer

I'm with Paul; "Stupid" is the first word that comes to my mind.

share|improve this answer
1  
"dumb" was the first word i thought of, but "stupid" works for me. the second thing i thought of was "dumb, but depressingly common". –  cas Aug 13 '09 at 1:00
    
For instance, you see it on The Daily WTF about once a week. –  Paul Tomblin Aug 13 '09 at 1:13
    
ok, guys, we have a log table with a column containing a number of different values. Does it have a name? Yes or No. I will try stack overflow otherwise. –  madphp Aug 13 '09 at 2:27
    
Uuh, this is not helpful at all. CSV is a perfectly valid way of transmitting small amounts of string values for once-off importing/exporting. Excel can read them out of the box (and has been able to since forever), as can Access, SSMS, and they're human readable in notepad to boot. –  Mark Henderson Aug 14 '09 at 1:43
    
Ack, sorry, I just saw that it's not CSV at all. You're right, this is stupid. –  Mark Henderson Aug 14 '09 at 1:46

BTW, the reason why this is "dumb" or "stupid" or "a really bad idea" is that this is better done as a separate table with a foreign key field pointing back to the id field in the first table, a "key" field, and a "value" field. this allows key/value pairs to be individually indexed, searched, and manipulated (add, delete, update).

and even then, this is only useful in some circumstances - where there are a variable number of unknown/arbitrary keys that a given record might have associated with itself. if the name and number of keys is known in advance, there are better ways to handle it.

wikipedia has several good introductory articles on databases and database normalisation that are worth reading. start with Database Models and Database Normalisation

share|improve this answer
    
+1 It's easy enough to say it's dumb but far more valuable to explain why. Without a good "why" the dumb things just get repeated. –  John Gardeniers Aug 14 '09 at 3:06

According to a couple of books on my shelf, the term you're looking for is:

Multipart Field

While there are exceptions to every rule, multipart fields are rarely needed.

Multipart fields can wreak havoc with data integrity and should be avoided. Problems most often come up when you try to edit, delete, or sort the date in a multipart field.

When you "normalize" your database and ensure that each field stores only a single value you are doing yourself (and others) a huge favor down the road by helping to ensure that you have data integrity and accurate information.

share|improve this answer

I call it a violation of the first normal form.

share|improve this answer

I agree, it's stupid
          IF
this is your application's data model.

This is actually quite beneficial if you are simply persisting something that was parsed elsewhere, such as user options in a client app. Makes the client to the work of parsing. This is a trivial storage mechanism that can easily be split and stored in a collection client-side.

share|improve this answer

Loosely, it's a "repeating value" and violates (at least) First Normal Form (sort of): http://en.wikipedia.org/wiki/Database_normalization

share|improve this answer

Comma seperated values is close.

I'm not sure why you would repeat field names for every value though.

share|improve this answer

You can use array types in PostgreSQL to achieve something similar to what you're after, but it's still a Really Bad Idea (tm).

share|improve this answer

This is how I would prefer to do it if you will be doing anything with this data besides simply persisting it.

id | opt | content
1  | 1   | this
1  | 2   | that
1  | 3   | other
2  | 1   | this
2  | 2   | that
2  | 3   | other
3  | 1   | this
3  | 2   | that
3  | 3   | other
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.