What's the best way to store an array in a relational database?

Question

I'm currently trying to design a database and I'm not too sure about the best way to approach a dynamically sized array field of one of my objects. My first thought is to use a column in my object to store an array of integers. However the more I read, the more I think this isn't the best option. Concrete example wise, I have a player object that stores 0 to many items, which are represented by an integer. What is the best way to represent this?

Answer 1

If that collection of values is atomic, store them together. Meaning, if you always care about the entire group, if you never search for nested values and never sort by nested values, then they should be stored together as a single field value.

If not, they should be stored in a separate table, each value bring a row , each assigned the parent ID (foreign key) of a record on the other table that "owns" them as a group.

For example, a clump of readings from a scientific instrument that are only ever used together as a collection for analysis should be stored together in a field. In contrast, a list of phone numbers for a customer that may often need to be queried for an individual number should probably be broken up into single phone number per row in a related child table.

For more info, search on the term "database normalization".

Some databases, support an array as a data type. For example, Postgres allows you to define a column as a one-dimension array, or even a two dimension array.

If your database does not support array as a type of column definition, then you may have three alternatives:

• XML/JSON
  Transform you data collection into an XML or JSON document if your database your database supports that type. For example, Postgres has basic support for storing, retrieving, and non-indexed searching of XML using XPath. And Postgres offers excellent industry-leading support for JSON as a data type including indexed support on nested values with its jsonb data type where incoming JSON is parsed and stored in an internally-defined binary format. This feature addresses one of the main reasons people consider using the so-called “NoSQL” systems, looking to store and search semi-structured data.
• Text
  Create a string representation of your data to store as text.
• BLOB
  Create a binary value to store as a binary large object (BLOB).