Modelling Financial Transactions In Database

Question

I m really looking for some input and guidance here.

Suppose you need to model a financial trading system. This needs to be able to handle different products (eg Equities(Shares) / Commodities (Oil /Gold) and Fixed Income (Bonds)

Now each product has different characteristics

The way I think about this is to have one "Transactions" table with about ten columns to "Product" tables.

This allows me to answer queries like "Show me all transactions on date X" and also allows me to provide more specific information on the actual transactions.

Am I going about this the right way?

I guess an alternative would be to have one big daddy "Product" table which stored the union of all fields but which would be very sparse

What do you guys think

Answer 1

Just because the products have different characteristics, I don't think you should have 10 different foreign keys into different products tables.

I'd suggest a single transactions table and a single (master) products table. If the product differ significantly then it might be worth keeping the key and the common pieces of data in the master and then pushing the other fields into extension tables with a one-to-one relationship back to products. This avoids having nulls everywhere but still obeys normalisation rules.

Answer 2

I know you're looking for the how-to, but in reality you might want to take a look at persistance engines better geared for this (SunGard and Murex solutions come to mind). Traditional ORDBMS/RDBMSs are not as dynamic as your needs and crawl under the demands of high volume/high transactions.