mysql: using one field to contain many "fields" to save on fields

Question

Hi

I have a project which needs an Excel GUI (client's request) with a backend mysql db/table requiring almost 90 fields. (almost 60 fields are duplications of 6 fields.)

After giving it some thought, I ended up creating a table with 11 fields: 10 searcheable fields, and one big field which can contain up to 60 fields "together", separated by ":"

So a record on that big field would be look something like this:

charge1:100:200:200::usd:charge2:1000:2000:2000::usd:charge3:150:200:200:250:USD, and so on

As you can see, these are blocks of 6 fields and can be up 10 of these "blocks", but never more than 255 characters altogether.

None of these "fields" need to to be indexed nor searched for (that's done on the other 10 fields)

What I am doing is "SELECT *" query (with an Excel GUI) of the 11 fields and then (with VBA) I separate these values to columns (this takes less than 1 second). With VBA I display the data on certain fields within the Excel "form".

This is working fine and I am very happy with the results, as I was looking for a light, simple and super fast solution, and it is.

Is there a "technical" reason for not doing this ?

Perhaps fields with too many characters might give problems ????

I understand there are many ways of handling this, however this is a small project and I am looking for a simple solution that works, not a complex one (with too many tables and/or fields)

Since the GUI is an excel interface I don't want to make it too complex if there isn't need for that.

Thanks in advance for your input.

Answer 1

I think you already have a pretty good idea of problems that may arise.

Indexing doesn't work real good on those fields, updating and reading individual values requires extra work in your application.

Also, you're storing what looks mostly like numbers in a string-type column, so that means some extra storage space (though you'd have to weigh that against a bit of overhead for separate columns).

It might turn into a nightmare when the structure of those columns changes.

All of that might be manageable effort for you, but it's entirely possible that the dev after you will hate you. :p