10

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.

8
  • 1
    Hi, what is the meaning of 00, 01, 02, 03... ?
    – Skrol29
    Mar 19, 2012 at 12:59
  • 00, 01... stands for different years. Like 00 represents most recent year, 01 - an year before that.
    – Pratyush
    Mar 19, 2012 at 13:16
  • With a design like that, you'd have to continually rename columns. Why not name them by absolute year, rather than "years ago," e.g. sales_annual_2012, sales_annual_2011, sales_annual_2010, ...?
    – Matt Ball
    Mar 19, 2012 at 13:18
  • If I use absolute years, then I would need to add more columns each year. Also, like these annual columns, there are quarterly columns too :). In that case, I would need to add another column for each quarter too.
    – Pratyush
    Mar 19, 2012 at 13:26
  • please take a look at the wordpress database design approach. you can go from column wise approach to row wise approach for your columns
    – maz3tt
    Mar 19, 2012 at 13:37

7 Answers 7

13

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!

4
  • Thanks Jeff, you introduced me to a very new concept. I was reading this post to understand the difference between Database and Data Warehouse. I still couldn't figure out if DW is a concept or some database solution like MySQL. Also the article says "if you are cash strapped, you could easily do that at extremely low costs by using excellent open source databases like MySQL." Can provide any link to that please. Thanks again :).
    – Pratyush
    Mar 19, 2012 at 13:37
  • Yes, data warehousing is a design concept, not a product like MySQL. There are many good white papers available online and there are books available on the specific subject of dimensional data warehousing with MySQL
    – CFL_Jeff
    Mar 19, 2012 at 13:54
  • 1
    Links are outdated.
    – Alex R.
    May 11, 2021 at 8:50
  • Updated links. Thanks @AlexR.
    – CFL_Jeff
    Jul 20, 2021 at 18:07
13

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.)

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
4
  • Hi Danny, there are more than 100 fields like sales, profit, interest... The second option looks good, however, I think in case of second option, the query searching won't be possible. Eg. finding companies with interest_annual_00 < interest_annual_01 and profit_annual_00 > profit_annual_01.
    – Pratyush
    Mar 19, 2012 at 14:00
  • I'll add an example query. Mar 19, 2012 at 14:04
  • if the data can be searched with second approach, then that might be the perfect solution. Thanks You!
    – Pratyush
    Mar 19, 2012 at 14:11
  • 3
    The 2nd example is an EAV, which tends to fall apart when attempting heavy analysis (due to requiring a ridiculous number of joins). It is useful in some situations, although I would vastly prefer a relational model for most of this - especially if you're planning on storing something other than the base records - maintaining multiple copies of the data (base and aggregates) is annoying, and dangerous. Mar 19, 2012 at 23:23
1

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.

1

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.

1

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. )

1
  • Thanks Man, you very well interpreted my problem. I have updated the question with a preview link of how things are working currently. Yes, "one giant table" really makes queries easy. However, though there are currently 500+ columns (for 5 year financial data), I seek to further crease that to 10 years data. So is it still ok if I have 1000+ columns? Performance is not much of a problem currently as the speeds are pretty good already.
    – Pratyush
    Mar 19, 2012 at 13:46
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.