10

During our last weekly meeting, a person that has no background experience in Database Administration brought up this question:

"Would there be a scenario that justifies storing data in-line (string) instead of several lines?"

Let us assume a table called countryStates where we want to store the states of a country; I'll use USA for this example and will not list all the States for the sake of laziness.

There we would have two columns; one called Country and the other called States. As discussed here, and proposed by @srutzky's answer, the PK will be the code defined by ISO 3166-1 alpha-3.

Our table would look like this:

+---------+-----------------------+-------------------------------------------------------+
| Country | States                | StateName                                             |
+---------+-----------------------+-------------------------------------------------------+
| USA     | AL, CA, FL,OH, NY, WY | Alabama, California, Florida, Ohio, New York, Wyoming |
+---------+-----------------------+-------------------------------------------------------+

When asking this same question to a friend developer, he said that from the data traffic size point of view, this might be useful, but not if we need to manipulate this data. In this case there would have to be an intelligence on the application code which could transform this string in a list (let's say that the software that has access to this table needs to create a combo box).

We concluded that this model is not very useful, but I got suspicious that there might be a way to make this useful.

What I'd like to ask is if any of you already saw, heard or done something like this in a way that really works.

  • Now imagine you have a second table, "sales", which has data for every sale that happened along with the state code in which the sale happened. How would you write a query that generates a report with columns (StateName, TotalSalesAmount)? Difficult, right? – zgguy Jan 12 '16 at 19:46
  • Exactly. I also do not agree with this model. We get stuck at any point that we need to recover any type of data ( or useful data if you will). – Human_AfterAll Jan 12 '16 at 19:47
  • A possible scenario could be to store variables. Store a;b;c, use the front end to parse your string you then get a, b, c and carry on execution doing something with them, maybe?. Feel it might suit some sort of specific need in that fashion... On second thought, no. You could always store IDs, Join your tables and create a concatenated string than can send content to the FE... – Nelz Jan 12 '16 at 19:53
  • To be fair (to me, at least ;-), I proposed using the 2-character country codes :-) in that other answer. – Solomon Rutzky Jan 12 '16 at 20:47
  • 2
    Notice that nobody has qualms about storing the value "Alabama" in a column rather than having a separate table with columns STATE, N & C for "state STATE's name has Nth character C". Because either 1. we don't intend to query about characters of names or 2. we don't mind calling a function NTH_CHAR(N,S) returning "the Nth character of string S" on every row with a name if we do. (Vs JOIN & other relational operators eliminating some such rows via the extra table.) Ditto for integers and NTH_DIGIT(N,I). It is always a judgement call as to what in a particular database is relationally atomic. – philipxy Jan 12 '16 at 22:20
12

To start with, the current Question title referring to "storing data as string instead of columns" is a little confusing. When speaking of storing data as strings instead of something else, that usually refers to serializing everything to a string format instead of a proper / strong datatype (e.g. INT or DATETIME). But if asking about storing data as multiple values in a single field as opposed to separate rows, that is a bit different. And to be fair, while concatenating values is most easily done with strings, it can also be done with INT and BINARY types as well, either by bit-masking or similarly reserving certain positions to have different meanings. Since the second interpretation is what is actually being asked about, based on the text of the Question, let's address that.

In a word: No. If you are storing actual data points then it will only bring pain (in terms of code and performance) as it is unnecessary complication. If it is a value that will only ever be stored as a single unit, updated as a single unit, and never disassembled within the database, then that could be ok as it is roughly analogous to storing an image or PDF. Otherwise, any attempt to parse the data will invalidate using any indexes (e.g. using LIKE '%something%', or CHARINDEX, or PATINDEX, or SUBSTRING, etc).

If you need to store separate values in a single field of a single row then there are more appropriate means of doing that: XML or JSON. These are parseable formats (XML / JSON) and XML can even be indexed. But ideally this data would be stored in properly-typed fields so that it can be truly useful.

And please do not forgot that the purpose of an RDBMS is to store data such that it can be retrieved and manipulated as efficiently as possible, within the constraints imposed by being ACID-compliant. Retrieving concatenated values is bad enough due to the need to parse the values first, and that is not indexable. But manipulating often means replacing the entire blob just to update a part of it (assuming that no pattern exists to use with a REPLACE function). The XML datatype at least allows for XML DML for simplistic updates, though those are still not as fast as a simple update of properly modeled data.

Also, given a scenario such as what is shown in the Question above, by concatenating all of the StateCodes together, you would be unable to Foreign Key (in either direction) those values.

And what if the business requirements change over time and you need to track additional properties of these items? In terms of "states", what about the capitals, or population, or a sort-order, or anything else? Stored properly as rows you can add more columns for additional properties. Sure, you can have multiple levels of parsable data, such as |StateCode,Capital,Population |StateCode,Capital,Populate|... but hopefully anyone can see the problem growing exponentially out of control. Of course, this particular issue is rather easily dealt with the XML and JSON formats, and that is their value as mentioned above. But you would still need a very good reason for using either of those as an initial means of modeling as neither will ever be as efficient as using discrete fields in separate rows.

9

I have actually used something like that for a very limited purpose. We created a table of headers for output files. They were specifically constructed and were mostly just the column headings but not quite. So the data looked something like

OutputType   OutputHeader
PersonalData Name|Address|City|State|Zip
JobInfo      Name|JobName|JobTitle

