I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate clients and there design is working fine. But they didn't develop any web based system. But there database design is quite unusual. Let me explain it and then I will post my questions.
So, Now I joined them to develop them a web based ERP (a replica of there desktop system in web). Since I am building the application from scratch, they have given me liberty to revamp any thing which I think would effect positively.
Now the design is,
You have two choices:
Use it, and live with it.
Completely redesign it.
I recommend #1 because #2 will be hard and will be viewed skeptically by your colleagues and boss. Any problems with you progress will be put down to your crazy database design.
The database you describe embodies the classic entity-value-attribute design error. Instead of defining tables modeled on real-world entities in the universe of discourse, and using the DBMS to enforce and infer logical relationships among them, the designers opted to remove all meaning from the database to the application. Entities that should be in the database are constructed in memory using application logic that supplies meaning to
S1 and such. From a database perspective, it's an absolute nightmare.
It's also understandable. EVA designs usually arise where there's little database expertise, and where the problem domain is poorly understood. That adds up to "anything could go in the database", and an EVA design will indeed hold "anything". To the extent your customers determine the actual design -- that is, the supply independent meanings for each database column -- the application acts as a kind of DBMS proxy. The fact that every table has a vast number of unused columns suggests their use may be customer-determined: the customer can "add a column", and the application plucks one from the unused pile. No schema change necessary. It's dynamic!
There's a whole industry based on that idea. For example, the so-called "master data management" tools boil down to an EVA design where the customer designs a database within the application, and application uses the DBMS in much the way you describe.