(mySQL/scip) How to avoid creating new columns in a database when variables have subscripts

Question

Some background information:

We use linear programming (scip/ZIMPL) to solve a optimization problem

As a result variables have subscripts in the mathematical formulation

Currently using mySQL/HeidiSQL for database/interface

Currently we take the variables (constraints) and put them into indexes which are individual columns in a table.

i.e. variable "v" is index01, which refers to timeperiods, index02 refers to centralinventories

However currently only 6 indexes/columns are created - although there are no variables with 6 subscripts (indexes), some do have rows with more than 6 indexes.

Yes we could do a quick fix and double the number of indexes, but that isn't the long term solution

How do we map the the subscripts outputted by zimpl/scip back to the original meaningful variable names without creating a new column for each variable?

Barmar · Accepted Answer · 2014-10-24 14:41:48Z

0

Use a table with separate columns for the variable and the subscript.

CREATE TABLE arrays (
    variable VARCHAR(10),
    index SMALLINT,
    value FLOAT,
    PRIMARY KEY (variable, index));

answered Oct 24, 2014 at 14:41

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

(mySQL/scip) How to avoid creating new columns in a database when variables have subscripts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related