One table or many - need valid reasons.

I need some assistance convincing the design group at my company of the proper database design. I believe option 2 (below) is best. Your collective feedback is appreciated.

• Situation ---

Our new front-end CRM application is being integrated with multiple "back-end" systems. Each of these back-end systems has data that is common to all back-end systems and other data that is unique to the back-end system.

Our database model contains a single table for each common entity (i.e. customer, address, salesperson, etc.). There are a finite number of these common entitities (5-10).

• The Goal ---

Store data that is specific to the back-end system in a generic fashion so that when another back-end system is integrated, minimal database changes need to be made. For example, if one of the back-end systems contains a "smoking preference" attribute for a customer, rather than creating a new column in the customer table to store the smoking preference, a new record would be added to store the information in a generic "INTEGRATION_ATTRIBUTE" table.

There are two options being considered:

• Option 1 ---

Create a ***single*** child table (i.e. INTEGRATION_ATTRIBUTES) with an ID and "type" column. Then, depending upon the type, the record would refer to a row in a specific parent table.

Create insert and update triggers on the child table to ensure that there is a row in the parent table, determined by the value in the "type" column, sharing the ID. (A pseudo foreign key.)

Create a delete trigger to ensure that if a row is deleted in the parent table all rows in the child table sharing the parent's ID are also deleted (A pseudo cascading foreign key.)

• Option 2 ---

Create a child table for each parent table (i.e. CUSTOMER_ATTRIBUTES, SALESPERSON_ATTRIBUTES, etc.). Create normal cascading foreign keys between the child table and its parent.

• Notes ---
  1. All queries on a parent table will be joined with the child table(s).
  2. The number of backend-specific data types could be quite large. Therefore, a single child table could contain many, many rows.
  3. The application is transactional in nature. There are many queries, inserts, and updates to the tables. Deletes are rare.
  4. System uses Oracle 10g.

Here are the advantages I've enumerated for option 2, are there others and are these accurate?

1. Referential integrity is supported natively, not through triggers, which is simpler and offers better performance.
2. The design is more straight-forward and easier to understand.
3. Database maintenance is easier because the data could be spread across multiple tablespaces (as opposed to being stored in a single table and therefore single tablespace).
4. Better performance due to:
5. Reduced contention on a single table.
6. Fewer indexes per table (i.e. fewer indexes to maintain on updates and inserts).
7. Fewer number of rows per join on a query.

Thanks. Received on Mon Feb 07 2005 - 15:00:11 CST