Multi-data in Salmon tables

Why does Salmon support multiple data in a single DB table column record?

Traditionally, database administrators don't like it. Some will even call it a DB heresy. However, for programmers it's often a performance and application logic necessity.

Multi-data is kind of having several properties enumerated into a single one. Common example is a form with 3 choices to be unchecked or checked. The DBA approach is having 3 different columns for each choice, each with value let say as 'on' or 'off'. However, there are application scenarios where the programmer would prefer to have it all in one column, such as 'on;off;on'.

In the example above, we said 3 choices. What about if there were 30, 60, even more? That would make the table pretty large, which would lead us to use a separate table for the choices. Now, let say the choices themselves have properties, that would become easily messy. Multi-data is much straight forward.

Using multi-data with large scale applications.

Another example is users and usersgroups, membership relations. Let say we have 1000 groups, about 100K users, and users can be members of mutiple groups with an average of being member of 10 to 100 groups. Using the DBA approach, we would have a row entry for each user membership to a group, that is 1M to 10M entries.

Now lets take this to a more realistic example of what a large scale application would be, in the magnitude of 10K groups and 1M users. Bingo, you are now searching within 10M to 100M entries. This is potentially creating DB bottleneck, wasting computing resources, and at least so redundant. This is where multi-data would save you much pain.

Using multi-data, we would just load the single enumeration of the user's membership to groups into a single column, which don't even has to be in a separate table. For example, '10;21;302;48;52' each number being representing a group ID.

Important! Use of separators.

One of the reasons DBA often cite against multi-data in a single column is that it makes searching impossible. However, this is not strictly true, especially if properly using separators.

Because we need to keep the ability to somewhat search and select specific data though SQL queries, the Salmon multi-data approach is to prefix and postfix the data with a chosen separator. In instance ';on;off;on;' instead of 'on;off;on', ';10;21;302;48;52;' instead of '10;21;302;48;52'. That way, we can make a query such as "where column like '%;10;%' " .

Considering that nowadays web applications intensively rely on client-side application data processing, multi-data works a bit like a searchable pre-feetched query result.