Thursday, August 27, 2009

DB Schema Design

We were in the middle of a DB Schema review and I thought of discussing some important points from it. You may have many more important things to add as well, so pls feel free.

1. Identify the Parent Table. It's good to identify whether your schema has a parent table or not. There could even be contention for the Parent table and then you have to decide whether you have multiple parent tables or not.

2. It's probably sensible to keep some tables in de-normalized form. Specially if your system is in the need of faster searches etc...

3. The tables should not contain any derivative fields. Eg: If for an event table start_date and end_date is included, do NOT include number_of_days as another field.

4. Its sensible to name the Primary Key column of each table as 'Id'. Also name each foreign key column as Id. Eg: If you have a School and Student tables where School has many students, Student can have Id and SchoolId columns. This could be specially useful for SQLGeneration logic later in the product life cycle if you do this consistently across each table.

5. Provide provisions for future extension or integrations to the system. If some of the tables (content) has a potential to be replaced by an external system(s) later in the life cycle, try to keep those tables isolated from your main tables by introducing a mapping table in between.
Eg: Assume that your crm system is capable of integrating with different ERP systems. Your system might anyway have to keep some information from ERP system duplicated in your system for ease of search etc... In this case it's not advisable to keep those tables tightly integrated to your main tables. The trick is to use an intermediate mapping table like ERPRef with an id and the rest of the fields keeping relevant ERP data together. May be you can just keep set of ERP specific Ids so that when u need data you can go and fetch it. Also you can have other base data in de-normalized form in this table to facilitate some heavyly used search within your system boundaries.

This would be the table facing heavy changes when your crm system is integrated with different ERP systems, isolating the core tables in your system.

For an example this table can have id,erp_ordertable_id, erp_account_id, suppliername, suppliercategory etc...

Post a Comment