# 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. | ##