What is the best approach for database design with lots of columns?

Question

I am writing a query based financial application. It lets the user to write complicated equations (much like WHERE part of an SQL query) and find companies matching those criteria.

For the above, I currently have more than 500 columns in the database table (each column representing a financial field).

Example of Columns are: company_name, sales_annual_00, sales_annual_01, sales_annual_02, sales_annual_03, sales_annual_04, protit_annual_00, profit_annual1...(over 500 such columns).

The number of rows is around 5000.

Going forward, I would like to further increase the number of columns/financial-fields.

For the above I would like to get help regarding:

1) What is the best database design approach? Is it ok to have these many number of columns?

2) How can it be normalized? (User can use any of these fields in search criteria).

3) Is it ok to stick with MySQL, or modern document based databases like MongoDB should be better for it?

P.S. (Update): I have been using MySQL till now and a running example of the usage is at: http://screener.in/companies/89/Formula-- In above there around 500 fields/columns to create your query on, however, I seek to increase that number to much more in future.

Answer 1

If this site is going to be used for ad-hoc reporting, and you anticipate a large number of rows, you should design the database as a Data Warehouse. This shifts the focus from normalization to performance and query efficiency, which sounds appropriate for the application you've described.

To learn more about this, look into Dimensional Modeling. Those tables with large numbers of columns representing numeric data would most likely be "Fact" tables and the smaller, more descriptive tables would be "Dimension" tables.

Ralph Kimball has published lots and lots of good information about designing, implementing, and maintaining data warehouses. Read his stuff!

Answer 2

It is OK to have many columns if there truly are many different aspects of an entity. But sales_annual_01, sales_annual_02 etc. just scream "bad design". If you have multiple versions of essentially the same attribute, almost certainly they should be in a separate table which you can join to your table if needed - that way you don't have to update an unknown number of places in your logic oncen the powers that be suddenly demand seven-year reports rather than five-year reports (which you know they will, eventually).

(Note that if you do have the scenario that CFL_Jeff suspects, this is less relevant: normalization isn't as important when you won't be changing your data or your schema and performance is the foremost requirement.)

Answer 3

Try this division to tables:

Companies
(
    CompanyPK PK,
    Name
)

Sales
(
    CompanyPK PK,
    Year PK,
    Value
)

Expenses
(
    CompanyPK PK,
    Year PK,
    Value
)

This way you can have multiple years per company and take into consideration that some companies might have not existed in a given year. Also you will not need to move data around each year - just add rows.

Profit would be a calculation (Sales = Expenses), so you don't need a table/columns for that.

If you have many different non-calculated fields, then consider a dictionary approach instead...

Companies
(
    CompanyPK PK,
    Name
)

Fields
(
    FieldTypePK PK,
    CompanyPK PK,
    Year PK,
    Value
)

FieldTypes
(
    FieldTypePK PK,
    Name
)

Example of usage for second option:

select
    c.CompanyPK,
    c.Name
from Companies c
inner join Fields f1
on f1.CompanyPK = c.CompanyPK
inner join Fields f2
on f2.CompanyPK = c.CompanyPK
where f1.FieldPK = 1 and f1.Year = 2012 and f1.Value > 1000000
and f2.FieldPK = 2 and f2.Year = 2012 and f2.Value < 50000

Answer 4

For 5000 lines at 500 fields per line, the only reason to use a relational database is that your users all know how to use SQL to do queries, and you plan to give them raw SQL.

The moment you give them anything other that raw SQL to do their searches, you are far better off throwing away your DBMS and making this a straight sequential one-pass scan on a flat text file. 5000 x 500 = 2.5e6, so you have 2.5 million individual fields. Assuming average of 10 bytes per field, that's 25 million bytes. That's a memory-resident array on a single PC and initially reading the sucker into memory is what's going to kill you. Assuming average of 100 bytes per field, that's still only 250 million bytes. Maybe you page it, 50 million bytes at a whack.

Just because you have data doesn't mean you have to use a database management system.

Answer 5

1) What is the best database design approach? Is it ok to have these many number of columns?

2) How can it be normalized? (User can use any of these fields in search criteria).

This is just terrible database design. The most obvious fix is, that you need to have it in third normal form:

Companies (
company_name,
...
)

FinancialResults (
period, 
sales_annual,
profit_annual,
...
)

The EAV-style approach you can see in some answers is not the most efficient way and will be pain in the backside to extract data from.

3) Is it ok to stick with MySQL, or modern document based databases like MongoDB should be better for it?

Yes, MySQL will do perfectly fine for that. It's not the type, volumne or throughput of data for which you'd have to consider no-SQL solutions. And no-SQL solution will not work efficiently if you can query on any of the columns.

Answer 6

If I understand you correctly, this data is built out regularly and used By the end-user as a read-only data source… So the Key requirement is to have an easy structure for the user to report off of.

Assuming the above is true. You are doing a good job by saying Normalization is not important in this case I’ll just give the user one giant table to make his life easier. (You can cross off #2)

Going NoSQL (MongoDB) will make your Non-Tech users life a living hell, with only 5000 rows there is no way you are having performance issues (You can cross off #3 and stick with MySQL)

So what are left is the issue of too many columns. Honestly is not the approach I would take. but it’s not that that big of a deal either.

I would make Year a column then add a static set of columns for sales_annual, profit_annual ect.. (But there is a chance your user won’t like this, so talk to him\her fist. )

Answer 7

Switch to a normalized structure. SQL is fundamentally integrated to this design choice. Failure to comply will cause you problems. Either your users will truly understand SQL, and will have no problem, or your front end needs to do the heavy lifting and mask the fact that all of these columns are normalized away.

Secondly, use UUIDs! This will prevent a massive amount of grief in the connecting of your disparate data on the fly. And, your search criteria fits what the user asks. If they want all records from 2010 for company Foo, then that's just an inner join of the records table on the company table where company name = Foo and an inner join on the years table where year = 2010.

Finally, your performance will be very fast with a proper setup, because you aren't breaking the SQL design (see item 1). SQL is all about sets so setting things up for queries like the example in item 2 will work very fast. If everything is in one big table, all of a sudden the front end has to wait for every single row to be returned across a network, and then it has to read every single row and check it against the search criteria. This is not procedural programming, do not pretend it is.

For an excellent post on all of this, read this article.