Storing attributes with multiple integer values

Question

I need to store a dynamic number of integer attributes (1-8). I'm storing them in individual columns in the database table, like:

attribute_1, attribute_2, ..., attribute_8

This makes for a fairly messy model when methods need to reference these, as well as an unwieldy database table and schema.

These are assigned default values (but are overridable on a form), and represent unique identifiers for the user.

For instance, a Brew is composed of up to eight batches before they are mixed together in a fermenter. The brewer might want to go back and refer to any one of these by its unique identifying number. I'm assigning these unique values based on the last highest value when creating a new Brew. However, the user may want to override these default values to some other numbers.

In most cases (smaller breweries), they'll probably only use the first two, but some larger breweries would use all eight.

There must be a better way to store these than having eight different attributes with the same name and a number at the end.

I'm using MySQL. Is there an easy/concise way to store an array or a JSON hash but still be able to edit these values on a form?

Answer 1

I would not store attributes like that. It will limit you in the future. Let say you want to know which brews have used attribute_4? You will have to scan the entire brews table, open the attributes field and deconstruct it to see if 4 is in there.

Much better to separate Brew and Attributes in two tables, and link them, like so:

Another benefit, is you can add attributes easily.

Storing JSON is ok, like @max pointed out. I just propose the normalized database way of doing it.