Mixing
Is storing a delimited list in a database column really that bad?
Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table.
Now, I know that the correct solution would be to create a second table and properly normalize the database. It was quicker to implement the easy solution, and I wanted to have a proof-of-concept of that application quickly and without having to spend too much time on it.
I thought the saved time and simpler code was worth it in my situation, is this a defensible design choice, or should I have normalized it from the start?
Some more context, this is a small internal application that essentially replaces an Excel file that was stored on a shared folder. I'm also asking because I'm thinking about cleaning up the program and make it more maintainable. There are some things in there I'm not entirely happy with, one of them is the topic of this question.
database database-design database-normalization
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
add a comment |
Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table.
Now, I know that the correct solution would be to create a second table and properly normalize the database. It was quicker to implement the easy solution, and I wanted to have a proof-of-concept of that application quickly and without having to spend too much time on it.
I thought the saved time and simpler code was worth it in my situation, is this a defensible design choice, or should I have normalized it from the start?
Some more context, this is a small internal application that essentially replaces an Excel file that was stored on a shared folder. I'm also asking because I'm thinking about cleaning up the program and make it more maintainable. There are some things in there I'm not entirely happy with, one of them is the topic of this question.
database database-design database-normalization
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
17
in that case, why bothering database?, saving in a file will do.
– thavan
Feb 12 '13 at 9:31
4
Agreed with @thavan. Why even save the data for a proof of concept? Once you have the proof complete, then add a database correctly. Your fine doing lightweight for proof of concept, just don't make things you have to unmake later.
– Jeff Davis
Aug 9 '13 at 14:10
add a comment |
Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table.
Now, I know that the correct solution would be to create a second table and properly normalize the database. It was quicker to implement the easy solution, and I wanted to have a proof-of-concept of that application quickly and without having to spend too much time on it.
I thought the saved time and simpler code was worth it in my situation, is this a defensible design choice, or should I have normalized it from the start?
Some more context, this is a small internal application that essentially replaces an Excel file that was stored on a shared folder. I'm also asking because I'm thinking about cleaning up the program and make it more maintainable. There are some things in there I'm not entirely happy with, one of them is the topic of this question.
database database-design database-normalization
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
Imagine a web form with a set of check boxes (any or all of them can be selected). I chose to save them in a comma separated list of values stored in one column of the database table.
Now, I know that the correct solution would be to create a second table and properly normalize the database. It was quicker to implement the easy solution, and I wanted to have a proof-of-concept of that application quickly and without having to spend too much time on it.
I thought the saved time and simpler code was worth it in my situation, is this a defensible design choice, or should I have normalized it from the start?
Some more context, this is a small internal application that essentially replaces an Excel file that was stored on a shared folder. I'm also asking because I'm thinking about cleaning up the program and make it more maintainable. There are some things in there I'm not entirely happy with, one of them is the topic of this question.
database database-design database-normalization
database database-design database-normalization
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
edited Mar 22 '17 at 11:02
Mad Scientist
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
asked Sep 6 '10 at 18:11
Mad ScientistMad Scientist
11.2k96286
11.2k96286
17
in that case, why bothering database?, saving in a file will do.
– thavan
Feb 12 '13 at 9:31
4
Agreed with @thavan. Why even save the data for a proof of concept? Once you have the proof complete, then add a database correctly. Your fine doing lightweight for proof of concept, just don't make things you have to unmake later.
– Jeff Davis
Aug 9 '13 at 14:10
add a comment |
17
in that case, why bothering database?, saving in a file will do.
– thavan
Feb 12 '13 at 9:31
4
Agreed with @thavan. Why even save the data for a proof of concept? Once you have the proof complete, then add a database correctly. Your fine doing lightweight for proof of concept, just don't make things you have to unmake later.
– Jeff Davis
Aug 9 '13 at 14:10
17
17
in that case, why bothering database?, saving in a file will do.
– thavan
Feb 12 '13 at 9:31
in that case, why bothering database?, saving in a file will do.
– thavan
Feb 12 '13 at 9:31
4
4
Agreed with @thavan. Why even save the data for a proof of concept? Once you have the proof complete, then add a database correctly. Your fine doing lightweight for proof of concept, just don't make things you have to unmake later.
– Jeff Davis
Aug 9 '13 at 14:10
Agreed with @thavan. Why even save the data for a proof of concept? Once you have the proof complete, then add a database correctly. Your fine doing lightweight for proof of concept, just don't make things you have to unmake later.
– Jeff Davis
Aug 9 '13 at 14:10
add a comment |
10 Answers
10
active
oldest
votes
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems:
- Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
- Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
- Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
- Can’t delete a value from the list without fetching the whole list.
- Can't store a list longer than what fits in the string column.
- Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'*
- Hard to count elements in the list, or do other aggregate queries.
- Hard to join the values to the lookup table they reference.
- Hard to fetch the list in sorted order.
- Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.
To solve these problems, you have to write tons of application code, reinventing functionality that the RDBMS already provides much more efficiently.
Comma-separated lists are wrong enough that I made this the first chapter in my book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
There are times when you need to employ denormalization, but as @OMG Ponies mentions, these are exception cases. Any non-relational “optimization” benefits one type of query at the expense of other uses of the data, so be sure you know which of your queries need to be treated so specially that they deserve denormalization.
* MySQL 8.0 no longer supports this word-boundary expression syntax.
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
6
Thanks for the detailed list, I thought of some of those problems but certainly not of all of them.
– Mad Scientist
Sep 6 '10 at 18:35
5
Book looks interesting so I bought it. I've long thought it was subject matter that needed a book.
– HLGEM
Sep 7 '10 at 14:01
7
An ARRAY (of any datatype) can fix the exception, just check PostgreSQL: postgresql.org/docs/current/static/arrays.html (@Bill: Great book, a must read for any developer or dba)
– Frank Heikens
Nov 24 '11 at 21:18
4
+1 bill Karwin Great answer! Lovely concise bullet points. That looks like a great book too. Love the cover too +1 NullUserException. I'm in the process of designing the schema for a MySQL database to replace a flat file text based system. I've encountered several dilemmas so far. So this book will be worth buying.
– therobyouknow
Jan 30 '12 at 16:16
8
Shouldn't banana come before 3 rather than after?
– Jay
Feb 19 '15 at 21:31
|
show 13 more comments
There are numerous questions on SO asking:
- how to get a count of specific values from the comma separated list
- how to get records that have only the same 2/3/etc specific value from that comma separated list
Another problem with the comma separated list is ensuring the values are consistent - storing text means the possibility of typos...
These are all symptoms of denormalized data, and highlight why you should always model for normalized data. Denormalization can be a query optimization, to be applied when the need actually presents itself.
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
add a comment |
"One reason was laziness".
This rings alarm bells. The only reason you should do something like this is that you know how to do it "the right way" but you have come to the conclusion that there is a tangible reason not to do it that way.
Having said this: if the data you are choosing to store this way is data that you will never need to query by, then there may be a case for storing it in the way you have chosen.
(Some users would dispute the statement in my previous paragraph, saying that "you can never know what requirements will be added in the future". These users are either misguided or stating a religious conviction. Sometimes it is advantageous to work to the requirements you have before you.)
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
I always hear some people saying that "my design is more flexible than yours" when I confront them about things like not setting up foreign key constraints, or storing lists in a single field. To me, flexibility (in such cases) == no discipline == laziness.
– foresightyj
Jan 30 '15 at 3:28
add a comment |
In general anything can be defensible if it meets the requirements of your project. This doesn't mean that people will agree with or want to defend your decision...
In general, storing data in this way is suboptimal (e.g. harder to do efficient queries) and may cause maintenance issues if you modify the items in your form. Perhaps you could have found a middle ground and used an integer representing a set of bit flags instead?
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
3
+1 for the suggestion of alternative approaches.
– Hammerite
Sep 6 '10 at 18:35
add a comment |
Yes, I would say that it really is that bad. It's a defensible choice, but that doesn't make it correct or good.
It breaks first normal form.
A second criticism is that putting raw input results directly into a database, without any validation or binding at all, leaves you open to SQL injection attacks.
What you're calling laziness and lack of SQL knowledge is the stuff that neophytes are made of. I'd recommend taking the time to do it properly and view it as an opportunity to learn.
Or leave it as it is and learn the painful lesson of a SQL injection attack.
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
16
I don't see anything in this question that suggests he is vulnerable to SQL injection. SQL injection and database normalisation are orthogonal topics, and your digression on injection is irrelevant to the question.
– Hammerite
Sep 6 '10 at 18:24
4
@Paul: And maybe the same attitude will lead to him being hit by a bus when he fails to look both ways before crossing the street, but you haven't warned him about that. Edit: I had thought you were the poster of this answer, my mistake.
– Hammerite
Sep 6 '10 at 18:29
1
@Hammerite - your extrapolation to buses is ridiculous.
– duffymo
Sep 6 '10 at 19:06
3
Yes, it was intended to be ridiculous. Its ridiculousness illustrates the point I'm making, which is that it makes no sense to warn him against something you have no reason to think he needs to be warned about.
– Hammerite
Sep 6 '10 at 21:21
1
Yes, I see. I think I had far more reason that your warning about buses.
– duffymo
Sep 6 '10 at 21:24
|
show 3 more comments
I needed a multi-value column, it could be implemented as an xml field
It could be converted to a comma delimited as necessary
querying an XML list in sql server using Xquery.
By being an xml field, some of the concerns can be addressed.
With CSV: Can't ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
With XML: values in a tag can be forced to be the correct type
With CSV: Can't use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
With XML: still an issue
With CSV: Can't enforce uniqueness: no way to prevent 1,2,3,3,3,5
With XML: still an issue
With CSV: Can't delete a value from the list without fetching the whole list.
With XML: single items can be removed
With CSV: Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan.
With XML: xml field can be indexed
With CSV: Hard to count elements in the list, or do other aggregate queries.**
With XML: not particularly hard
With CSV: Hard to join the values to the lookup table they reference.**
With XML: not particularly hard
With CSV: Hard to fetch the list in sorted order.
With XML: not particularly hard
With CSV: Storing integers as strings takes about twice as much space as storing binary integers.
With XML: storage is even worse than a csv
With CSV: Plus a lot of comma characters.
With XML: tags are used instead of commas
In short, using XML gets around some of the issues with delimited list AND can be converted to a delimited list as needed
edited May 23 '17 at 11:47
Community♦
11
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
add a comment |
Yes, it is that bad. My view is that if you don't like using relational databases then look for an alternative that suits you better, there are lots of interesting "NOSQL" projects out there with some really advanced features.
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
add a comment |
Well I've been using a key/value pair tab separated list in a NTEXT column in SQL Server for more than 4 years now and it works. You do lose the flexibility of making queries but on the other hand, if you have a library that persists/derpersists the key value pair then it's not a that bad idea.
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
11
No, it's a horrible idea. You've managed to get away with it, but the cost of your few minutes of development time has cost you lousy query performance, flexibility, and maintainability of your code.
– Paul Tomblin
Sep 6 '10 at 18:28
4
Paul, I agree. But as I said I used if for a specific purpose, and that is for a data entry operation where you have many kinds of forms. I am revising the design now that I have learnt NHibernate but back then I needed the flexibity to design the form in ASP.NET and use the textbox ids as key in the key/value pair.
– Raj
Sep 7 '10 at 6:58
25
+1 just to counter the downvotes. Telling someone who has maintained the app for 4 years about maintenance concerns is a bit presumptuous. There's very few "horrible" ideas in sw development - mostly they're just ideas with very limited applicability. It's reasonable to warn folks on the limitations, but chastising those who have done it and lived through it strikes me as a holier-than-thou attitude I can do without.
– Mark Brackett
Jul 9 '13 at 22:55
add a comment |
I would probably take the middle ground: make each field in the CSV into a separate column in the database, but not worry much about normalization (at least for now). At some point, normalization might become interesting, but with all the data shoved into a single column you're gaining virtually no benefit from using a database at all. You need to separate the data into logical fields/columns/whatever you want to call them before you can manipulate it meaningfully at all.
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
The form contains some more fields, this is only one part of the form (which I did not explain well in the question).
– Mad Scientist
Sep 6 '10 at 18:29
add a comment |
If you have a fixed number of boolean fields, you could use a INT(1) NOT NULL (or BIT NOT NULL if it exists) or CHAR (0) (nullable) for each. You could also use a SET (I forget the exact syntax).
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
add a comment |
protected by Stefano Borini Mar 30 '13 at 14:37
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
10 Answers
10
active
oldest
votes
10 Answers
10
active
oldest
votes
active
oldest
votes
active
oldest
votes
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems:
- Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
- Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
- Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
- Can’t delete a value from the list without fetching the whole list.
- Can't store a list longer than what fits in the string column.
- Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'*
- Hard to count elements in the list, or do other aggregate queries.
- Hard to join the values to the lookup table they reference.
- Hard to fetch the list in sorted order.
- Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.
To solve these problems, you have to write tons of application code, reinventing functionality that the RDBMS already provides much more efficiently.
Comma-separated lists are wrong enough that I made this the first chapter in my book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
There are times when you need to employ denormalization, but as @OMG Ponies mentions, these are exception cases. Any non-relational “optimization” benefits one type of query at the expense of other uses of the data, so be sure you know which of your queries need to be treated so specially that they deserve denormalization.
* MySQL 8.0 no longer supports this word-boundary expression syntax.
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
6
Thanks for the detailed list, I thought of some of those problems but certainly not of all of them.
– Mad Scientist
Sep 6 '10 at 18:35
5
Book looks interesting so I bought it. I've long thought it was subject matter that needed a book.
– HLGEM
Sep 7 '10 at 14:01
7
An ARRAY (of any datatype) can fix the exception, just check PostgreSQL: postgresql.org/docs/current/static/arrays.html (@Bill: Great book, a must read for any developer or dba)
– Frank Heikens
Nov 24 '11 at 21:18
4
+1 bill Karwin Great answer! Lovely concise bullet points. That looks like a great book too. Love the cover too +1 NullUserException. I'm in the process of designing the schema for a MySQL database to replace a flat file text based system. I've encountered several dilemmas so far. So this book will be worth buying.
– therobyouknow
Jan 30 '12 at 16:16
8
Shouldn't banana come before 3 rather than after?
– Jay
Feb 19 '15 at 21:31
|
show 13 more comments
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems:
- Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
- Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
- Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
- Can’t delete a value from the list without fetching the whole list.
- Can't store a list longer than what fits in the string column.
- Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'*
- Hard to count elements in the list, or do other aggregate queries.
- Hard to join the values to the lookup table they reference.
- Hard to fetch the list in sorted order.
- Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.
To solve these problems, you have to write tons of application code, reinventing functionality that the RDBMS already provides much more efficiently.
Comma-separated lists are wrong enough that I made this the first chapter in my book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
There are times when you need to employ denormalization, but as @OMG Ponies mentions, these are exception cases. Any non-relational “optimization” benefits one type of query at the expense of other uses of the data, so be sure you know which of your queries need to be treated so specially that they deserve denormalization.
* MySQL 8.0 no longer supports this word-boundary expression syntax.
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
6
Thanks for the detailed list, I thought of some of those problems but certainly not of all of them.
– Mad Scientist
Sep 6 '10 at 18:35
5
Book looks interesting so I bought it. I've long thought it was subject matter that needed a book.
– HLGEM
Sep 7 '10 at 14:01
7
An ARRAY (of any datatype) can fix the exception, just check PostgreSQL: postgresql.org/docs/current/static/arrays.html (@Bill: Great book, a must read for any developer or dba)
– Frank Heikens
Nov 24 '11 at 21:18
4
+1 bill Karwin Great answer! Lovely concise bullet points. That looks like a great book too. Love the cover too +1 NullUserException. I'm in the process of designing the schema for a MySQL database to replace a flat file text based system. I've encountered several dilemmas so far. So this book will be worth buying.
– therobyouknow
Jan 30 '12 at 16:16
8
Shouldn't banana come before 3 rather than after?
– Jay
Feb 19 '15 at 21:31
|
show 13 more comments
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems:
- Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
- Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
- Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
- Can’t delete a value from the list without fetching the whole list.
- Can't store a list longer than what fits in the string column.
- Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'*
- Hard to count elements in the list, or do other aggregate queries.
- Hard to join the values to the lookup table they reference.
- Hard to fetch the list in sorted order.
- Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.
To solve these problems, you have to write tons of application code, reinventing functionality that the RDBMS already provides much more efficiently.
Comma-separated lists are wrong enough that I made this the first chapter in my book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
There are times when you need to employ denormalization, but as @OMG Ponies mentions, these are exception cases. Any non-relational “optimization” benefits one type of query at the expense of other uses of the data, so be sure you know which of your queries need to be treated so specially that they deserve denormalization.
* MySQL 8.0 no longer supports this word-boundary expression syntax.
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
In addition to violating First Normal Form because of the repeating group of values stored in a single column, comma-separated lists have a lot of other more practical problems:
- Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
- Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
- Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
- Can’t delete a value from the list without fetching the whole list.
- Can't store a list longer than what fits in the string column.
- Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL:
idlist REGEXP '[[:<:]]2[[:>:]]'*
- Hard to count elements in the list, or do other aggregate queries.
- Hard to join the values to the lookup table they reference.
- Hard to fetch the list in sorted order.
- Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.
To solve these problems, you have to write tons of application code, reinventing functionality that the RDBMS already provides much more efficiently.
Comma-separated lists are wrong enough that I made this the first chapter in my book: SQL Antipatterns: Avoiding the Pitfalls of Database Programming.
There are times when you need to employ denormalization, but as @OMG Ponies mentions, these are exception cases. Any non-relational “optimization” benefits one type of query at the expense of other uses of the data, so be sure you know which of your queries need to be treated so specially that they deserve denormalization.
* MySQL 8.0 no longer supports this word-boundary expression syntax.
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
edited Aug 8 '18 at 20:38
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
answered Sep 6 '10 at 18:26
Bill KarwinBill Karwin
380k64518672
380k64518672
6
Thanks for the detailed list, I thought of some of those problems but certainly not of all of them.
– Mad Scientist
Sep 6 '10 at 18:35
5
Book looks interesting so I bought it. I've long thought it was subject matter that needed a book.
– HLGEM
Sep 7 '10 at 14:01
7
An ARRAY (of any datatype) can fix the exception, just check PostgreSQL: postgresql.org/docs/current/static/arrays.html (@Bill: Great book, a must read for any developer or dba)
– Frank Heikens
Nov 24 '11 at 21:18
4
+1 bill Karwin Great answer! Lovely concise bullet points. That looks like a great book too. Love the cover too +1 NullUserException. I'm in the process of designing the schema for a MySQL database to replace a flat file text based system. I've encountered several dilemmas so far. So this book will be worth buying.
– therobyouknow
Jan 30 '12 at 16:16
8
Shouldn't banana come before 3 rather than after?
– Jay
Feb 19 '15 at 21:31
|
show 13 more comments
6
Thanks for the detailed list, I thought of some of those problems but certainly not of all of them.
– Mad Scientist
Sep 6 '10 at 18:35
5
Book looks interesting so I bought it. I've long thought it was subject matter that needed a book.
– HLGEM
Sep 7 '10 at 14:01
7
An ARRAY (of any datatype) can fix the exception, just check PostgreSQL: postgresql.org/docs/current/static/arrays.html (@Bill: Great book, a must read for any developer or dba)
– Frank Heikens
Nov 24 '11 at 21:18
4
+1 bill Karwin Great answer! Lovely concise bullet points. That looks like a great book too. Love the cover too +1 NullUserException. I'm in the process of designing the schema for a MySQL database to replace a flat file text based system. I've encountered several dilemmas so far. So this book will be worth buying.
– therobyouknow
Jan 30 '12 at 16:16
8
Shouldn't banana come before 3 rather than after?
– Jay
Feb 19 '15 at 21:31
6
6
Thanks for the detailed list, I thought of some of those problems but certainly not of all of them.
– Mad Scientist
Sep 6 '10 at 18:35
Thanks for the detailed list, I thought of some of those problems but certainly not of all of them.
– Mad Scientist
Sep 6 '10 at 18:35
5
5
Book looks interesting so I bought it. I've long thought it was subject matter that needed a book.
– HLGEM
Sep 7 '10 at 14:01
Book looks interesting so I bought it. I've long thought it was subject matter that needed a book.
– HLGEM
Sep 7 '10 at 14:01
7
7
An ARRAY (of any datatype) can fix the exception, just check PostgreSQL: postgresql.org/docs/current/static/arrays.html (@Bill: Great book, a must read for any developer or dba)
– Frank Heikens
Nov 24 '11 at 21:18
An ARRAY (of any datatype) can fix the exception, just check PostgreSQL: postgresql.org/docs/current/static/arrays.html (@Bill: Great book, a must read for any developer or dba)
– Frank Heikens
Nov 24 '11 at 21:18
4
4
+1 bill Karwin Great answer! Lovely concise bullet points. That looks like a great book too. Love the cover too +1 NullUserException. I'm in the process of designing the schema for a MySQL database to replace a flat file text based system. I've encountered several dilemmas so far. So this book will be worth buying.
– therobyouknow
Jan 30 '12 at 16:16
+1 bill Karwin Great answer! Lovely concise bullet points. That looks like a great book too. Love the cover too +1 NullUserException. I'm in the process of designing the schema for a MySQL database to replace a flat file text based system. I've encountered several dilemmas so far. So this book will be worth buying.
– therobyouknow
Jan 30 '12 at 16:16
8
8
Shouldn't banana come before 3 rather than after?
– Jay
Feb 19 '15 at 21:31
Shouldn't banana come before 3 rather than after?
– Jay
Feb 19 '15 at 21:31
|
show 13 more comments
There are numerous questions on SO asking:
- how to get a count of specific values from the comma separated list
- how to get records that have only the same 2/3/etc specific value from that comma separated list
Another problem with the comma separated list is ensuring the values are consistent - storing text means the possibility of typos...
These are all symptoms of denormalized data, and highlight why you should always model for normalized data. Denormalization can be a query optimization, to be applied when the need actually presents itself.
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
add a comment |
There are numerous questions on SO asking:
- how to get a count of specific values from the comma separated list
- how to get records that have only the same 2/3/etc specific value from that comma separated list
Another problem with the comma separated list is ensuring the values are consistent - storing text means the possibility of typos...
These are all symptoms of denormalized data, and highlight why you should always model for normalized data. Denormalization can be a query optimization, to be applied when the need actually presents itself.
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
add a comment |
There are numerous questions on SO asking:
- how to get a count of specific values from the comma separated list
- how to get records that have only the same 2/3/etc specific value from that comma separated list
Another problem with the comma separated list is ensuring the values are consistent - storing text means the possibility of typos...
These are all symptoms of denormalized data, and highlight why you should always model for normalized data. Denormalization can be a query optimization, to be applied when the need actually presents itself.
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
There are numerous questions on SO asking:
- how to get a count of specific values from the comma separated list
- how to get records that have only the same 2/3/etc specific value from that comma separated list
Another problem with the comma separated list is ensuring the values are consistent - storing text means the possibility of typos...
These are all symptoms of denormalized data, and highlight why you should always model for normalized data. Denormalization can be a query optimization, to be applied when the need actually presents itself.
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
answered Sep 6 '10 at 18:17
OMG PoniesOMG Ponies
259k62443469
259k62443469
add a comment |
add a comment |
"One reason was laziness".
This rings alarm bells. The only reason you should do something like this is that you know how to do it "the right way" but you have come to the conclusion that there is a tangible reason not to do it that way.
Having said this: if the data you are choosing to store this way is data that you will never need to query by, then there may be a case for storing it in the way you have chosen.
(Some users would dispute the statement in my previous paragraph, saying that "you can never know what requirements will be added in the future". These users are either misguided or stating a religious conviction. Sometimes it is advantageous to work to the requirements you have before you.)
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
I always hear some people saying that "my design is more flexible than yours" when I confront them about things like not setting up foreign key constraints, or storing lists in a single field. To me, flexibility (in such cases) == no discipline == laziness.
– foresightyj
Jan 30 '15 at 3:28
add a comment |
"One reason was laziness".
This rings alarm bells. The only reason you should do something like this is that you know how to do it "the right way" but you have come to the conclusion that there is a tangible reason not to do it that way.
Having said this: if the data you are choosing to store this way is data that you will never need to query by, then there may be a case for storing it in the way you have chosen.
(Some users would dispute the statement in my previous paragraph, saying that "you can never know what requirements will be added in the future". These users are either misguided or stating a religious conviction. Sometimes it is advantageous to work to the requirements you have before you.)
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
I always hear some people saying that "my design is more flexible than yours" when I confront them about things like not setting up foreign key constraints, or storing lists in a single field. To me, flexibility (in such cases) == no discipline == laziness.
– foresightyj
Jan 30 '15 at 3:28
add a comment |
"One reason was laziness".
This rings alarm bells. The only reason you should do something like this is that you know how to do it "the right way" but you have come to the conclusion that there is a tangible reason not to do it that way.
Having said this: if the data you are choosing to store this way is data that you will never need to query by, then there may be a case for storing it in the way you have chosen.
(Some users would dispute the statement in my previous paragraph, saying that "you can never know what requirements will be added in the future". These users are either misguided or stating a religious conviction. Sometimes it is advantageous to work to the requirements you have before you.)
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
"One reason was laziness".
This rings alarm bells. The only reason you should do something like this is that you know how to do it "the right way" but you have come to the conclusion that there is a tangible reason not to do it that way.
Having said this: if the data you are choosing to store this way is data that you will never need to query by, then there may be a case for storing it in the way you have chosen.
(Some users would dispute the statement in my previous paragraph, saying that "you can never know what requirements will be added in the future". These users are either misguided or stating a religious conviction. Sometimes it is advantageous to work to the requirements you have before you.)
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
edited Sep 22 '11 at 17:04
cHao
68.7k14115154
68.7k14115154
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
answered Sep 6 '10 at 18:22
HammeriteHammerite
17k35377
17k35377
I always hear some people saying that "my design is more flexible than yours" when I confront them about things like not setting up foreign key constraints, or storing lists in a single field. To me, flexibility (in such cases) == no discipline == laziness.
– foresightyj
Jan 30 '15 at 3:28
add a comment |
I always hear some people saying that "my design is more flexible than yours" when I confront them about things like not setting up foreign key constraints, or storing lists in a single field. To me, flexibility (in such cases) == no discipline == laziness.
– foresightyj
Jan 30 '15 at 3:28
I always hear some people saying that "my design is more flexible than yours" when I confront them about things like not setting up foreign key constraints, or storing lists in a single field. To me, flexibility (in such cases) == no discipline == laziness.
– foresightyj
Jan 30 '15 at 3:28
I always hear some people saying that "my design is more flexible than yours" when I confront them about things like not setting up foreign key constraints, or storing lists in a single field. To me, flexibility (in such cases) == no discipline == laziness.
– foresightyj
Jan 30 '15 at 3:28
add a comment |
In general anything can be defensible if it meets the requirements of your project. This doesn't mean that people will agree with or want to defend your decision...
In general, storing data in this way is suboptimal (e.g. harder to do efficient queries) and may cause maintenance issues if you modify the items in your form. Perhaps you could have found a middle ground and used an integer representing a set of bit flags instead?
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
3
+1 for the suggestion of alternative approaches.
– Hammerite
Sep 6 '10 at 18:35
add a comment |
In general anything can be defensible if it meets the requirements of your project. This doesn't mean that people will agree with or want to defend your decision...
In general, storing data in this way is suboptimal (e.g. harder to do efficient queries) and may cause maintenance issues if you modify the items in your form. Perhaps you could have found a middle ground and used an integer representing a set of bit flags instead?
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
3
+1 for the suggestion of alternative approaches.
– Hammerite
Sep 6 '10 at 18:35
add a comment |
In general anything can be defensible if it meets the requirements of your project. This doesn't mean that people will agree with or want to defend your decision...
In general, storing data in this way is suboptimal (e.g. harder to do efficient queries) and may cause maintenance issues if you modify the items in your form. Perhaps you could have found a middle ground and used an integer representing a set of bit flags instead?
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
In general anything can be defensible if it meets the requirements of your project. This doesn't mean that people will agree with or want to defend your decision...
In general, storing data in this way is suboptimal (e.g. harder to do efficient queries) and may cause maintenance issues if you modify the items in your form. Perhaps you could have found a middle ground and used an integer representing a set of bit flags instead?
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
answered Sep 6 '10 at 18:20
bobbymcrbobbymcr
20.6k24361
20.6k24361
3
+1 for the suggestion of alternative approaches.
– Hammerite
Sep 6 '10 at 18:35
add a comment |
3
+1 for the suggestion of alternative approaches.
– Hammerite
Sep 6 '10 at 18:35
3
3
+1 for the suggestion of alternative approaches.
– Hammerite
Sep 6 '10 at 18:35
+1 for the suggestion of alternative approaches.
– Hammerite
Sep 6 '10 at 18:35
add a comment |
Yes, I would say that it really is that bad. It's a defensible choice, but that doesn't make it correct or good.
It breaks first normal form.
A second criticism is that putting raw input results directly into a database, without any validation or binding at all, leaves you open to SQL injection attacks.
What you're calling laziness and lack of SQL knowledge is the stuff that neophytes are made of. I'd recommend taking the time to do it properly and view it as an opportunity to learn.
Or leave it as it is and learn the painful lesson of a SQL injection attack.
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
16
I don't see anything in this question that suggests he is vulnerable to SQL injection. SQL injection and database normalisation are orthogonal topics, and your digression on injection is irrelevant to the question.
– Hammerite
Sep 6 '10 at 18:24
4
@Paul: And maybe the same attitude will lead to him being hit by a bus when he fails to look both ways before crossing the street, but you haven't warned him about that. Edit: I had thought you were the poster of this answer, my mistake.
– Hammerite
Sep 6 '10 at 18:29
1
@Hammerite - your extrapolation to buses is ridiculous.
– duffymo
Sep 6 '10 at 19:06
3
Yes, it was intended to be ridiculous. Its ridiculousness illustrates the point I'm making, which is that it makes no sense to warn him against something you have no reason to think he needs to be warned about.
– Hammerite
Sep 6 '10 at 21:21
1
Yes, I see. I think I had far more reason that your warning about buses.
– duffymo
Sep 6 '10 at 21:24
|
show 3 more comments
Yes, I would say that it really is that bad. It's a defensible choice, but that doesn't make it correct or good.
It breaks first normal form.
A second criticism is that putting raw input results directly into a database, without any validation or binding at all, leaves you open to SQL injection attacks.
What you're calling laziness and lack of SQL knowledge is the stuff that neophytes are made of. I'd recommend taking the time to do it properly and view it as an opportunity to learn.
Or leave it as it is and learn the painful lesson of a SQL injection attack.
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
16
I don't see anything in this question that suggests he is vulnerable to SQL injection. SQL injection and database normalisation are orthogonal topics, and your digression on injection is irrelevant to the question.
– Hammerite
Sep 6 '10 at 18:24
4
@Paul: And maybe the same attitude will lead to him being hit by a bus when he fails to look both ways before crossing the street, but you haven't warned him about that. Edit: I had thought you were the poster of this answer, my mistake.
– Hammerite
Sep 6 '10 at 18:29
1
@Hammerite - your extrapolation to buses is ridiculous.
– duffymo
Sep 6 '10 at 19:06
3
Yes, it was intended to be ridiculous. Its ridiculousness illustrates the point I'm making, which is that it makes no sense to warn him against something you have no reason to think he needs to be warned about.
– Hammerite
Sep 6 '10 at 21:21
1
Yes, I see. I think I had far more reason that your warning about buses.
– duffymo
Sep 6 '10 at 21:24
|
show 3 more comments
Yes, I would say that it really is that bad. It's a defensible choice, but that doesn't make it correct or good.
It breaks first normal form.
A second criticism is that putting raw input results directly into a database, without any validation or binding at all, leaves you open to SQL injection attacks.
What you're calling laziness and lack of SQL knowledge is the stuff that neophytes are made of. I'd recommend taking the time to do it properly and view it as an opportunity to learn.
Or leave it as it is and learn the painful lesson of a SQL injection attack.
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
Yes, I would say that it really is that bad. It's a defensible choice, but that doesn't make it correct or good.
It breaks first normal form.
A second criticism is that putting raw input results directly into a database, without any validation or binding at all, leaves you open to SQL injection attacks.
What you're calling laziness and lack of SQL knowledge is the stuff that neophytes are made of. I'd recommend taking the time to do it properly and view it as an opportunity to learn.
Or leave it as it is and learn the painful lesson of a SQL injection attack.
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
answered Sep 6 '10 at 18:16
duffymoduffymo
271k33318507
271k33318507
16
I don't see anything in this question that suggests he is vulnerable to SQL injection. SQL injection and database normalisation are orthogonal topics, and your digression on injection is irrelevant to the question.
– Hammerite
Sep 6 '10 at 18:24
4
@Paul: And maybe the same attitude will lead to him being hit by a bus when he fails to look both ways before crossing the street, but you haven't warned him about that. Edit: I had thought you were the poster of this answer, my mistake.
– Hammerite
Sep 6 '10 at 18:29
1
@Hammerite - your extrapolation to buses is ridiculous.
– duffymo
Sep 6 '10 at 19:06
3
Yes, it was intended to be ridiculous. Its ridiculousness illustrates the point I'm making, which is that it makes no sense to warn him against something you have no reason to think he needs to be warned about.
– Hammerite
Sep 6 '10 at 21:21
1
Yes, I see. I think I had far more reason that your warning about buses.
– duffymo
Sep 6 '10 at 21:24
|
show 3 more comments
16
I don't see anything in this question that suggests he is vulnerable to SQL injection. SQL injection and database normalisation are orthogonal topics, and your digression on injection is irrelevant to the question.
– Hammerite
Sep 6 '10 at 18:24
4
@Paul: And maybe the same attitude will lead to him being hit by a bus when he fails to look both ways before crossing the street, but you haven't warned him about that. Edit: I had thought you were the poster of this answer, my mistake.
– Hammerite
Sep 6 '10 at 18:29
1
@Hammerite - your extrapolation to buses is ridiculous.
– duffymo
Sep 6 '10 at 19:06
3
Yes, it was intended to be ridiculous. Its ridiculousness illustrates the point I'm making, which is that it makes no sense to warn him against something you have no reason to think he needs to be warned about.
– Hammerite
Sep 6 '10 at 21:21
1
Yes, I see. I think I had far more reason that your warning about buses.
– duffymo
Sep 6 '10 at 21:24
16
16
I don't see anything in this question that suggests he is vulnerable to SQL injection. SQL injection and database normalisation are orthogonal topics, and your digression on injection is irrelevant to the question.
– Hammerite
Sep 6 '10 at 18:24
I don't see anything in this question that suggests he is vulnerable to SQL injection. SQL injection and database normalisation are orthogonal topics, and your digression on injection is irrelevant to the question.
– Hammerite
Sep 6 '10 at 18:24
4
4
@Paul: And maybe the same attitude will lead to him being hit by a bus when he fails to look both ways before crossing the street, but you haven't warned him about that. Edit: I had thought you were the poster of this answer, my mistake.
– Hammerite
Sep 6 '10 at 18:29
@Paul: And maybe the same attitude will lead to him being hit by a bus when he fails to look both ways before crossing the street, but you haven't warned him about that. Edit: I had thought you were the poster of this answer, my mistake.
– Hammerite
Sep 6 '10 at 18:29
1
1
@Hammerite - your extrapolation to buses is ridiculous.
– duffymo
Sep 6 '10 at 19:06
@Hammerite - your extrapolation to buses is ridiculous.
– duffymo
Sep 6 '10 at 19:06
3
3
Yes, it was intended to be ridiculous. Its ridiculousness illustrates the point I'm making, which is that it makes no sense to warn him against something you have no reason to think he needs to be warned about.
– Hammerite
Sep 6 '10 at 21:21
Yes, it was intended to be ridiculous. Its ridiculousness illustrates the point I'm making, which is that it makes no sense to warn him against something you have no reason to think he needs to be warned about.
– Hammerite
Sep 6 '10 at 21:21
1
1
Yes, I see. I think I had far more reason that your warning about buses.
– duffymo
Sep 6 '10 at 21:24
Yes, I see. I think I had far more reason that your warning about buses.
– duffymo
Sep 6 '10 at 21:24
|
show 3 more comments
I needed a multi-value column, it could be implemented as an xml field
It could be converted to a comma delimited as necessary
querying an XML list in sql server using Xquery.
By being an xml field, some of the concerns can be addressed.
With CSV: Can't ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
With XML: values in a tag can be forced to be the correct type
With CSV: Can't use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
With XML: still an issue
With CSV: Can't enforce uniqueness: no way to prevent 1,2,3,3,3,5
With XML: still an issue
With CSV: Can't delete a value from the list without fetching the whole list.
With XML: single items can be removed
With CSV: Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan.
With XML: xml field can be indexed
With CSV: Hard to count elements in the list, or do other aggregate queries.**
With XML: not particularly hard
With CSV: Hard to join the values to the lookup table they reference.**
With XML: not particularly hard
With CSV: Hard to fetch the list in sorted order.
With XML: not particularly hard
With CSV: Storing integers as strings takes about twice as much space as storing binary integers.
With XML: storage is even worse than a csv
With CSV: Plus a lot of comma characters.
With XML: tags are used instead of commas
In short, using XML gets around some of the issues with delimited list AND can be converted to a delimited list as needed
edited May 23 '17 at 11:47
Community♦
11
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
add a comment |
I needed a multi-value column, it could be implemented as an xml field
It could be converted to a comma delimited as necessary
querying an XML list in sql server using Xquery.
By being an xml field, some of the concerns can be addressed.
With CSV: Can't ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
With XML: values in a tag can be forced to be the correct type
With CSV: Can't use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
With XML: still an issue
With CSV: Can't enforce uniqueness: no way to prevent 1,2,3,3,3,5
With XML: still an issue
With CSV: Can't delete a value from the list without fetching the whole list.
With XML: single items can be removed
With CSV: Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan.
With XML: xml field can be indexed
With CSV: Hard to count elements in the list, or do other aggregate queries.**
With XML: not particularly hard
With CSV: Hard to join the values to the lookup table they reference.**
With XML: not particularly hard
With CSV: Hard to fetch the list in sorted order.
With XML: not particularly hard
With CSV: Storing integers as strings takes about twice as much space as storing binary integers.
With XML: storage is even worse than a csv
With CSV: Plus a lot of comma characters.
With XML: tags are used instead of commas
In short, using XML gets around some of the issues with delimited list AND can be converted to a delimited list as needed
edited May 23 '17 at 11:47
Community♦
11
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
add a comment |
I needed a multi-value column, it could be implemented as an xml field
It could be converted to a comma delimited as necessary
querying an XML list in sql server using Xquery.
By being an xml field, some of the concerns can be addressed.
With CSV: Can't ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
With XML: values in a tag can be forced to be the correct type
With CSV: Can't use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
With XML: still an issue
With CSV: Can't enforce uniqueness: no way to prevent 1,2,3,3,3,5
With XML: still an issue
With CSV: Can't delete a value from the list without fetching the whole list.
With XML: single items can be removed
With CSV: Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan.
With XML: xml field can be indexed
With CSV: Hard to count elements in the list, or do other aggregate queries.**
With XML: not particularly hard
With CSV: Hard to join the values to the lookup table they reference.**
With XML: not particularly hard
With CSV: Hard to fetch the list in sorted order.
With XML: not particularly hard
With CSV: Storing integers as strings takes about twice as much space as storing binary integers.
With XML: storage is even worse than a csv
With CSV: Plus a lot of comma characters.
With XML: tags are used instead of commas
In short, using XML gets around some of the issues with delimited list AND can be converted to a delimited list as needed
edited May 23 '17 at 11:47
Community♦
11
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
I needed a multi-value column, it could be implemented as an xml field
It could be converted to a comma delimited as necessary
querying an XML list in sql server using Xquery.
By being an xml field, some of the concerns can be addressed.
With CSV: Can't ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
With XML: values in a tag can be forced to be the correct type
With CSV: Can't use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
With XML: still an issue
With CSV: Can't enforce uniqueness: no way to prevent 1,2,3,3,3,5
With XML: still an issue
With CSV: Can't delete a value from the list without fetching the whole list.
With XML: single items can be removed
With CSV: Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan.
With XML: xml field can be indexed
With CSV: Hard to count elements in the list, or do other aggregate queries.**
With XML: not particularly hard
With CSV: Hard to join the values to the lookup table they reference.**
With XML: not particularly hard
With CSV: Hard to fetch the list in sorted order.
With XML: not particularly hard
With CSV: Storing integers as strings takes about twice as much space as storing binary integers.
With XML: storage is even worse than a csv
With CSV: Plus a lot of comma characters.
With XML: tags are used instead of commas
In short, using XML gets around some of the issues with delimited list AND can be converted to a delimited list as needed
edited May 23 '17 at 11:47
Community♦
11
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
edited May 23 '17 at 11:47
Community♦
11
edited May 23 '17 at 11:47
Community♦
11
edited May 23 '17 at 11:47
Community♦
11
11
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
answered Jul 12 '13 at 17:23
James A MohlerJames A Mohler
7,135123353
7,135123353
add a comment |
add a comment |
Yes, it is that bad. My view is that if you don't like using relational databases then look for an alternative that suits you better, there are lots of interesting "NOSQL" projects out there with some really advanced features.
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
add a comment |
Yes, it is that bad. My view is that if you don't like using relational databases then look for an alternative that suits you better, there are lots of interesting "NOSQL" projects out there with some really advanced features.
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
add a comment |
Yes, it is that bad. My view is that if you don't like using relational databases then look for an alternative that suits you better, there are lots of interesting "NOSQL" projects out there with some really advanced features.
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
Yes, it is that bad. My view is that if you don't like using relational databases then look for an alternative that suits you better, there are lots of interesting "NOSQL" projects out there with some really advanced features.
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
answered Sep 6 '10 at 18:40
RobinRobin
3,9361320
3,9361320
add a comment |
add a comment |
Well I've been using a key/value pair tab separated list in a NTEXT column in SQL Server for more than 4 years now and it works. You do lose the flexibility of making queries but on the other hand, if you have a library that persists/derpersists the key value pair then it's not a that bad idea.
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
11
No, it's a horrible idea. You've managed to get away with it, but the cost of your few minutes of development time has cost you lousy query performance, flexibility, and maintainability of your code.
– Paul Tomblin
Sep 6 '10 at 18:28
4
Paul, I agree. But as I said I used if for a specific purpose, and that is for a data entry operation where you have many kinds of forms. I am revising the design now that I have learnt NHibernate but back then I needed the flexibity to design the form in ASP.NET and use the textbox ids as key in the key/value pair.
– Raj
Sep 7 '10 at 6:58
25
+1 just to counter the downvotes. Telling someone who has maintained the app for 4 years about maintenance concerns is a bit presumptuous. There's very few "horrible" ideas in sw development - mostly they're just ideas with very limited applicability. It's reasonable to warn folks on the limitations, but chastising those who have done it and lived through it strikes me as a holier-than-thou attitude I can do without.
– Mark Brackett
Jul 9 '13 at 22:55
add a comment |
Well I've been using a key/value pair tab separated list in a NTEXT column in SQL Server for more than 4 years now and it works. You do lose the flexibility of making queries but on the other hand, if you have a library that persists/derpersists the key value pair then it's not a that bad idea.
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
11
No, it's a horrible idea. You've managed to get away with it, but the cost of your few minutes of development time has cost you lousy query performance, flexibility, and maintainability of your code.
– Paul Tomblin
Sep 6 '10 at 18:28
4
Paul, I agree. But as I said I used if for a specific purpose, and that is for a data entry operation where you have many kinds of forms. I am revising the design now that I have learnt NHibernate but back then I needed the flexibity to design the form in ASP.NET and use the textbox ids as key in the key/value pair.
– Raj
Sep 7 '10 at 6:58
25
+1 just to counter the downvotes. Telling someone who has maintained the app for 4 years about maintenance concerns is a bit presumptuous. There's very few "horrible" ideas in sw development - mostly they're just ideas with very limited applicability. It's reasonable to warn folks on the limitations, but chastising those who have done it and lived through it strikes me as a holier-than-thou attitude I can do without.
– Mark Brackett
Jul 9 '13 at 22:55
add a comment |
Well I've been using a key/value pair tab separated list in a NTEXT column in SQL Server for more than 4 years now and it works. You do lose the flexibility of making queries but on the other hand, if you have a library that persists/derpersists the key value pair then it's not a that bad idea.
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
Well I've been using a key/value pair tab separated list in a NTEXT column in SQL Server for more than 4 years now and it works. You do lose the flexibility of making queries but on the other hand, if you have a library that persists/derpersists the key value pair then it's not a that bad idea.
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
answered Sep 6 '10 at 18:16
RajRaj
1,61511216
1,61511216
11
No, it's a horrible idea. You've managed to get away with it, but the cost of your few minutes of development time has cost you lousy query performance, flexibility, and maintainability of your code.
– Paul Tomblin
Sep 6 '10 at 18:28
4
Paul, I agree. But as I said I used if for a specific purpose, and that is for a data entry operation where you have many kinds of forms. I am revising the design now that I have learnt NHibernate but back then I needed the flexibity to design the form in ASP.NET and use the textbox ids as key in the key/value pair.
– Raj
Sep 7 '10 at 6:58
25
+1 just to counter the downvotes. Telling someone who has maintained the app for 4 years about maintenance concerns is a bit presumptuous. There's very few "horrible" ideas in sw development - mostly they're just ideas with very limited applicability. It's reasonable to warn folks on the limitations, but chastising those who have done it and lived through it strikes me as a holier-than-thou attitude I can do without.
– Mark Brackett
Jul 9 '13 at 22:55
add a comment |
11
No, it's a horrible idea. You've managed to get away with it, but the cost of your few minutes of development time has cost you lousy query performance, flexibility, and maintainability of your code.
– Paul Tomblin
Sep 6 '10 at 18:28
4
Paul, I agree. But as I said I used if for a specific purpose, and that is for a data entry operation where you have many kinds of forms. I am revising the design now that I have learnt NHibernate but back then I needed the flexibity to design the form in ASP.NET and use the textbox ids as key in the key/value pair.
– Raj
Sep 7 '10 at 6:58
25
+1 just to counter the downvotes. Telling someone who has maintained the app for 4 years about maintenance concerns is a bit presumptuous. There's very few "horrible" ideas in sw development - mostly they're just ideas with very limited applicability. It's reasonable to warn folks on the limitations, but chastising those who have done it and lived through it strikes me as a holier-than-thou attitude I can do without.
– Mark Brackett
Jul 9 '13 at 22:55
11
11
No, it's a horrible idea. You've managed to get away with it, but the cost of your few minutes of development time has cost you lousy query performance, flexibility, and maintainability of your code.
– Paul Tomblin
Sep 6 '10 at 18:28
No, it's a horrible idea. You've managed to get away with it, but the cost of your few minutes of development time has cost you lousy query performance, flexibility, and maintainability of your code.
– Paul Tomblin
Sep 6 '10 at 18:28
4
4
Paul, I agree. But as I said I used if for a specific purpose, and that is for a data entry operation where you have many kinds of forms. I am revising the design now that I have learnt NHibernate but back then I needed the flexibity to design the form in ASP.NET and use the textbox ids as key in the key/value pair.
– Raj
Sep 7 '10 at 6:58
Paul, I agree. But as I said I used if for a specific purpose, and that is for a data entry operation where you have many kinds of forms. I am revising the design now that I have learnt NHibernate but back then I needed the flexibity to design the form in ASP.NET and use the textbox ids as key in the key/value pair.
– Raj
Sep 7 '10 at 6:58
25
25
+1 just to counter the downvotes. Telling someone who has maintained the app for 4 years about maintenance concerns is a bit presumptuous. There's very few "horrible" ideas in sw development - mostly they're just ideas with very limited applicability. It's reasonable to warn folks on the limitations, but chastising those who have done it and lived through it strikes me as a holier-than-thou attitude I can do without.
– Mark Brackett
Jul 9 '13 at 22:55
+1 just to counter the downvotes. Telling someone who has maintained the app for 4 years about maintenance concerns is a bit presumptuous. There's very few "horrible" ideas in sw development - mostly they're just ideas with very limited applicability. It's reasonable to warn folks on the limitations, but chastising those who have done it and lived through it strikes me as a holier-than-thou attitude I can do without.
– Mark Brackett
Jul 9 '13 at 22:55
add a comment |
I would probably take the middle ground: make each field in the CSV into a separate column in the database, but not worry much about normalization (at least for now). At some point, normalization might become interesting, but with all the data shoved into a single column you're gaining virtually no benefit from using a database at all. You need to separate the data into logical fields/columns/whatever you want to call them before you can manipulate it meaningfully at all.
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
The form contains some more fields, this is only one part of the form (which I did not explain well in the question).
– Mad Scientist
Sep 6 '10 at 18:29
add a comment |
I would probably take the middle ground: make each field in the CSV into a separate column in the database, but not worry much about normalization (at least for now). At some point, normalization might become interesting, but with all the data shoved into a single column you're gaining virtually no benefit from using a database at all. You need to separate the data into logical fields/columns/whatever you want to call them before you can manipulate it meaningfully at all.
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
The form contains some more fields, this is only one part of the form (which I did not explain well in the question).
– Mad Scientist
Sep 6 '10 at 18:29
add a comment |
I would probably take the middle ground: make each field in the CSV into a separate column in the database, but not worry much about normalization (at least for now). At some point, normalization might become interesting, but with all the data shoved into a single column you're gaining virtually no benefit from using a database at all. You need to separate the data into logical fields/columns/whatever you want to call them before you can manipulate it meaningfully at all.
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
I would probably take the middle ground: make each field in the CSV into a separate column in the database, but not worry much about normalization (at least for now). At some point, normalization might become interesting, but with all the data shoved into a single column you're gaining virtually no benefit from using a database at all. You need to separate the data into logical fields/columns/whatever you want to call them before you can manipulate it meaningfully at all.
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
answered Sep 6 '10 at 18:19
Jerry CoffinJerry Coffin
386k50472911
386k50472911
The form contains some more fields, this is only one part of the form (which I did not explain well in the question).
– Mad Scientist
Sep 6 '10 at 18:29
add a comment |
The form contains some more fields, this is only one part of the form (which I did not explain well in the question).
– Mad Scientist
Sep 6 '10 at 18:29
The form contains some more fields, this is only one part of the form (which I did not explain well in the question).
– Mad Scientist
Sep 6 '10 at 18:29
The form contains some more fields, this is only one part of the form (which I did not explain well in the question).
– Mad Scientist
Sep 6 '10 at 18:29
add a comment |
If you have a fixed number of boolean fields, you could use a INT(1) NOT NULL (or BIT NOT NULL if it exists) or CHAR (0) (nullable) for each. You could also use a SET (I forget the exact syntax).
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
add a comment |
If you have a fixed number of boolean fields, you could use a INT(1) NOT NULL (or BIT NOT NULL if it exists) or CHAR (0) (nullable) for each. You could also use a SET (I forget the exact syntax).
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
add a comment |
If you have a fixed number of boolean fields, you could use a INT(1) NOT NULL (or BIT NOT NULL if it exists) or CHAR (0) (nullable) for each. You could also use a SET (I forget the exact syntax).
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
If you have a fixed number of boolean fields, you could use a INT(1) NOT NULL (or BIT NOT NULL if it exists) or CHAR (0) (nullable) for each. You could also use a SET (I forget the exact syntax).
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
answered Dec 1 '18 at 0:20
Solomon UckoSolomon Ucko
7292820
7292820
add a comment |
add a comment |
protected by Stefano Borini Mar 30 '13 at 14:37
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
17
in that case, why bothering database?, saving in a file will do.
– thavan
Feb 12 '13 at 9:31
4
Agreed with @thavan. Why even save the data for a proof of concept? Once you have the proof complete, then add a database correctly. Your fine doing lightweight for proof of concept, just don't make things you have to unmake later.
– Jeff Davis
Aug 9 '13 at 14:10