Creating mathematical model from table of permutations?

[jjc]

I am trying to create a mathematical model from a table of possible permutations. The table essentially consists of a list of various combinations of variables (there are 7 of them) and then an education guesstimate of how long that combination would take. Each variable is restricted to a limited set of possibilities, usually 3 or 4. The variables represent things like, how experienced is this person? = [Beginner, Moderate, Advanced, Expert]. So the variables aren't necessarily hard numbers.Here the actual set that I am working with:

A1 B1 C1 D1 E1 F1 G1 12
A1 B2 C1 D2 E2 F1 G1 88
A1 B2 C1 D3 E2 F2 G1 200
A1 B2 C1 D3 E2 F1 G1 160
A1 B2 C1 D3 E3 F2 G1 240
A1 B2 C1 D3 E3 F1 G1 200
A1 B2 C1 D4 E2 F1 G1 72
A2 B3 C2 D5 E2 F3 G2 72
A2 B3 C3 D6 E2 F3 G2 120
A2 B3 C4 D5 E2 F3 G3 96
A2 B3 C5 D6 E3 F3 G2 96
A2 B3 C5 D3 E2 F3 G2 72
A2 B3 C6 D5 E2 F3 G2 60
A2 B3 C6 D6 E2 F3 G2 80
A2 B3 C6 D7 E2 F3 G2 40
A2 B4 C6 D5 E4 F3 G2 24
A2 B4 C6 D6 E4 F3 G2 32
A2 B4 C6 D7 E4 F3 G2 16
A2 B5 C2 D5 E2 F3 G2 56
A2 B5 C3 D6 E2 F3 G2 64
A2 B5 C4 D5 E3 F3 G3 72
A2 B5 C5 D6 E3 F3 G2 96
A2 B5 C5 D3 E2 F3 G2 72
A2 B5 C6 D5 E5 F3 G2 40
A2 B5 C6 D6 E2 F3 G2 56
A2 B5 C6 D6 E3 F3 G2 96
A2 B5 C6 D7 E6 F3 G2 16
A2 B5 C6 D7 E8 F3 G2 32
A2 B5 C6 D8 E7 F3 G2 24
A3 B6 C6 D9 E4 F3 G4 4
A3 B6 C6 D10 E4 F3 G4 8
A3 B6 C6 D11 E4 F3 G4 12
A3 B4 C6 D9 E4 F3 G4 2
A3 B4 C6 D10 E4 F3 G4 4
A3 B4 C6 D11 E4 F3 G4 8

Each column is ostensibly 'added' to the next column, with the number being the RHS of the equals sign.

The table consists of 34 permutations. The guesstimated total is based on past data, but isn't a hard and fast mathematical correlation. And to top it all off, certain combinations are excluded; i.e. not all permutations are possible. E.g. one of the variables is 'location'. It has possible values of 'inside' and 'outside'. If it is 'outside' then the variable for 'needed equipment' is restricted (because certain pieces of equipment can't go outside).

This is all currently defined in a spreadsheet with conditional pick lists in each of 7 columns. I want to move it into a database system, and am trying to create the background model to represent this table. Something that is a bit more flexible and not just a strict lookup. (I want to do it better than the spreadsheet did.

So...I think that I can work through and try to find some specific values for each variable just using the simultaneous equations. The issue comes up in trying to represent the restrictions and the weighting of certain variables (e.g. an experienced person might make 2 or 3 other variables take less time).

Restrictions might have to just be boiled down to programming logic during data entry, I suppose. Maybe weighting isn't that important and it would all work out in the equation solving and be sufficiently accurate. This model is just a design guideline and not meant for anything that needs to be TERRIBLY exact. :)

Any suggestions are welcome.

Thanks,
J

 

[jjc]

Hmmm...that's not going to work, is it? Each instance of a variable is itself, really, a new variable. They aren't coefficients in front of the same variable, necessarily. So simultaneous equations doesn't really apply, it seems.

So now I am thinking I need to figure out how much a transition from one to another changes the result, and track that in some way...

 

[Number Nine]

This strikes me almost as a statistical problem; you have a group of categorical variables predicting a continuous variable. On it's face, this problem would warrant something like analysis of variance, but the fact that only certain combinations are allowed makes it a bit trickier.

 

[Stephen Tashi]

trying to create the background model to represent this table

Explain exactly what this means. Can the model be written in a general purpose programming language? - or is the form of the model limited by the database software? Or are you talking about a single mathematical expression that defines something like a polynomial in all those variables?

The study of databases is a field in computer science and it has its own terminology - for all I know a "model" for a database structure might be a technical term.

 

[jjc]

@Number Nine: Yes, I was sort of wondering the same thing, if this might be more a statistical question. (Or at least I was after I realized my error in my first conception of trying to solve with simultaneous equations.)

@Stephen: I wasn't implying 'model' in DB specific terms. Our system (FileMaker) has a built in scripting system, but can still be fairly flexible in terms of defining things.

I am trying to achieve some granularity on each term, so that they can be simply added up to reach a final number, or that I can represent the results of the table in a single formula type thing. They will have some affect on each other though, so a polynomial may be what it breaks down to. I was just trying to avoid recreating the look up table (that table of variables/results that I posted). I want to take the table and create a formula or be able to define individual values for each variable to calculate results. This would make it more flexible and usable if things change in the future.

- J

 

[Stephen Tashi]

I'm still confused about what the model does. I don't see how it is used.

For databases, we have the problem of data entry, data retrieval. For the data itself we have various problems of prediction like "for a randomly selected entry, if A = 2 and B = 4, what is the probability that C = 6".

I don't know which type of problem the model is supposed to address. I don't know what achieving "granuality" means - I assume a "term" means a category like 'A', 'B',...

 

[jjc]

By granularity I am trying to go from needing an entire matching line to find a resulting number, e.g: A2 B5 C5 D6 E3 F3 G2 = 96

to a system where each individual variable contributes it's particular specific share. In the case above, trying to define A2 = 13, B5 = 10, etc. (Just pulling numbers out of the air.) Hopefully, then, a new combination can be used that wasn't in the original table. And also, hopefullly, I wouldn't have to recreate the entire logic of the table in the programming.

And, it may be that a look up table is really the best route.

Yes, by '...granularity of each term...' I was referring to each category of variables (i.e. A1, A2, A3...).

 

[Number Nine]

What, specifically, do the variables mean?

 

[jjc]

They are more like descriptions of states. As originally mentioned, they are things like Experience (low, med, high), location (in, out), time (1-2 hrs, 3-4 hrs, ...), equipment (none, some, lots), computer work (none, some, lots), etc.

Yes, you asked specifically, but that is what you get. :) Does something more specific matter?

- J

 

[Stephen Tashi]

By granularity I am trying to go from needing an entire matching line to find a resulting number, e.g: A2 B5 C5 D6 E3 F3 G2 = 96

So this has to do with data retrieval? What kind of query do you hope to make that will retrieve a line?

to a system where each individual variable contributes it's particular specific share. In the case above, trying to define A2 = 13, B5 = 10, etc. (Just pulling numbers out of the air.) Hopefully, then, a new combination can be used that wasn't in the original table.

I don't understand that passage at all.

Yes, by '...granularity of each term...' I was referring to each category of variables (i.e. A1, A2, A3...).

I thought the categories were 'A', 'B', 'C' etc. and that the numbers were answers for the categories, so A2 meant A = 2.