Flat Tables vs Relational DB for Stock Data 4

[iwease]

Hi,

I'm trying to store stock data in a data base. All stocks will store the date, open, high, low, close. Some stocks may also have additional information stored such as P/E ratios and Debt/Equity ratios. When I first thought of this, I figured I would store the data in a few tables and have it without nulls

TableataTypes (this would be Open, high, low, close, p/e, ratios, etc)
DataTypeID
Name

Tableata
FK_StockQuoteID
FK_DataTypeID
Value

Table:StockQuotes
FK_StockID
Date

So, in this case, only the data that is available for each stock is stored. However, the more I look at this design, the more I think it is stupid. Having nulls in a table is not such a bad thing. Does something like this flat table make more sense?

Table:Quotes
(pk)FK_StockID
(pk)Date
Open
High
Low
Close

Table:ExtendedQuotes (all possible values that may be stored for the stock. Some will be null)
(pk)FK_StockID
(pk)Date
PE
DebtEquity
etc

Note that new quotes will come in many times each day.

 

[johnherman]

The flattened table seems to make more sense. Although you will spend a bit indicating that a particular column is null, it's better than spending CPU and memory at runtime joining the three tables. Disk is cheap relative to performance.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[ptheriault]

What happens if you add a new Datatype in the flat table design? You'll have to change your table structure. That's a bad approach.
I would go with the first option.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

Let's not destroy the concept of a relational database here.

If we store data in a flat table because disk is cheap we should just go back to flat files.

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[ptheriault]

I agree with onpnt. Also, a properlly designed database will not suffer performance problems because you have a join!

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

actually a join will be faster especially with multiple hardrives and multiple CPUs/Cores because of Parallelism (each CPU will grab one table and then it will be merged)

It is faster to return and find rows from 2 skinny tables that 1 fat table

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[iwease]

hmmm...well, how expensive is a join....suppose that I have, say 500 stocks, and each of these stocks has an average of 30 various DataTypes...and that they database is accepting quotes every 5 minutes

Then, I am joining 500 rows to 30 rows resulting in 15000...This seems like a lot when I could have used a flat file and had only 500 rows. Is this a lot

One other issue I failed to mention is that since my data types are all stored in one table, then I must be able to convert them all to the same SQL Data Type...so, ideally, they will all be Decimals but it is possible that I will have to store them as strings and then convert them to an appropriate type whenever I need them. If using a flat table (for this part of the DB only), I would not have this issue.

 

[iwease]

this is all very interesting to me. I've been designing DBs for a while now, but still not long enough to instantly have a feel for these types of problems