Essentially it looked like it was a delimited list. And in one way it was. But for our purposes it was a single long string.

That's the trick here. If you never plan on parsing the list then it's worth saving the list. If however you will or even might need to parse the list then it's worth the extra space & time to split it out and save it in separate rows.

1

I've used it once with a rather small table, for example:

CREATE TABLE t1 (
  ID number,
  some_feature   varchar2(100),
  valid_channels  varchar2(100));

CREATE TABLE channel_def (
  channel varchar2(100));

And then store values CRM,SMS,SELF-CARE into valid_channel.

The entire table has something like 10 records. valid_channel contains values that should actually be in a linking table that depicts the many-to-many relationship. Table t1 isn't going to be used intensively so we just decided to go down this road. Some politics was involved in this decision, though (see below).

But in general I avoid it, it's not 3NF.

The place where I work at currently has dozens of such columns all over the place. Their justification is that it makes their queries easier: instead of joining three tables using the linking table they can go straight for the definition table using LIKE. E.g.

SELECT * 
  FROM t1 
 INNER JOIN channel_def cd
    ON ','||t1.valid_channels||',' LIKE '%,'||cd.channel||',%';

Horrible + on Oracle it disables the use of index because of starting '%,'.

  • Which would be slower: LIKE or a simple join? – Human_AfterAll Jan 13 '16 at 10:58
  • It's best to have a join on a column that is indexed or at least has a referential constraint (FK) on it. Additionally, joins are usually done on a PK of the other table, which is indexed by default (at least on Oracle). If you're asking about the particular case at hand (see above), the execution plan would most likely say it was the same, since it was a small table. – Robotron Jan 13 '16 at 11:21
  • @Human_AfterAll the LIKE would be slower, especially if the data is properly modeled to be using a TINYINT PK field in channel_def. Then it only needs to compare a single byte between the two tables. Here it has to parse the string, character by character (at least until the condition is satisfied), and it is doing a case-insensitive search (based on the given table def not showing a _BIN2 collation being used). This does invalidate indexes on SQL Server as well. I addressed this in my answer by saying that parsing can't use indexes. I just updated my answer to make it clearer. – Solomon Rutzky Jan 13 '16 at 15:27
  • 1
    @Human_AfterAll I would say that this modeling decision was borne out of a lack of experience and knowledge (and sometimes laziness). One additional JOIN is all that is saved, but what is sacrificed is the ability to Foreign Key which would prevent entirely bogus data from getting in (even if it wouldn't match the LIKE clause and produce odd results, it can still cause other issues or at least make debugging harder/longer). It also makes updating the valid_channels field more complicated. This is not to say that this doesn't work, there is just no good reason for doing it. – Solomon Rutzky Jan 13 '16 at 15:34
  • "lack of experience" - what's worst is that this particular design decision was imposed by a senior staff member... – Robotron Jan 13 '16 at 15:45
1

This was done here on SE. As Marc Gravell writes:

... After some thought and consideration, we settled on a pipe (bar) delimited natural representation, with leading/trailing pipes, so “.net c#” becomes simply “|.net|c#|”. This has virtues:

  • very simple to parse
  • bulk update and removal of tags can be done with a simple replace (including the pipes, to avoid replacing mid-tag matches)
  • ...

This "new format" was next step from the "old format" which was a bit different and was chosen to make use of SQL Server Full-Text Search feature, so some of the benefits are not relevant if you do it from scratch.

They presumably did not fully normalize the thing for both amount of work and performance reasons.

0

Well, one possible primary benefit of using strings, and other data types, is send them from SQL Server to C#,C,C++ (etc) using the SQLCLR when sheer performance maybe needed. You could even create a view or stored procedure to represent relational data non-relationally--as you've with your example above for this very purpose.

See this example:

http://aboutsqlserver.com/2013/07/22/clr-vs-t-sql-performance-considerations/

per Wikipedia: SQL CLR or SQLCLR (SQL Common Language Runtime) is technology for hosting of the Microsoft .NET common language runtime engine within SQL Server. The SQLCLR allows managed code to be hosted by, and run in, the Microsoft SQL Server environment.

  • 2
    Hi there. Can you please give more detail here. I am unsure of how this is a benefit of storing data in non-traditional ways. If anything, it is a benefit of SQLCLR to be able to better deal with alternate data formats if those must exist. But that is not a reason to prefer an alternate data format. As such, I really don't think this answers the question. – Solomon Rutzky Jan 12 '16 at 22:50
  • The article link explains the benefits with the pros and cons. Also, I mentioned storing the data relationally, and for purposes of the CLR converting it to non-relational with a view, or stored procedure. Your question was "Would there be a scenario that justifies storing data in-line (string) instead of several lines?" And my answer was yes, though I prefer a view or stored procedure for purposes of interacting with the CLR. – Sting Jan 13 '16 at 13:13
0

In my view, the answer would be no. I've not used this approach and would avoid it - I can't think of a reason why I'd go down that route. You're leaning towards the world of JSON/NoSQL with an array.

We had similar design choices in a previous role whereby the architect team wanted to have have a "Data" field which was delimited and then converted to binary. We didn't go down that route in the end for a few reasons.

If you had to join to this type of data, it would be one ugly experience. Updating single elements of the string would also be unpleasant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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