SQL Multiple data stored and separated by 'char' is ok?

Question

I have an application that writes a list of values each separated by a specific character to a database field.

There is a table named orders with a field called product-list and an other one named date, these last two fields accept a list of values separated by a - like in this example: value-value-value.

Is there a technique or a format to save more efficiently a list of products, not just as a string separated by a special character, because I feel this constraints me a lot when making new changes to the code and when dealing with long list of products.

For example the product list-size is limited to one field of the database and since I correlate different values of different sizes, like a list of ids with a list of dates, I need to work around formating the date strings in a minized way so that sizes are similar and there is no errors when writing a long list in the order. id-id-id is smaller than dateformat-dateformat-dateformat so for a long list I need to make sure date-format doesn't exceed 255 chars.

This method translates to saving a list of x parameters depending on how you format your strings inside the list and to add extra columns to the database if you want to save more values.

How can this be scaled up? Is the most efficient solution to add extra fields(columns) in the database for the exceeding values? Is it the way the data is being saved? Im using php-c# api sending json strings.

Please let me know if I'm being clear in my question, I'm not that experienced, just want to be a better programmer.

Answer 1

Just don't do that. Storing delimited lists in a relational database is a typical SQL antipattern, that violates basic normalization rules, and should always be avoided.

Not only the length of the string might not fit in the column - this will hurt you in many other ways:

• referential integrity cannot be efficiently enforced - same goes for uniqueness

• it is hard to search the list

• it is hard to remove elements from the list

• it is hard to order the list

• and many more...

Instead, you should have a separate table to represent the many-to-many relationship between orders and products, say order_products, where each (order_id, product_id) tuple is stored on a different row.