Introduction

Data housed on 192.168.2.84 server running Microsoft SQL Server Version 12.00.2569 and accessed through SQL Server 2016. This design provides an ease with data entry and querying for the all the involved stakeholders.

Audience

This document helps database architects and business decision makers responsible for delivering upgrade and performance solution and are involved in the planning, design, and maintenance of databases; thus, aiding the product life-cycle.

The intended audience includes the following business roles:

• Technical/Database Architects

• Analyst responsible for driving architecture-level decisions

• Business decision makers who have business requirements

• Consultants, Business Partners, and other technical staff who work for its development

Accounts

The Accounts table details the mapping of Internal Account IDs and external group IDs along with other account details.

The Accounts can be:

• External Fund Manager

• Internal Fund Manager

• Investor

• Wallet

• Introducer

• Index

• Coverage

The fields for the Accounts table are:

Table Name: Accounts

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_AccountID	int identity		No	This field states the unique Account ID of a slave (or follower) in the Tradesocio environment generated by TC.
ExternalID	varchar		No	This field states the external Account ID, which would also be the MT4/MT5 account ID (in some cases).
Active	bit		No	The field lists the current activity of the component whether it is active or inactive.
LastUpdatedtime	datetime	(getutcdate())	Yes	The time when the last update took place.
ParticipantAccountType	int		Yes	1 = Slave 2 = Master 3 = Coverage 4 = Introducer 5 = Wallet 6 = Index
Balance	decimal	((0))	Yes	The field lists the balance, i.e., the amount of funds in an account after the closing of all trades.
Leverage	decimal	((0))	Yes	Leverage
Credit	decimal	((0))	Yes	Not in use
Currency	varchar		Yes	Deposit Currency
IsForIndex	bit	((0))	No	0 = Not an index 1 = Index
VAMI	decimal	((1000))	No	This field lists the VAMI, i.e., a measure to track the monthly performance of an investment.
ReturnPerc	decimal		Yes	Return value in percentage.
AUM	decimal		Yes	This Sum of current allocation for all slaves under a master.
UsedMargin	decimal	((0))	No	Used margin
isBBookMaster	int	((0))	No	0 = Disabled 1 = Enabled
FK_FundType	int	((0))	No	1 = Mirror 2 = Invest 3 = Index

Current Orders

The Current Orders table explains the order details of both master and coverage accounts. The fields for the CurrentOrders table are:

Table Name: CurrentOrders

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_OrderID	bigint identity		No	Order ID
LPOrdID	varchar		Yes	It is used in the case of partial close, it contains the ID of the original PK Order ID. Default it is kept as null
OrigOrdID	varchar		Yes	External order ID of Abook (master). Bbook (inhouse order ID in the case of web trading platform)
FK_AccountID	int		No	Account ID (TS ID)
PositionSize	bigint		Yes	Order quantity
Price	decimal		Yes	request price of the symbol
FK_OrderType	int		No	1 = MARKET 2 = LIMIT 3 = STOP 4 = STOP-LIMIT
FK_OrderStatusID	int		No	1 = NEW 2 = PARTIALLY_FILLED 3 = FILLED 4 = CANCEL 5 = CANCEL_ACK 6 = REPLACED 7 = PENDING_CANCEL 8 = REJECTED 9 = PENDING_NEW 10 = EXPIRED 11 = PENDING_REPLACE 12 = UNDEFINED 13 = ORDER_NOT_FOUND
FK_Side	int		No	1 = BUY 2 = SELL
OrderDateTimeRequested	datetime	(getutcdate())	Yes	Time when the order is placed. In UTC format.
Contract	varchar		No	Symbols
Product	varchar		No	Symbols
LastUpdateTime	datetime	(getutcdate())	Yes	The time when the last update took place.
FilledQty	int		Yes	filled position size
AvgFillPRice	decimal		Yes	Open price
CancelQty	int		Yes	At the event of a partial close, the remaining position size that will be opened as a new trade is the cancel quantity.
ProfitValue	decimal		Yes	Profit value
Reason	varchar		Yes	In case of trade rejection, error codes are listed here.
CloseQty	int		Yes	At the event of a partial close, the position size closed partially is the closed quantity.
PositionEffect	char		Yes	O = Open C = Closed
LinkedOrderID	varchar	('')	Yes	It is the PK order ID of Coverage and Slave order tables.
SL	decimal		Yes	Stop loss
TP	decimal		Yes	Take profit
ClosePrice	decimal		Yes	Close price
Commission	decimal		Yes	Commission
CommissionAgent	decimal		Yes	Commission agent
Swap	decimal		Yes	Swap
Taxes	decimal		Yes	Taxes
Comment	varchar		Yes	Comment
UsedMargin	decimal	((0))	No	Margin amount used in opening a trade.
ContractSize	decimal	((0))	No	Position size
Digit	int	((0))	No	digit
ReturnPerc	decimal	((0))	No	Return value displayed in percentage.
VAMI	decimal	((0))	No	This field lists the VAMI, i.e., a measure to track the monthly performance of an investment.
Equity	decimal	((0))	No	This field lists the equity, i.e., the amount of funds in an account that includes all open trades.
Profit_Mode	int	((0))	No	Profit mode

