Many fields in one table OR many tables each with one field??

[elziko]

I was going to create a table with 50 fields in it. Many thousands of rows would be added to this table and in any

one row most of the fields would be empty. However the fields that would be empty vary each time a record is added.

How does Oracle deal with these empty 'cells'. Is storage space still allocated even if a value is not stored?

If so would it be better to have 50 different tables and have one of these fields in each one. Then if data has not

been recorded for a particular field then a row will not be added to a certain table, saving space.

How would the ease and speed of performing queries be affected by spliting fields over many tables?

Any suggestions and comments on this would be much appreciated!

Cheers,

elziko

 

[carp]

Oracle will not reserve space for null values. It DOES allocate one byte for the field length indicator.

As to whether you should use many tables or one big table, I guess the answer is "it depends". What kind of application are you building? Is your current big table well normalized?
I would work on coming up with a good data model and let that be the primary driver of whether or not to split up the table.

 

[jcote]

Make sure your data model is good. It surprises me that you can have such differences between rows as to the columns that get data or not.
But in the case where your model is OK, I would not care to much about space lost for empty column. One bytes per column makes will make at most 50 bytes lost per rows, which is insignificant to me in these days and age.
If you go to the extreme to create multiple tables, you'll pay a much, much more bigger cost in my mind when you'll try to put all of those tables together . And just the duplication of the primary key (to be able to put back the data together) will be more costly in disk space that the single table solution.

 

[elziko]

Thanks for your replies.

Unfortunalty the nature of the beast dicates that many columns will be empty.

Since each entry can be stored in 2 bytes I think the lost byte may be significant?

jcote, you mentioned: "And just the duplication of the primary key (to be able to put back the data together) will be more costly in disk space that the single table solution."

Could you explain why this would be so? Sorry if I sound ignorant but I have no formal Oracle training. Until now I have been involved only in data access rather than database design.

Many thanks,

elziko

 

[kindus]

Hi,
Reading your post I could visualize the scenerio you are facing. May be following suggestion may give you different approach to resolve your problem.

create a generic table <gentable> with say colums
1) key columns
2) n varchar2 fields
3) n number fields
4) n/2 date fields

You may create 50 views on this table based on key value to make as if it is 50 tables.
But you are better judge, you know your application better than anybody in the forum.

best of luck.
~kindus

 

[jcote]

What I meant by the duplication of the primary key goes like this. If I understand correctly, you are undecided between one table with multiple columns, but only a few column that could be filled, and option number 2, where you would have multiple tables, with few columns. I imagine that these tables would be define so that when you insert a row in it, all (or most of the columns) would be filled.

In each option, the columns have to be related to some kind of key or they have no sense at all. In the first option, all the key would be related together by one column, in the same table as the information column.

In option number 2, you would have to duplicate that key to every table needed to store the information related to that key. Lets says that you split your big table in 5 tables, and that to store the information for a particular key, you need 3 of the 5 splitted tables, you would have 3 times the space needed for the key instead of one. On top of that, you would have to account for the space in the indexes needed for each table. Also, when you'll want to have all the information together, you'll have seek 3 tables instead of one, thus, spending more cpu.

 

[carp]

With multiple tables, you not only incur the additional storage required for values to join on, but you additionally wind up with sparsely populated columns anyway, possibly in multiple tables.

Add to this the performance whack of having to join multiple tables, not to mention the fact that you may now have induced a lot of outer joins (even MORE of a performance whack) to get all of your data.

All in all, if the 50-column table is actually a better logical model for your application, I would go with it!