1

If one account has multiple values that represent the same variable, how would I go about storing both variables? The number of values could range from one to several hundred. I would think arrays or lists would do the trick or something similar. I've looked through several forums but most of which are old and seem to offer workarounds.

Example:

input1 = 5
input2 = 9
structure -> |int|string|,
structure -> |{5,9}|string|
sellc
  • 380
  • 1
  • 5
  • 19
  • 1
    Best way is to replace your column by a table with a foreign key to the first. – Calimero Sep 07 '17 at 15:40
  • 9
    Just don't... storing multiple values in a database column always causes masses of problems; normalize your database structure to handle this as a 1-many relation – Mark Baker Sep 07 '17 at 15:40
  • 1
    I'm not sure if you're asking what I think you are, but take a look at this just in case: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Don't Panic Sep 07 '17 at 15:40
  • 2
    *"I've looked through several forums but most of which are old..."* ... we're talking data normalisation here, this is [not a new concept](https://computing.derby.ac.uk/c/codds-twelve-rules/). – CD001 Sep 07 '17 at 15:47
  • 1
    The accepted response in that link gave a nice explanation and helped clear up some of my confusion. @Don'tPanic – sellc Sep 07 '17 at 16:05
  • @csell It's one of my favorite answers. I like to point it out to people. – Don't Panic Sep 07 '17 at 16:12

1 Answers1

2

My first suggestion would be to normalize these values into another table and use foreign keys. This allows you the most flexibility for future queries that you may need.

If you really want to push through and use the same column, you can use MySQL's JSON Column Type.

Noel Llevares
  • 15,018
  • 3
  • 57
  • 81
  • 2
    If you're not using the data in a relational way, JSON is not a bad call. If you're storing data in there that *is* relational this is asking for a world of hurt. – tadman Sep 07 '17 at 15:49