1

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.

  • 1
    Could you add some actual examples of the data you are expecting to store in the tables? You are still discussing at a very abstract level. (IMHO). Thanks. – hot2use Jul 25 '17 at 5:42
  • @hot2use Is my edit sufficient? – Chris Happy Jul 25 '17 at 15:27
  • You want to store structured data within a relational table. If you are always going to use exactly the same structure, I'd recommend you normalize your design into atomic pieces of data. If the structure of the data you want to store is not always the same, I'd recommend storing it as JSON. You'll probably need the newest versions of MySQL, and use json_encode on the PHP side. – joanolo Jul 26 '17 at 0:09
  • @joanolo Thanks for your reply! The output isn't created yet, so I can change it if when necessary. Could you give me an example? I trying to understand the terms, but having a hard time... Thanks again! – Chris Happy Jul 26 '17 at 0:52
1

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.

  • Just fyi, all of this is specific to MySQL. And, peeking inside of JSON and XML is actually a pretty core part of the SQL 2011/2016 spec. MySQL, though not Maria, even provides a binary json format. – Evan Carroll Jul 26 '17 at 1:01
  • Yeah, somethings in the Standard need a few more years to become efficient enough to be worth doing. MariaDB has long had a similar Dynamic columns, and now has JSON (not identical to MySQL). – Rick James Jul 26 '17 at 1:14
  • PostgreSQL has had a binary json format since 2014. It's pretty efficient. – Evan Carroll Jul 26 '17 at 1:19
  • @EvanCarroll - Do you happen to know how performance compares, at scale, to having a 'real' column instead? I'm thinking of filtering (WHERE), sorting (ORDER BY), etc. Can postgres index a field in JSON? Is the index "sparse"? (I suspect MySQL and MariaDB don't do well at scale.) – Rick James Jul 26 '17 at 1:57
  • This is a little embarrassing, but by reading your answer, it clicked. I remembered that I'm going to have to use all of the data every time I fetch it, so I might as well store it all in one column. – Chris Happy Jul 26 '17 at 2:34
0

Your best method to create a scheduler is to use something like the well established cron format. If this works it'll keep your tasks simple.

CREATE TABLE tasks ( pkid int AUTO_INCREMENT, ... );

CREATE TABLE crontab (
  minutes   text,
  hours     text,
  dom       text,
  month     text,
  dow       text,
  year      text,
  fkey_task int REFERENCES tasks.pkid
);

The notation generally works well enough for all sub-minute schedulers. Now you just need a library to manage the scheduler's event loop.

  • Thanks for your answer! However, since it's not time sensitive, I'm just going to place it all into a blob :) – Chris Happy Jul 26 '17 at 2:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.