Re: One table or many - need valid reasons.

<quantumonline_at_comcast.net> wrote in message news:1107810011.637061.204130_at_g14g2000cwa.googlegroups.com...
> 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:
> a) Reduced contention on a single table.
> b) Fewer indexes per table (i.e. fewer indexes to maintain on updates
> and inserts).
> c) Fewer number of rows per join on a query.
>
>
> Thanks.
>

Neither option is correct. I also agree with David. Bite the bullet, and do it right the first time. this will be a lot of work now, but will make life simple later. ALso, consider this: Each system may have a different value for a particular attribute. How will it be decided which one is correct? For instance, smoking_prefernce may be Yes in one system, and No in another. Or one system may use Y and N, while another uses 1 and 0. The list is endless, and you can't hide from it no matter which way you do it. May as well do it the right way. Whatever timeline they've come up with probably needs to be increased dramatically.

If attributes belong to a customer, they should be a column in the CUSTOMER table, not a row in a CUSTOMER_ATTRIBUTES table. What is being proposed looks a lot like an OO data structure superimposed on an RDBMS. This is a VERY, VERY, VERY BAD IDEA. Received on Tue Feb 08 2005 - 09:34:16 CST