Original Post
Hi, A MySQL question for y'all - hope it's the right forum! As part of my learning C++ process I wrote a program for my wife's cake decorating business - just a simple Win32 frontend for ingredients, recipes, designs etc. I did all the data storage using plain ASCII files. Now I'm updating, using C#, and MySQL for the data storage (it's always more fun to learn two things at once!). One of the things I have is a conversion table - that is, a square matrix of weights / volumes etc. For example, it knows that one ounce is 28.4 grams (and vice versa). Then, when the wife designs a cake that's an amalgamation of several different recipes (each of which probably has the same ingredients in different units), everything is converted to one constant unit for the purposes of pricing and shopping. Hope that's clear (although it's not 100% required for understanding the problem). OK - so I have a square matrix of conversions - but the problem is, this is somewhat dynamic (you'd be surprised at the number of ways of representing amounts people can think up). Hence, when a new unit is added, the matrix expands in dimensionality across both axes. Doing all this is no problem, and I have a nice little algorithm that auto-updates all conversions on the basis of one specified conversion to an old unit - but I need to think about how to represent this in the MySQL database. Two options spring immediately to mind. a) Use something like: alter table conversion add column ToSomething details; Problem : unique identifier column names (although this might be possible). b) Store the SQL table as 3 columns : unit from, unit to, ratio. Then just pack and unpack the dataset as it's read in and out of SQL. So - questions : are there any differences / problems with either of these approaches in MySQL? Is there another alternative? Does MySQL have a dynamic matrix type dataset that I could use by default? Any other thoughts? Thanks in advance, Jim.