I have a question related to designing. Please consider the following scenario -
Have some master tables for each transaction type-
master_table_1 - col11 number primary key, col12 number, col13 number
master_table_2 - col21 number primary key, col22 number, col23 number
master_table_3 - col31 number primary key, col32 number, col33 number
and have a transaction table that records every transaction
trans_table_1 - coltt1 number primary key, col11 number references master_table_1(col11), col21 number references master_table_2(col21), col31 number references master_table_3(col31)...
In the above table, only one foreign key will be populated at a given time i.e. col11 or col21 or col31 based on the type of transaction and the rest are NULLs. Couple of thousands records will be added to this table every day. So, it grows really fast.. and also bigger within few months of time.
We will have a front end web application developed in Java to access these tables. The application is designed in such a way that it requires 3 objects (or DB tables/views) for these 3 transaction types.
Now my question is, what would be the best to way to do this, whether to have 3 materialized views created on these tables using joins or simple views as shown below
vw1 - tran_table_1 joined master_table_1
vw2 - tran_table_1 joined master_table_2
vw3 - tran_table_1 joined master_table_3
Will there be any difference if we use materialized views instead of normal views???