Coverage Orders

The Coverage Orders table explains the order details of coverage accounts. The fields for the CoverageOrders table are:

Table Name: CoverageOrders

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_OrderID	bigint identity		No	OrderID
LPOrdID	varchar		Yes	It is used in the case of partial close, it contains the ID of the original PK Order ID. Default it is kept as null
OrigOrdID	varchar		Yes	External order ID of Abook (master). Bbook (inhouse order ID in the case of web trading platform)
FK_AccountID	int		No	Account ID (TS ID)
PositionSize	bigint		Yes	Order quantity
Price	decimal		Yes	request price of the symbol
FK_OrderType	int		No	1 = MARKET 2 = LIMIT 3 = STOP 4 = STOP-LIMIT
FK_OrderStatusID	int		No	1 = NEW 2 = PARTIALLY_FILLED 3 = FILLED 4 = CANCEL 5 = CANCEL_ACK 6 = REPLACED 7 = PENDING_CANCEL 8 = REJECTED 9 = PENDING_NEW 10 = EXPIRED 11 = PENDING_REPLACE 12 = UNDEFINED 13 = ORDER_NOT_FOUND
FK_Side	int		No	1 = BUY 2 = SELL
OrderDateTimeRequested	datetime	(getutcdate())	Yes	Time when the order is placed. In UTC format.
Contract	varchar		No	Symbols
Product	varchar		No	Symbols
LastUpdateTime	datetime	(getutcdate())	Yes	The time when the last update took place.
FilledQty	int		Yes	Filled position size
AvgFillPRice	decimal		Yes	Open price
CancelQty	int		Yes	At the event of a partial close, the remaining position size that will be opened as a new trade is the cancel quantity.
ProfitValue	decimal		Yes	Profit value
Reason	varchar		Yes	In case of trade rejection, error codes are listed here.
CloseQty	int		Yes	At the event of a partial close, the position size closed partially is the closed quantity.
PositionEffect	char		Yes	O = Open C = Closed
LinkedOrderID	varchar	('')	Yes	It is the PK order ID of the coverage in current orders table.
ClosePrice	decimal		Yes	Close price
ClosePriceRequested	decimal		Yes	Requested close price
ContractSize	decimal		Yes	Position size
Digit	int		Yes	digit
OpenOrigIDFix	varchar		Yes	External FIX ID generated when trade opens.
CloseOrigIDFix	varchar		Yes	FIX ID generated when trade is closed.

Slave Orders

The Slave Orders table explains the order details of slave accounts. The fields for the SlaveOrders table are:

