Re: Table Design Question pls help..

From: Brian Dick <bdick_at_cox.net>
Date: Wed, 6 Oct 2004 12:15:58 -0400
Message-ID: <1ny5q5zbbw8if$.1lghqvdvq83h3.dlg@40tude.net>

On Sun, 03 Oct 2004 21:48:06 -0700, Daniel Morgan wrote:

> Comments in-line.
> 
> xtanto wrote:
> 

>> Hi gurus,
>>
>> I have two design question, could you please give some reccomendation

>>:

>>
>> (1) Instead of CHAR, I plan to use VARCHAR2 for all character type
>> column. This is to avoid having to use RTRIM for char comparison from
>> within Forms application. Is there any bad impacts of this design
>> decision ?

> 
> No.
> 

>> (2) We have two tables of organization membership, one for 'permanent'
>> member , the other for 'non-permanent' member. After two years,
>> non-permanent member will become permanent member. The permanent
>> member is 4.000.000 rows and the non permanent member is about 200.000
>> rows.
>> These two tables are separated because there are reports that are only
>> dedicated to Non-permanent member, so that the query will be faster.
>> BUT there are also reports that combine the two tables using UNION.
>> Is using UNION with this amount of rows will cause any bad performance
>> ?
>> Or these two table should be only one tables using member-type column
>> ?
>>
>> Thank you for any comments,
>> xtanto

> 
> Combine the tables. Your design is non-relational and guaranteed to
> create problems.

We have not been given any information about the attributes of a member versus a non-member. Nor have we been given any information about the relationships between members, non-members and other entities in his domain. Until given this information, making a normalization judgement is totally speculative.

If we put normalization aside and just address actual implementation of the tables, then we still don't have enough information to make a judgement. We need to know the usage patterns of the data (CRUD matrix). If members and non-members have drastically different usage patterns, keeping the phsycally distinct could be the correct implementation. A UNION can be quite fast, especially if it is a UNION ALL. And if the UNION is used infrequently relative to the usage frequency of its constituant parts, keeping them separate can be more performant. Received on Wed Oct 06 2004 - 11:15:58 CDT