Background
I am trying to design a PostgreSQL database that will store trade records from cryptocurrency exchanges. I have a single Trade model in my client, but would like to split the trades into their own tables based on the symbol they represent. The symbols that are available are deduced at runtime, and trades from the exchange each have their own Id sequence starting at 0.
For example, say I want to cache trades from Binance. My client would call their API, see that (some) of the symbols available on the platform are BTCUSDT, ETHUSDT, and ETHBTC, and if those symbols aren't already in the bn.Symbols table, it would insert them, as well as create a new table bn.Trades-SYMBOLNAME.
The tech stack I am using is .NET Core 3 with Dapper and PostgreSQL.
The issue
I have seen several posts detailing how to write a procedure or function that creates a table with a provided variable as the name, and a common sentiment seems to be that this is a poor design.
In addition to this negative sentiment, my client is using the Repository and Unit of Work Patterns, and the TradeRepository is interfaced in a way that defines a GetById(long) method, so I cannot specify which symbol I want the repository to target, nor can I specify the symbol in the repository functions, without resorting to some dirty tricks.
A simple solution would be to store all trades in the same table, have a foreign key column for Symbol, and create a unique composite index for the Id and Symbol columns. However, I am hesitant to do this, because I will eventually be storing over a billion trades, and this approach also still causes issues with my current Repository pattern design.
Is there a better way to approach this?
Code
Here is some code showing how my models are currently configured:
public abstract class EntityBase : IEntity
{
#region Properties
public long Id
{
get;
set;
}
#endregion
}
public class Symbol : EntityBase
{
#region Data Members
private long _baseAssetId;
private long _quoteAssetId;
#endregion
#region Properties
public string Name
{
get;
set;
}
public long BaseAssetId
{
get
{
if( BaseAsset != null )
return _baseAssetId = BaseAsset.Id;
return _baseAssetId;
}
set => _baseAssetId = value;
}
public int BaseAssetPrecision
{
get;
set;
}
public long QuoteAssetId
{
get
{
if( QuoteAsset != null )
return _quoteAssetId = QuoteAsset.Id;
return _quoteAssetId;
}
set => _quoteAssetId = value;
}
public int QuoteAssetPrecision
{
get;
set;
}
#endregion
#region Navigation Properties
public virtual Asset BaseAsset
{
get;
set;
}
public virtual Asset QuoteAsset
{
get;
set;
}
#endregion
}
public class Trade : EntityBase
{
#region Properties
public decimal Price
{
get;
set;
}
public decimal Quantity
{
get;
set;
}
public decimal QuoteQuantity
{
get;
set;
}
public long Timestamp
{
get;
set;
}
public bool IsBuyer
{
get;
set;
}
public DateTime Time
{
get => Timestamp.ToUnixTimeMilliseconds();
}
#endregion
}
Here is how all repositories are interfaced:
public interface IRepository<TEntity> : IDisposable
where TEntity : IEntity, new()
{
Task AddAsync( TEntity entity, CancellationToken cancellationToken = default );
Task<TEntity> GetByIdAsync( long id, CancellationToken cancellationToken = default );
Task<bool> RemoveAsync( TEntity entity, CancellationToken cancellationToken = default );
Task<bool> UpdateAsync( TEntity entity, CancellationToken cancellationToken = default );
}
And here is how the UnitOfWork is interfaced:
public interface IUnitOfWork : IDisposable
{
#region Properties
Guid Id
{
get;
}
IDbConnection Connection
{
get;
}
IDbTransaction Transaction
{
get;
}
#endregion
#region Repositories
IAssetRepository Assets
{
get;
}
ITradeRepository Trades
{
get;
}
ISymbolRepository Symbols
{
get;
}
#endregion
#region Public Methods
void Begin();
void Commit();
void Rollback();
#endregion
}
