0

We are actually a group of students developing an internal application for a company, and we need to change SQL data everyweek.

We are planning to use Symfony console command made by ourself (so in PHP) that will be executed by a windows task everyweek. The main problem is to know how we could handle a system like this in our database.

There would be the actual value (1, 2 or 3) , and the cycle for each user to store (cycles don't have a fixed length, but the maximum should be 4), but we think this is too big to store this type of data.

We were thinking about something like this for an example :

╔═════════╦══════════════╦═════════╗
║ id_user ║ actual_value ║  cycle  ║
╠═════════╬══════════════╬═════════╣
║       1 ║            1 ║ [1,2,3] ║
║       2 ║            2 ║ [2,3,1] ║
║       3 ║            2 ║ [1,2]   ║
╚═════════╩══════════════╩═════════╝

After the script has been runed, we should get this :

╔═════════╦══════════════╦═════════╗
║ id_user ║ actual_value ║ cycle   ║
╠═════════╬══════════════╬═════════╣
║       1 ║            2 ║ [1,2,3] ║
║       2 ║            3 ║ [2,3,1] ║
║       3 ║            1 ║ [1,2]   ║
╚═════════╩══════════════╩═════════╝

Are we going in the right directions ? Is there a more efficient/easiest way to do this type of thing ? This is actually prototyping, so if you have other table schema in mind that could be better, we are listening to any suggestions.

Trichard
  • 1
  • Don't really see any issues with this approach. Being a RDBMS some would argue the `cycle` data should be broken out into its own table `id_user, cycle_value` with one row per `cycle` a user has. Normalization – ficuscr Feb 04 '19 at 22:40
  • If you want to ease the coding I'd have a separate table with the cycle including cycle position and cycle value. That would make it simple to increment to the next value in the cycle. Not that you couldn't do it in a string like you have, but that's also likely to be prone to error if something other than the expected string ends up in there. If its table then you know it can only contain integers. – TomC Feb 04 '19 at 22:52
  • Any time you find yourself putting comma-separated values in a SQL table, think again. https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Barmar Feb 04 '19 at 23:13
  • And the square brackets around them just makes things even harder, unless you're using the JSON functions to parse it. – Barmar Feb 04 '19 at 23:14
  • @ficuscr This seems to be a good idea to do it in another table. We are using MySQL, could it be interesting to use a datatype like SET or JSON ? – Trichard Feb 04 '19 at 23:17
  • @Barman You think we should make 4 fields and put value in it if we want to ? We could have something like this : `CREATE TABLE 'cycles' ( 'id' INT NOT NULL AUTO_INCREMENT , 'actual_position' INT NOT NULL , 'first_entry' INT NOT NULL , 'second_entry' INT NULL , 'third_entry' INT NULL , 'fourth_entry' INT NULL , PRIMARY KEY ('id')) ENGINE = MyISAM;` The id of this table could be stored in the user table – Trichard Feb 04 '19 at 23:23
  • I'd use a bitmask before an ENUM or SET. Those usually indicate bad design. Might never be an issue if the data don't grow or you don't have to query much against it. "Right" answer though is to probably add the second table. – ficuscr Feb 04 '19 at 23:27
  • Do you really even need multiple values? Are the values always consecutive numbers up to a limit? Maybe you could just use a `current_value` and `max_value` column. You increment `currnt_value`, and go back to `1` after `max_value`. – Barmar Feb 05 '19 at 00:34
  • @Trichard Your idea of having a Cycles table might work if you have a fixed set of reusable cycles. You could also simplify coding if you added another column that included "NextPosition" so you can always look up the current value, and know what to set the next value to. Have to make sure the update of this structure is always clean though. I'd always caution about assumptions like the fact there can only ever be 4 entries - thats a recipe for disaster. If you just had (cycleid, currentvalue, nextvalue) then you would have it all covered, with each user pointing at a (cycleid,currentvalue) fk – TomC Feb 05 '19 at 01:10
  • When using Symfony with Doctrine you can use the `json` type for the cycle. When your database natively supports JSON, e.g. MySQL 5.7+, Doctrine will even use that field type in your database, otherwise it will do a `json_encode()` before storing the data in a string-field. Doctrine will also take care of "unpacking" the value from the database and restoring it as a list in your PHP code. I would prefer this over a cycle table. If you need the information what value was set on each cycle then I would consider looking into a concept called Event Sourcing. – dbrumann Feb 05 '19 at 04:23
  • @Barmar This can't work, because values are not consecutive. – Trichard Feb 05 '19 at 08:46
  • @Trichard That's why I asked. It would be nice if the example data didn't make it look like it is. – Barmar Feb 05 '19 at 17:04

0 Answers0