DB Design - Store summary data at master table, instead of deriving it — oracle-tech

    Forum Stats

  • 3,702,028 Users
  • 2,239,548 Discussions
  • 7,835,719 Comments

Discussions

DB Design - Store summary data at master table, instead of deriving it

Hi All,

I am trying to prepare DB design for APEX application. Requirement is as follows.

In Departments IR page, users are asking below columns

1) Number of employees in each department (Department may or may not have employees)

2) Primary Location for Department (Department can have multiple addresses and addresses are stored in other table, along with primary flag)

3) Alternative Manager's Email Address for Department (alt_manager_id column, this is optional column and refers to employees table)

I can implement these requirements using either inline sub queries or using OUTER JIONs. But, these approaches will have performance impact as the data grows (like 100s of thousands of rows). So, my question is, is it ok to store these data directly at "Departments" table and update "Departments" table when child tables gets updated. Basically, I am trying to store summary data at master table, instead of deriving it as on when needed from child tables. Is this considered bad practice? Is it ok to implement such DB design?

Thank you

Best Answer

Answers

  • EdStevensEdStevens Posts: 27,735 Gold Crown
  • EdStevensEdStevens Posts: 27,735 Gold Crown

    That question probably should have been migrated to DBA StackExchange

    I've never really gotten a grip on that whole StackExchange architecture, and the difference between SE and SO. I just have a bookmark for each and filter on messages tagged 'oracle'. If the question was in the wrong place there, that's up to their moderators.

    😁

  • Mike KutzMike Kutz Posts: 5,548 Silver Crown

    SE has a LOT of different categories. It appears that each one is a different "interests groups".

    SO would be the OVERFLOW section for questions that don't appear to fit into one.

    back on topic

    I once had the "bright idea" of starting at my Step 2 (MV) for one (personal) project.

    It turns out, ATP Free Tier (19.5c EE) does not support what I need to make it a viable solution ( FAST REFRESH + QUERY REWRITE ). Until I get around to fixing it, I'm stuck with REFRESH COMPLETE ON COMMIT.

    Since I'm always looking at the sum() values, a CLUSTER table sounds like a good idea. But, I don't think that the performance gains out weigh the extra cost of development/maintenance.

    As I've said in one DBA SE answer (regarding clustered tables):

    Whenever you have a schema design idea, run benchmarks to (dis)prove its usefulness.


    So, for the OP: Start with a VIEW.

    My $0.02

    MK

  • John-APEXJohn-APEX Posts: 5 Green Ribbon

    Thank you. I will try with simple approach and if needed will try using MVs.

Sign In or Register to comment.