Problem solve Get help with specific problems with your technologies, process and projects.

A design question

Scenario: Account Harry buys 100 shares of IBM at $100. So, the total is $10,000. This trades may have n number of fees associated with it or no fee at all. Also, this trade will lead to a movement of stock and money. I will get 100 shares of IBM, and I will give up $10,000. The movements of stock and money are recorded separately. The movements may be broken up into more than one transaction. So, I may get 50 shares from one and 50 shares from another seller, and so the cash movement will be broken up accordingly. This usually happens within 3 days of the trade. Now the requirement: To be able to see all the fees that relate to a trade and their corresponding cash and stock movements. MY SOLUTION:
  • Create a Fact_TRADE that has everything that relates to a trade including the fee. Standard fee type amounts will be carried on the trade record. Total fees will also be carried.
  • Create Fact_FEE that has all the details for FEE and are this fact table will be connected to the FACT_TRADE, so 1 to many relationship.
  • Create a Fact_CASH that will record all the cash movements that relate to a trade. Again a 1:M relationship with a Fact_TRADE record.
  • Create a Fact_STOCK just like the Fact_CASH record with a 1:M relationship with the Trade record.

As you can see these are Fact table joins. These can be expensive. But I cannot see another way around since they are all at different grains. Can you think of an alternative? Any suggestion will be appreciated.

Your approach has a heavy dimensional modeling influence. I would recommend seperating your data warehouse into architectural layers that are designed to function in specific roles. The 1st role: is the In-Take layer. This is typically modeled to closely align to you enterprise data model, highly normalized, subject oriented, integrated and contains atomic level of history. The Next Layer is the distribution layer. This layer provides a means to "serve up" data from the intake layer for purposes of delivering data to data marts. The Data Marts serve in the capacity of the access layer. More times than not, this layer will be dimensional, but doesn't always have to be. I would recommend maintaining the transaction details in the intake layer in a normalized form. Then look at your customers' access needs and opportunistically build your dimensional data marts sourcing them from the in-take layer and aggregating them as needed.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.