Re: Number of Columns in a table.

In message <1141520200.112514.209680_at_i39g2000cwa.googlegroups.com>, milind.namjoshi_at_trans.ge.com writes
>My primary Key is a composite key of 5 columns (22 bytes)
>With the Vertical/Long format it will be5+1, column repeated for all
>the attributes.
>I can not replace the PK with surrogate key as i want to use them for
>my start queries.
>
>Thanks
>

You don't need to duplicate your PK in all the children in order to use it in queries.

create table master

( master_id number(10,0), -- primary key
  pk_col_1 varchar2(10),
  pk_col_2 varchar2(10),
  pk_col_3 varchar2(10),
  pk_col_4 varchar2(10),
  pk_col_5 varchar2(10)

alter table master add constraint pk_mast primary key (master_id);

alter table master add constraint pk_mast unique ( pk_col_1,etc);

create table detail

 (det_master_id number(10),
  det_attr_id number(3),
  det_value number(10));

alter table detail add constraint pk_det primary key (det_master_id,det_attr_id);

alter table detail add contraint fk_mast foreign key (det_master_id) references master;

select * from master, detail where pk_col_1='aaaa' and master_ID=DET_master_id;

You could also consider using a cluster where you do duplicate the primary key, but oracle only stores it once.

Without knowing your business requirement it is impossible to say if you data model is right, but based on experience it is unlikely.

You have said you can't normalise because it will harm performance. How do you know? Have you tried it?

You should always normalise, because that gives you the flexibility and maintainability of the relational model, and then ONLY IF NECESSARY de-normalise to improve performance.

-- 
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.