Table Name: SlaveOrders

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_OrderID	bigint identity		No	TradeID of the order
LPOrdID	varchar		Yes	It is used in the case of partial close, it contains the ID of the original PK Order ID. Default it is kept as null
FK_AccountID	int		No	Account ID (TS ID)
PositionSize	bigint		Yes	Order quantity
Price	decimal		Yes	request price of the symbol
FK_OrderType	int		No	1 = MARKET 2 = LIMIT 3 = STOP 4 = STOP-LIMIT
FK_OrderStatusID	int		No	1 = NEW 2 = PARTIALLY_FILLED 3 = FILLED 4 = CANCEL 5 = CANCEL_ACK 6 = REPLACED 7 = PENDING_CANCEL 8 = REJECTED 9 = PENDING_NEW 10 = EXPIRED 11 = PENDING_REPLACE 12 = UNDEFINED 13 = ORDER_NOT_FOUND
FK_Side	int		No	1 = BUY 2 = SELL
OrderDateTimeRequested	datetime		Yes	Time when the order is placed. In UTC format.
Contract	varchar		No	Symbols
Product	varchar		No	Symbols
LastUpdateTime	datetime	(getutcdate())	Yes	The time when the last update took place.
FilledQty	int		Yes	filled position size
AvgFillPRice	decimal		Yes	Open price
CancelQty	int		Yes	At the event of a partial close, the remaining position size that will be opened as a new trade is the cancel quantity.
ProfitValue	decimal		Yes	Profit value
Reason	varchar		Yes	In case of trade rejection, error codes are listed here.
CloseQty	int		Yes	At the event of a partial close, the position size closed partially is the closed quantity.
PositionEffect	char		Yes	O = Open C = Closed
LinkedOrderID	varchar		Yes	It is the PK order ID of the coverage in current orders table.
SL	decimal		Yes	Stop loss
TP	decimal		Yes	Take profit
ClosePrice	decimal		Yes	Close price
CurrentAllocation	decimal		Yes	Current allocation
UsedMargin	decimal	((0))	Yes	Used margin
SlaveEquity	decimal		Yes	Equity of the slave
MasterEquity	decimal		Yes	Equity of the master
SlaveCurrency	varchar		Yes	Slave currency
MasterCurrency	varchar		Yes	Master currency
ClosePriceRequested	decimal		Yes	Request close price
PSType	int		Yes	1 = Fixed 2 = Percentage
PSValue	decimal		Yes	Profit share value
WalletPSCommission	decimal		Yes	Profit share commission in wallet account
WalletRBCommission	decimal		Yes	Not in use
BSType	int		Yes	1 = Fixed 2 = Percentage
BSValue	decimal		Yes	Broker share value
WalletBSCommission	decimal		Yes	Broker share commission in wallet account
WalletCurrency	varchar		Yes	Currency of the wallet account
SwapType	int		Yes	0 = By point 1 = By money 2 = By interest 3 = By money in margin currency
Swap	decimal	((0))	Yes	Swap
SlaveContractSize	decimal	((0))	No	Contract size of the symbol in USD.
MasterContractSize	decimal	((0))	No	Master Contract Size
ReturnPerc	decimal	((0))	No	Return value in percentage.
RetunPercMapping	decimal	((0))	No	Mapping Return value in percentage.
VAMI	decimal	((0))	No	This field lists the VAMI, i.e., a measure to track the monthly performance of an investment.
MappingVAMI	decimal	((0))	No	This field lists the Mapping VAMI,
Profit_Mode	int	((0))	No	By default, it is 0.

Master Slave Account Mapping

The Master Slave Account Mapping table provides relation between master and slave accounts. The fields for the MasterSlaveAccountMapping table are:

Table Name: MasterSlaveAccountMapping

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_MasterSlaveAcc	int identity		No	Mapping ID of the master and slave accounts.
FK_MasterAccountID	int		No	Account ID of the master
FK_SlaveAccountID	int		No	Account ID of the slave
CopyStartTime	datetime	(getutcdate())	Yes	Time the copy starts
SLAmount	decimal		Yes	Stop loss amount
PeakAllocationAmount	decimal		Yes	Highest allocation amount
AllocationAmount	decimal		Yes	Amount that can be allocated for a trade.
CurrentAllocation	decimal		Yes	Amount allocated for a trade.
UsedMargin	decimal	((0))	Yes	Margin amount used in opening a trade.
ManageLots	int		No	This field provides a understanding of the type of manage lot. 1 = Fixed 2 = Percentage 3 = Equity 4 = Balance
MoneyMgmtRules	decimal		No	Used to calculate manage lot 1 and 2.
LastUpdatedtime	datetime	(getutcdate())	Yes	The time when the last update took place.
VAMI	decimal	((1000))	No	This field lists the VAMI, i.e., a measure to track the monthly performance of an investment.
ReturnPerc	decimal	((0))	No	Return value in percentage.
HWM	decimal	((0))	No	0 default. Not in use.
HWMModifiedDate	datetime	(getutcdate())	No	Default Time
AllocationID	int		Yes	Master-Slave ID used after resuming of trade. If first time, and no pause, then same as the Master-Slave ID.

Account Metrices Per Master Slave

The Account Metrices Per Master Slave table details the account description of the Slave with respect to the Master accounts. The fields for the Account_MetricesPerMasterSlave table are:

