I wanted to just re-confirm few database design related issues in Oracle and the corresponding performance.
(1) Suppose if I have 2 tables with the same primary key but other column informations in both tables are different, is it better to go for a single table instead of two tables -as the primary key is the same. Or are there any performance issues in having a single table and multiple tables are preferred?
Just to illustrate, (a) ApplicationRequestTable has columns StartDate, ApplicationName, NumberOfRequests and (b) ApplicationResponseTable has columns StartDate, ApplicationName, MinTime, MaxTime of which StartDate and ApplicationName are the Primary key.
Is it better to have a single table ApplicationTable with fields as StartDate, ApplicationName, NumberOfRequests, MinTime and MaxTime.
(2) For a reporting tool, will it be better to have a consolidated information in a single table and define materialised views on whatever information we are interested OR is it preferred to have multiple tables for each and every dimension we are interested in.
Going by the same example, let us consider UserName is also recorded in the ApplicationTable. Hence, the fields are StartDate, ApplicationName, UserName, NumberOfRequests, MinTime and MaxTime where keyfield is StartDate, ApplicationName and UserName.
Would it be better to have materialised view on UserName and StartDate independent of the applications used OR are separate tables needed for ApplicationUsers with fields as StartDate, UserName, NumberOfRequests and Applications with fields as StartDate, ApplicationName, NumberOfRequests, MinTime and MaxTime.
(1) Having 2 separate tables seems like overkill here. If you put it all in one table, Oracle can get it all in one go.
(2) Materialized views would be preferable to holding duplicated information at different summarisation levels. That's assuming you need to do either - maybe your report can just SUM the data in a timely fashion?