I would like to share my database design experiance for one of our project with you.My previous company I worked on development side.We were working on fresh proect where we need to design database for one of our bank client.
Approach for designing should be
• Establish the Scope of the Database
• Identify the ‘Things of Interest’ that are within the Scope, (called Entities or Tables in a Database).
• Determine the Relationships between them.
1. Define the Scope as the Area of Interest.
2. Define the “Things of Interest”,(e.g. Customer), in the Area of Interest.
3. Analyze the Things of Interest and identify the corresponding Tables.For this go through the requirement document of client.
4. Produce the list of Things of Interest. Also same time decide the primary key & respective for foreign key.
5. Establish the relationships between the Tables.
For example, “A Customer can place many Orders”, and “A Product can be purchased many times and appear in many Orders.”
6. Determine the characteristics of each Table,(e.g. an Employee has a Date-of-Birth). Maintain the Data dictionary of all the objects which we need to provide to the client while the finale release.
7. Identify the Static and Reference Data, such as Country Codes or Customer Types.
8. Obtain a small set of Sample Data.
9. Review Code or Type Data which is (more or less) constant, which can be classified as Reference Data.
10. Look for ‘has a’ relationships. These can become Foreign Keys, or ‘Parent-Child’ relationships.
11. You need to define a Primary Key for all Tables.
For Reference Tables, use the’Code’ as the Key, often with only one other field, which is the Description field. For all non-Reference Data Tables, I suggest that you simply assign an Auto-increment Integer for each Primary Key.
12. Same time collect the required procedure, function, packages etc. from developer.
13. Once we decide all the tables and indexes we need to segregate those objects in Master, Transaction, History etc. We segregate those objects so that we can plan different logical segment (Tablespace) for respective objects. Depends upon the objects category we will calculate required size for the logical segment (….I will discuss this size calculation for logical segment in another post).While segregating naming convention should be specific. Such as for master table name should be start as MST_, History as HST_ etc.
14. At lat combine all those objects scripts, procedure, function, package in single .sql script. Will implement this script in given schema with required privileges.
15. Confirm the first draft of the Database design against the Sample Data.
16. Review the Business Rules with Users,(if you can find any Users).
17. Obtain from the Users some representative enquiries for the Database.
18. Review the Results of Steps 1 to 8 with appropriate people, such as Users, Managers,
Development staff, etc. and repeat until the final Database design is reached.
19. Define User Scenarios and step through them with some sample data to check that that Database supports the required functionality.
Test the project from performance point of view till the requirements meet. Review the procedure, function for sql query tuning for performance issue.
Once the requirements meet release the product to the client as version 1.Same time provide them the Data Dictionary for all the objects and ER-Diagram(….We will discuss how to design the ER-diagram in another post).
In above discussion designing the ER –Diagram is another topic which we will discuss in next post.
But this short & sweet way to design the database.
samadhandba 