OK so yes originally this was based on spreadsheets (before my time) and i dont want to use an EAV but I lack design skills which is why im asking here for any sort of advice.
The industry im dealing with is mining and no mine is the same. The ColXX are able to be mapped by the user via a mapping table to describe what they are. So for one project Col1 may be Iron grade (iron ore mine) or Gold grade (gold mine) or any other sort of metal you can think of based on mining. Also, each type of mine can have different numbers of ColXX. I would love to be able to use IronGrade, GoldGrade as a column name but since they will never be related im unsure on how to go about doing this without using either dynamic sql or customising code for each mine.
The table i posted with ColXX is part of the current structure - the new structure will be using different data types. This is one thing I believe I can do after working with the data for the past few weeks 🙂
you'll end up devolving your codebase doing backflips with dynamic SQL
The system is currently purely dynamic with columns and tables being passed around 🙁 I would do anything to stop this as its extremely difficult to maintain.
If anything else needs clarifying please let me know and i will do my best to provide more information.