Announcement Announcement Module
No announcement yet.
Populate working tables using DUMB staging data+business rules for data relationship Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Populate working tables using DUMB staging data+business rules for data relationship


    My batch app has staging tables which doesn't have any relationship/constraint. The business rules helps in establishing the parent - child relationship and such valid relationship need to be written to working tables where the constraints are present.

    eg:- Order table has OrderNo, OrderDate
    OrderItem table has OrderItemNo, OrderNo, OrderQty.
    All the fields in both tables are nullable except the primary key which is nothing but a running sequence number (dumb key).

    Business rule says, OrderItem record is associated with Order record if OrderItem table has the same OrderNo as in Order table.
    The business rule involves validations (say Order record having OrderNo as NULL is invalid, Order and OrderItem can be saved only if there is at-least one orderitem for an order and so on)

    Once there is a valid order as well as all the order items associated with the Order is found, write them to the working tables where the relationship is enforced (even with the foreign key, unique key etc)

    My question is what strategy can be used to process this.

    While there is a read (reading from the tables), process (validating and transforming the data based on business rules) and write (writing to working tables) are involved as part of chunk processing, in order to ensure that Order and its related OrderItems can be saved to the working tables, I will require to read the entire staging table because one related OrderItem might be last record in the table.

    In read phase, I can definitely issue a query to retrieve OrderItems from staging table by passing the OrderNo, but that is more of a business rule which should be used ideally in the process phase rather than read phase. If I were to do this and build the object relationship using the staging data, I would be able to pass this to process phase and eventually when the commit interval is reached, will commit it. However I will be using atleast some of the business rules in the read phase.

    Even then, I will still be left with those records that are not having valid OrderNo. And also there could be some dangling OrderItem records too. Where do I process this? Another step dedicated to this?

    I took this example with two tables, but in reality this is more than 5 where the table B is dependent on A, C on B, D on C etc and A through D should be written to working tables only if related records from A through D passes the business rules.

    Any thoughts/strategies?

    Looking in to memory usage too since the whole table(s) cannot be read in to memory anyhow.

    Thanks in advance!