Hi guys, I'm relatively new to databases and have been playing with SQL Server 2008 Express for some time. I want to build a database on stock prices and various other stock-related indicators across time. So basically it's going to have three dimensions: name of the stock, stock attributes, and time. The user will be able to query for certain stock's performance on a particular attribute across a designated time period.

Normally this would be a perfect job for OLAP (multidimensional database). However, due to financial constraint and other reasons , I am not considering OLAP at the moment. With my limited knowledge on databases, I think I could achieve the above by using:

1) Relational database: if I need to track 1000 stocks, I will construct 1000 tables, with time and stock attributes on each stock table.

2) XML: I am relatively new to XML too, but it seems that with some clever XPath/XQuery coding, dumping all the data into one huge XML database is not a bad idea (or is it?), as long as data can be effectively retrieved.

In terms of speed/performance, maintenance convenience, and server-memory efficiency, which of the above two is a better choice? Or do I really need to migrate to using OLAP?

Sorry if this is a simple question, I am a total newbie regarding databases.

Thanks in advance!!

I'm in the "regular" db field, and have played a lot with xml, so my advice would be: forget xml.

I never touched olap further than on paper, and even there it seemed a bit too "abstract" for me to catch it.
Surely playing with it would help, but as I have neither time nor need now...

So, sure, xpath can dig through an xml file and pull up the datas, but maintaining those files will be a nightmare, and any xslt transformation made against a file > 10Mo will be terribly slow.

If you can model what you need into a usual relational db, then go that way.
It will surely be easier in the future.

Thank you for your help! I'll go with conventional databases then

Just to throw my two penny worth in

I agree with Thierry.

XML -> Great for small amounts of relatively static data. Configuration data etc.

OLAP -> Sounds nice in principle, just can't quite get my head around it in practice. Mind you I never really got "pivot" tables either

Just give me a relational Db and I'll work out how to get anything you want out of it

Thank you guys for your help, these are really great suggestions.

chrishirst,

Some people actually suggest that I dump all the data into one huge table, but I was afraid that it'll get way too large for the database to handle; though again I don't really know the limit either.....

For example, if I want my database to contain 1000 stocks with 50 attributes for a 3-year period, this table will need to contain: 1000 x 50 x 260(business days) x 3 = 39,000,000 stock prices . Is this a little bit too large for one table? (If it is, as an alternative perhaps I could break 1000 stocks down into several smaller tables based on the market in which they are traded in? So one table for each market.) What do you think?

If I dump them all in one table, it'll look like the one below. Is there a better design? Please let me know what you think. Thanks!! I really appreciate your help.

Date StockName Open High Low Close AttribA AttriB AttriC ... ...
10/25/2007 Citi
...
...
11/25/2009 Citi
10/25/2007 MSFT
...
...
11/25/2009 MSFT
...
...
...

A single table would not be the best schema for such a system, the amount of redundancy would be penomenal and 40 million rows is nothing for SQL databases.

Use a relational structure (which SQL is designed to do).

I would probably go to the extreme of normalising and have a table for each attribute, maybe a table for dates and prices, high and low are then easily extracted with MIN() and MAX() functions.

Thank you chrishirst, I believe I'll only have about 50 attributes for each stock, so following your suggestions there'll be about 50 tables of 780,000 data each.

Just two more questions though,

Assuming the same amount of data, is it better to have more tables with less data in it, fewer tables with more data in it?

Since the phenomenal redundancy in the previously proposed database structure exists not in the data itself(various stock price & attribute values across time) but in the rows which are dates, will it still hurt performance? How would this one huge table perform, compared to 50 tables with 780,000 data?

Again thank you for your help!

In the DBA world when dealing with large (plus huge and enormous) data sets it's often a fine line to walk between minimising redundancy which can be very expensive in storage space and indexing considerations and data retrieval performance.

I have always leaned towards keeping redundancy at a minimum (I come from the days when storage space was ludicrously expensive) this also give maximum flexibility on dealing with changes to the information architecture.

By using separate tables for each attribute it becomes a relatively simple matter to add a new attribute without needing to disrupt existing structures.
It also makes roll outs to distributed/clustered structures quicker. Just one structure and the queries to synch up whereas a monolithic structure to synch may take require a system to be off-line for extended periods.

Thank you Chrishirst for your help!