Table Name: Account_MetricesPerMasterSlave

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_ID	int identity		No	It is a unique ID.
AccountID	bigint		No	This field states the Account ID of a slave (or follower) in the Tradesocio environment and is referenced from the Accounts table.
MasterAccountID	bigint		No	This field lists the Account ID of the Master whom the slave is following and is referenced from the Accounts table.
Balance	decimal	((0))	No	The field lists the balance, i.e., the amount of funds in an account after the closing of all trades.
Equity	decimal	((0))	No	This field lists the equity, i.e., the amount of funds in an account that includes all open trades.
MaxDDPercentForDay	decimal	((0))	No	This field lists the maximum drawdown, i.e., the decline of investment from peak to dip in a day. It is calculated as a percentage of the peak value.
EOD_DDPercent	decimal	((0))	No	End of the Day Drawdown percent.
VAMI	decimal	((0))	No	This field lists the VAMI, i.e., a measure to track the monthly performance of an investment.
ReturnPercent	decimal	((0))	No	Return value in percentage.
BrokerTime	datetime	(getutcdate())	No	This field specifies the time when deductions and update occur in the table in a day.
PNL	decimal	((0))	No	This field lists the realized Profit and Loss, i.e., the loss or profit has been added to the equity as the transaction is completed.
isEOD	int	((0))	No	0 = The current day status is not yet updated and will get updated by end of day. 1 = The previous day status is updated.
UPNL	decimal	((0))	No	This field lists the Unrealized Profit and Loss, i.e., the loss or profit has occurred on paper, but the transactions are not yet completed.
LastUpdatedTime	datetime	(getutcdate())	No	The time when the last update took place in this specific table.

Account Daily Return Detail

The Account table details the account description of the Slave with respect to the Master accounts. The fields for the AccountDailyReturnDetail table are:

Table Name: AccountDailyReturnDetail

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_ID	int identity		No	This field consists of unique IDs of the account, which is further used in various other tables in database.
AccountID	bigint		No	This field states the Account ID of a slave (or follower) in the Tradesocio environment generated by TC.
Balance	decimal	((0))	No	The field lists the balance, i.e., the amount of funds in an account after the closing of all trades.
Equity	decimal	((0))	No	This field lists the equity, i.e., the amount of funds in an account that includes all open trades.
MaxDDPercentForDay	decimal	((0))	No	This field lists the maximum drawdown, i.e., the decline of investment from peak to dip in a day. It is calculated as a percentage of the peak value.
EOD_DDPercent	decimal	((0))	No	End of the Day Drawdown percent
VAMI	decimal	((0))	No	This field lists the VAMI, i.e., a measure to track the monthly performance of an investment.
ReturnPercent	decimal	((0))	No	Return value in percentage.
BrokerTime	datetime	(getutcdate())	No	This field specifies the time when deductions and update occur in the table in a day.
LastUpdatedTime	datetime	(getutcdate())	No	The time when the last update took place in this specific table.
PNL	decimal	((0))	No	This field lists the realized Profit and Loss, i.e., the loss or profit has been added to the equity as the transaction is completed.
AUM	decimal	((0))	No	This Sum of current allocation for all slaves under a master.
Credit	decimal	((0))	No	Not in use
isEOD	int	((0))	No	0 = The current day status is not yet updated and will get updated by end of day. 1 = The previous day status is updated.

Account Transaction History

The Account Transaction History table provides an insight into the transaction details of a master or slave account. The fields for the AccountTransactionHistory table are:

Table Name: AccountTransactionHistory

Column Name	Data Type	Valid Values	Allow Nulls	Description
PK_ID	int identity		No	This field consists of unique IDs of the account, which is further used in various other tables in database.
FK_AccountId	int		No	This field states the Account ID of a master or slave (or follower) in the Tradesocio environment.
ValueDate	datetime	(getutcdate())	No	Time when the value is updated.
Narration	text		Yes	Specific comments are listed over here.
Ref	varchar		Yes	References
Debit	decimal	((0))	Yes	Debit
Credit	decimal	((0))	Yes	Credit
Balance	decimal	((0))	Yes	The field lists the balance, i.e., the amount of funds in an account after the closing of all trades.
FK_FeeType	int	((0))	No	Different types of fees are: 1 = Subscription 2 = Fixed 3 = Performance 4 = Management 5 = Exit
BrokerTime	datetime	(getutcdate())	No	This field specifies the time when deductions and update occur in the table in a day.
WalletTransactionID	int		Yes	This field lists the transaction IDs for the fee deductions and transactions from the slave’s wallet account.
ReverseFeeID	int		Yes	This field lists the ID of the fee that will be reversed to the account.