How to save a list in a column of a database table

Question

So, for Mehrdad's answer to the corresponding question , I understand that the “correct” column of the database table does not store the list. Rather, you should create another table that effectively holds the elements of the specified list and then refers to it directly or through the connection table. However, the type of list I want to create will consist of unique elements (as opposed to the related sample fruit question). In addition, the items in my list are clearly sorted - this means that if I saved the items in another table, I would have to sort them every time I access them. Finally, the list is basically atomic in the sense that at any time when I want to access the list, I want to access the entire list, and not just part of it, so it seems silly to issue a database request data collection list.

The AKX solution (linked above) is to serialize the list and store it in a binary column. But it also seems uncomfortable because it means I have to worry about serialization and deserialization.

Is there a better solution? If there is no better solution, then why? It seems that this problem should occur from time to time.

... a little more information so you know where I come from. As soon as I started to understand SQL and databases in general, LINQ to SQL turned me on, and now I'm a little messed up, because I expect to deal with my model of object objects without thinking about how objects are queried or stored in the database .

Thanks everyone!

John

UPDATE: So, in the first flurry of answers that I get, I see that "you can go along the CSV / XML route ... but NOT!". So now I'm looking for an explanation of why. Call me good recommendations.

Also, to better understand what I'm doing: there is a function table in my database that will have a list of (x, y) pairs. (The table will also contain other information that does not matter for our discussion.) I will never need to see part of the list of (x, y) pairs. Rather, I will take them all and close them on the screen. I will allow the user to drag nodes around to sometimes change values ​​or add additional values ​​to the graph.

Answer 1

No, there is no better way to keep a sequence of elements in one column. Relational databases are specifically designed to store one value for each row / column combination. To save more than one value, you must serialize your list into a single value for storage, and then deserialize it when retrieving. There is no other way to do what you are talking about, because what you are talking about is a bad idea, which, as a rule, will never be fulfilled).

I understand that it seems silly to you to create another table to store this list, but this is exactly what relational databases do. You fight a difficult battle and violate one of the most fundamental principles of a relational database for no good reason. Since you are claiming that you are just learning SQL, I would strongly advise you to avoid this idea and adhere to the recommendations recommended by more experienced SQL developers.

The principle that you violate is called the first normal form, which is the first step in normalizing the database.

At the risk of simplifying the situation, database normalization is the process of defining your database based on data so that you can write reasonable, consistent queries against it and be able to maintain it easily. Normalization is designed to limit logical inconsistencies and corruption in your data, and there are many levels. The Wikipedia article on database normalization is actually pretty good.

Basically, the first rule (or form) of normalization states that your table should represent a relation. It means that:

