Rule #1
The design of a database must only be thought after the through analysis of the user requirements.
Rule #2
Never implement any functionalities of Oracle if it is not required. Always adhere Rule #1.
Rule #3
Never implement as per theory, but base your design on practical thought. Always adhere Rule #2.
What does this mean?
I will elaborate each and every point mentioned above. Also note that all the rules are related to one another. This ensures that the design is always as per user requirements.
Rule #1
The first rule says that never start or think of database design before understanding what exactly the user requires. This is the major and very important rule which must be adhered, whatever comes.
This rule thus verifies the importance of analysis of user requirements. Unless the requirements of user is fully understood, it will be nearly impossible to design the database.
Rule #2
This rule again double verifies the rule #1. The design must be using only using such oracle functionalities which are exactly required. Otherwise if a new feature is used without additional thought on it, it will affect the application performance and also may throw the application as useless.
For example, for an order entry screen there is one master-detail table identified as per user requirements. The normal way of going for design for such a scenario is for designing two tables with parent key (primary key) and child keys (foreign keys). But if we design the same structure say with Nested tables, it will affect the way the data is stored. It will also be very difficult for MIS applications which require just the child tables.
Rule #3
This rule reminds of the theory we learn when studying the database concepts. For example Normalization. We have all learned that all tables must be normalized. And normally we all adhere to this rule. But this is just a guideline rather than a rule.
For example in a small application (it is left to the user about what a small application is and what is a large application) the performance might not be of a big effect. But in large applications, this will prove to be fatal. We will take the case of normalization itself. If all the tables are normalized then oracle will be storing data in more than one tables for a transaction. This will cause more overhead for the database to fetch the data, maintain index storage, maintain data storage etc.
It is not though meant that normalization is bad. But normalization of tables which can be avoided should not be normalized. Do not think of your database design in which normal form, wheras think how well it can help the user in maintaining their data.
Thus the database design cirulates around these:
1. Collect necessary user requirements
2. Only after collecting user requirements think of database design
3. Design the database in such a way that it is user oriented (data oriented) rather than theory oriented
4. You need not use all the features that oracle offers.
This ends part 1 of Tuning guide. Next parts will be updated soon. Watch this space.
