I have currently created a design which uses three main tables for storing information related to financial actions. The two tables I wish to combine are described below. There is a third table after the OrderTransactions table which contains information about each step of a transaction.
This means that anytime I have to write a query to get information down at the transaction activity level (very frequently), I will have to always perform two joins. Would it be acceptable in this scenario to combine the Orders and OrderTransactions tables, and place a ParentOrderID field in there? A transaction would either have no parent, or would have to belong to a parent that does not have a parent.
This means that the information in the Orders table will be duplicated for each transaction. The data in the Orders table is more or less static after its initial insert. The data there is never updated, no matter which approach is used.
Either approach will work, I'm just looking to see what some of the people more knowledgeable than me think of the situation.
Contains the core order information pertaining to all transactions
CREATE TABLE [Orders] (
[OrderID] [int] NOT NULL ,
[MerchantID] [int] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentMethodID] [int] NOT NULL ,
[IsTestOrder] [bit] NOT NULL ,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
Each order may have one or more transactions. All of the information in the Orders table is pertinent to a given transaction.
CREATE TABLE [OrderTransactions] (
[OrderID] [int] NOT NULL ,
[TransactionID] [int] NOT NULL ,
[TransactionTypeID] [int] NOT NULL ,
[CustomerIPAddress] [bigint] NOT NULL ,
[Description] [nvarchar] (250) NOT NULL ,
CONSTRAINT [PK_OrderTransactions] PRIMARY KEY CLUSTERED
) ON [PRIMARY] ,
CONSTRAINT [FK_OrderTransactions_Orders] FOREIGN KEY
) REFERENCES [Orders] (
I like recursive relationships. Just about every database I build has some element of recursion. But I wouldn't recommend it in this case.
Orders and order transactions are two different types of data. You want to combine them so you can avoid a join under some circumstances. But to check whether a given records represents a transaction you are going to need to use a join anyway, albeit a self-join ("A transaction would either have no parent, or would have to belong to a parent that does not have a parent"). You may save a bit on cacheing, but I doubt it.
Now, if a transaction could, under some business circumstances, represent a transaction, then you would have a good case for recursion. Or if an order could consist of a bundle of smaller orders. And I mean in your business model, not just that you COULD represent it this way in your schema.
If it's not practically useful, then it's practically useless.