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,
- They have around 150 tables in database.
- Every table has the same schema definition.
- They have divided the fields in three categories.
- Strings (so they assign 50 varchar(250) fields in database).
- DateTime (so they assign 15 smalldatetime fields in database).
- Numeric (so they assign 30 Numeric() fields in database).
- All columns have names as (these names don't terrify the developers, in a week or two they are accustomed and even remembered many of the fields associations):
- Strings (S1, S2, S3, S4 and so on).
- DateTime (D1, D2, D3, D4 and so on).
- Numerics (N1, N2, N3, N4 and so on).
- As I have told you the schema. Every table consist of 95 columns. And only 15-20 columns are actually been used. The remaining 75-80 columns are NULL.
- The tables are well normalized and indexes are maintained.
- Number of rows in most of the tables are less than 1000. Only the transaction table records touches several hundred thousands.
- The precision for numeric columns are by default (1, 0). When any field is selected to be used then the precision is adjusted as per the requirement.
- An empty database is of ~4MB.
- This design makes there development quite easy. Since they have a number of columns and whenever they need a field. They just select the data type, i.e. String or Numeric or DateTime and the next available column is assigned.
I think this information is quite enough. Now I want to ask
- Since, I don't know a lot about SQL. Is this design viable for web environment (web API which will be called from web client as well as from mobile)?
- Since, every table has 75-80 NULL columns. Does they cost us a lot of memory in future, when transaction records will touch millions.
- What are your suggestions to improve this design?