MySQL Database Design – Storing Recurring Datasets

database-designeavMySQLPHP

I'm trying to store the following arrays into a database, but due to my inexperience, I don't know how to set it up.

It's for a scheduler. A day entry holds three arrays:

  • The 1st array holds the basic entry ['prefix', begin, increment, 'suffix'], that displays:

    'prefix' begin - (begin + increment) 'suffix'

  • The 2nd array holds periodic entries that displays every number. For example, every 5 basic entries, display a new type.

  • The 3rd array holds the days to display on.

There will also be week entries, that can contain up to 7 day entries.

I organized it as the following:

Day entry:

'day' => [
    ['prefix', begin, increment, 'suffix'],
    [
        [every, ['prefix', begin, increment, 'suffix']],
        [every, ['prefix', begin, increment, 'suffix']],
        [every, ['prefix', begin, increment, 'suffix']],
        (Haven't decided on limit)
    ],
    [list of days]
]

I thought of just sticking all of that data into one column as it is, but then I remembered that I also needed to store week entries.

'week' => [
  'day' => [
      ['prefix', begin, increment, 'suffix'],
      [
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          etc...
      ],
      [list of days]
  ],
  'day' => [
      ['prefix', begin, increment, 'suffix'],
      [
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          [every, ['prefix', begin, increment, 'suffix']],
          etc...
      ],
      [list of days]
  ],
  (Possibly 5 more 'days')
]

I'll have an id column and a few other irrelevant columns for each entry, but that's the primary data. I haven't written much code yet, so I'm willing to change anything.

My question is, what's the most maintainable, efficient, etc. way to store this data in a mySQL database?

Edit: The data would look something like this:

'day' => [
    ['Lesson', 1, 2, ''],
    [
        [5, ['Test', 1, 0, '(study)']],
        [10, ['Investigation', 10, 0, '(study)']]
    ],
    [1, 2, 3, 4, 5]
]

And a week entry could hold up to 7 of these day entries.

Best Answer

A database table has only two dimensions: rows and columns. Your JSON-like stuff is not amenable to RDBMS, unless you want to store that hierarchical stuff in a blob that the database engine does not need to look into. When you store a jpeg in a table, all you can do is retrieve the jpeg; you cannot ask the database to look for yellow clowns among all the jpegs.

(Actually there are ways to peek inside JSON and XML, but that is not the "real" way to use a RDBMS.)

Reformulate the data as one or more sets of similar rows and dissimilar columns; then we can talk further.

Related Question