• You should be able to distinguish one row from any other row (in other words, there should be something in the table that can serve as the primary key. This also means that no row should be duplicated.
• Any ordering of data should be determined by data, not by physical ordering of strings (SQL is based on the idea of ​​typing, which means that the only ordering you have to rely on is what you explicitly define in your query)
• Each row / column intersection must contain one and only one value

The last point, obviously, is the highlight here. SQL is designed to store your sets for you, not to provide you with buckets so you can keep the set. Yes it is possible. No, the world will not end. However, you have already learned to understand SQL and the best practices that go with it, immediately switching to using ORM. LINQ to SQL is fantastic, just like graphing calculators. In the same vein, however, they should not be used as a substitute in order to know how these processes work.

Now your list may be completely “atomic”, and this may not change for this project. But you, however, will have the habit of doing similar things in other projects, and you will eventually (most likely quickly) come across a scenario in which you now fit your fast n-easy list in a column when this is completely inappropriate. There is not much extra work to create the right table for what you are trying to save, and you will not be ridiculed by other SQL developers when they see your database design. In addition, LINQ to SQL will see your relationship and automatically provide you with a suitable object-oriented interface. Why don't you give up the convenience ORM offers you so that you can execute a non-standard and illogical hacker database?

Answer 2

You can just forget SQL all together and go with the NoSQL approach. RavenDB , MongoDB and CouchDB to recall as possible solutions. With the NoSQL approach, you are not using a relational model. You are not even attached to schemes.

Answer 3

What I saw a lot of people is this (this may not be the best approach, correct me if I am wrong):

The table that I use in the example is shown below (the table shows the nicknames that you indicated to your specific friends. Each girl has a unique identifier):

nicknames(id,seq_no,names) 

Suppose you want to keep many aliases under an identifier. That is why we have included the seq_no field.

Now fill in these values ​​in the table:

 (1,1,'sweetheart'), (1,2,'pumpkin'), (2,1,'cutie'), (2,2,'cherry pie') 

If you want to find all the names that you gave your friend-girlfriend 1, you can use:

 select names from nicknames where id = 1; 

Answer 4

In addition to what everyone else said, I would suggest that you analyze your approach for longer periods of time than now. The items are currently unique. Currently, using items will require a new list. It is almost necessary that the list is currently small. Despite the fact that I do not have the specifics of a domain, it is not so much to think that these requirements can change. If you serialize your list, you bake in inflexibility, which is not needed in a more normalized design. By the way, this does not necessarily mean much. Many relationships. You can have only one child table with a foreign key for the parent and a character column for the element.

If you still want to go this way of serializing the list, you might consider storing the list in XML. Some databases, such as SQL Server, even have an XML data type. The only reason I propose XML is that, almost by definition, this list should be short. If the list is long, then serializing it as a whole is a terrible approach. If you are following the CSV route, you need to consider the values ​​containing the delimiter, which means that you are forced to use quoted identifiers. Assuming the lists are short, it probably won't make much difference whether you use CSV or XML.

Answer 5

If you need to request a list, save it in a table.

If you always need a list, you can save it as a delimited list in a column. Even so, unless you have VERY special reasons not to store it in the lookup table.

Answer 6

I would just save it as CSV, if these are simple values, then that should be all you need (XML is very verbose and serializing to / from it will probably be redundant, but it will also be an option).

Here's a good answer on how to pull CSV with LINQ.

Answer 7

Only one option is not mentioned in the answers. You can de-normalize your database design. Therefore, you need two tables. One table contains the correct list, one element per row, the other table contains the complete list in one column (for example, divided into coma).

Here is the "traditional" database design:

 List(ListID, ListName) Item(ItemID,ItemName) List_Item(ListID, ItemID, SortOrder) 

Here is a non-normalized table:

 Lists(ListID, ListContent) 

The idea here is that you save the list table using triggers or application code. Each time you modify the contents of List_Item, the corresponding lines in the lists are updated automatically. If you mostly read lists, this might work well. Pros - you can read the lists in one application. Cons - Updates require more time and effort.

Answer 8

If you really want to keep it in a column and query a lot of databases, support XML now. If you are not prompting, you can save them as comma-separated values ​​and parse them with a function when you need to separate them. I agree with everyone, but if you want to use a relational database, most of the normalization is the separation of such data. I am not saying that all data is suitable for a relational database. You can always look at other types of databases if many of your data do not fit the model.

Answer 9

I think that in some cases you can create a “list” of FAKE elements in the database, for example, there are several photos in the product to show your data, you can combine all the identifiers of the images, separated by a comma and a string storage in the database, then you just you need to parse the string when you need it. I am currently working on a website, and I plan to use this method.

Answer 10

The simple answer is: if and only if you are sure that the list will always be used as a list, then connect the list together at its end with a character (for example, "\ 0") that will not be used in the text and stores it. Then, when you extract it, you can divide by "\ 0". Of course, there are other ways to solve this problem, but they depend on the particular database provider.

As an example, you can save JSON in a Postgres database. If your list is text, and you just need a list without the hassle, this is a reasonable compromise.

Others dared to suggest serialization, but I really don't think serialization is a good idea. Part of the neat information about the databases is that several programs written in different languages ​​can talk to each other. And programs serialized using the Java format will not do all this well if the Lisp program wants to download it.

If you need a good way to do such things, arrays or similar types are usually available. Postgres, for example, offers an array as a type and allows you to store an array of text, if that's what you want , and there are similar tricks for MySql and MS SQL using JSON, and IBM DB2 also offers an array type (in their own useful documentation). This would not be so common if it were not necessary.

What you lose by following this path is the concept of a list as a sequence of things in a sequence. At least nominally, databases treat fields as unit values. But if that’s all you want, then you should go for it. This is a value judgment that you must make for yourself.