3 Replies - 1326 Views - Last Post: 01 December 2009 - 12:44 PM Rate Topic: -----

#1 NobleJms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-November 09

Traditional relational databases, XML, or OLAP?

Posted 28 November 2009 - 06:16 PM

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 :rolleyes: , 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!! :)

Is This A Good Question/Topic? 0
  • +

Replies To: Traditional relational databases, XML, or OLAP?

#2 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: Traditional relational databases, XML, or OLAP?

Posted 29 November 2009 - 04:31 AM

A native XML database can do very smart stuff with xml-based data. However, if you need to convert your data _into_ xml before you can use it, you'd better use a traditional relational database. Relational databases have a longer history and can optimize things a lot more, in my opinion.

Just a thought: Why do you need a table per stock? If you want to compare stocks, why don't you just drop it in a single (huge) table. Any decent DBMS does not really care about that (as long as you use the right indexes).

Just a note: Why don't you just use PostgreSQL with some OLAP plugins? That will serve you best I think, and does not cost a thing.
Was This Post Helpful? 0
  • +
  • -

#3 NobleJms  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 16
  • Joined: 26-November 09

Re: Traditional relational databases, XML, or OLAP?

Posted 29 November 2009 - 05:02 AM

View PostHary, on 29 Nov, 2009 - 03:31 AM, said:

A native XML database can do very smart stuff with xml-based data. However, if you need to convert your data _into_ xml before you can use it, you'd better use a traditional relational database. Relational databases have a longer history and can optimize things a lot more, in my opinion.

Just a thought: Why do you need a table per stock? If you want to compare stocks, why don't you just drop it in a single (huge) table. Any decent DBMS does not really care about that (as long as you use the right indexes).

Just a note: Why don't you just use PostgreSQL with some OLAP plugins? That will serve you best I think, and does not cost a thing.


Thank you for the reply Hary! I thought if I put them all in one table, it'll get way too large for the database to handle; though again I don't really know what's the limit. :rolleyes:

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?
Was This Post Helpful? 0
  • +
  • -

#4 chemicalfan  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 88
  • Joined: 16-October 09

Re: Traditional relational databases, XML, or OLAP?

Posted 01 December 2009 - 12:44 PM

Each attribute would be a field, with each stock as a record, so your table would have 1000 rows, and each row would have about 52 columns. Effectively, this is a "history table", if I understand it correctly? Each transaction will insert a new row, rather than updating an existing one? In which case, your table will only have 1000 rows at the start, before any transactions. If not all attributes are updated at once, you could split them into their own tables, to save writing to the whole table at once (and therefore locking it from others to use). Hard to explain, but I'll try:

Headings (table 1):-
StockID Attribute1 Timestamp

Headings (table 2):-
StockID Attribute2 Timestamp
etc.

I'd also have a master table, with just:-
StockID StockDescription (however your users will search)

StockID will be a numeric character, as they are more efficient when querying. You'll have to be careful when using a timestamp, and how precise it is, as if you're searching by it, and it's recording to seconds in precision, a single second difference (say, when the 2 records are written) will cause the query to leave out data. In this instance, it's better to have an extra column on each attribute table for TransactionID, and write this ID to any table that has a related transaction (so, the system can find all records that relate to a given TransactionID, regardless of how many seconds the writing of new rows took). Didn't explain that well - basically, if you update attributes 3, 7 and 34 in one transaction, then write the same TransactionID to attribute tables 3, 7 and 34.

If you feel you need the big table, rather than loads of little ones, consider using the little ones, and having a big view instead - you won't lock the database as much when people are writing new records.

This post has been edited by chemicalfan: 01 December 2009 - 12:46 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1