Database Design

[jayeshdba]

Hi Friend,

I am having question about database design.
Here we wil have database size of 4 tb. This database
performs only inserts and select operation. No update is going to occur. I am plannig to desgin one huge table of 82 fields
and going to partition and index it on 16 different columns.
We r using oracle 8i (8.1.6) and Sun solaries 2.8 on sun E6500
machine.

But my colleagues suggest me to go for more than one table and use joins while query
instead of one huge table.While one huge table is possible.

So what will be the best database design to get optimal
select query performance.

Thanks
Jayesh

[omegamark]

1.)In that case what will be the average row size...
2.)how big will be that table..

pls inform..

[marist89]

I personally prefer one large table with multiple partitions distributed on many disks.

[omegamark]

Your have decide purely from the application angle.

Incase you need only columns always and the rest very rarely.Because of this you will fill the buffer with unwanted buffers for a longtime . and you need to have many i/os .

then in that case it is better to denormalize the table.

[tamilselvan]

JOIN in Oracle is a costly resource. Hence , one table with 82 columns design is good. Besides, if the columns do not have value, nothing will be stored. This will save your disk space.

[pwoneill]

To what extent is everything (de)normalized when it is all in one table? If one table with 82 columns is fully normalized then of course that's the way to go.

If it's not, as pointed out by the others one big table might still be the way to go but at some point you might want to consider normalizing and creating more tables if the application requires it. You might not want one huge inflexible table if your application has to extend in the future.

Try to learn about the application and what is being stored in those 82 columns and you can make a better decision.

[dsql]

Hi Jayesh,

I too feel keeping data in one table will be suitable. As u know normalization is a good thing but it was very strongly practiced in the old days when memory and hard disk were very costly, seeing the latest trends in market, I personally feel, people go for HARDWARE TUNNING rather than SOFTWARE TUNNING as now memory and harddisk is not a problem. So you can have a bit denormalized table .NO HARM.

Regards
Deepankar