To manage our masive transaction data and keep it to easy to query those data, we are saving our data with table like
create table stock ( stock_id integer, transaction_date date, product_id integer, beginning_bal numeric, incoming_bal numeric, outgoing_bal numeric, ending_bal numeric, warehouse_id integer, period_id integer )
Since this table contains millions of data, we made partition of this data based on warehouse_id and period_id. We are using inherit table feature of PostgreSQL and made those table as follows:
create table stock_warehouse_id_period_id INHERITS stock;
In case we have Warehouse_ID 1000001, 1000002 and Period_ID 101,102; the actual inheritad table created will be
stock_1000001_101 stock_1000002_101 stock_1000001_102 stock_1000002_102
And use those partition table to save our actual data, based on warehouse dan period info, instead of the stock table. We are using the stock table as a template only.
Since the variation of warehouse and period combination will be huge, while using Ecto, we need to map each of those table variation to its own models.
Is there any chance to make it running with single Ecto models (let say stock schema as mapping to stock tables) and all of CRUD activities should be done and depends on Warehouse_ID and Period_ID supplied ?
Need your advice please.
Thanks for reading