Is it a dangerous practice to have tables with large number of columns. Is there possibility of some performance or memory issues? My DB is Oracle 10g. It is easier to add columns for existing table then rearrange structure of db in my case. I'm talking about 30-50 additional columns to the table that already has had about 50 columns in it. Thanx in advance.
+2
A:
It isn't dangerous, per se, but it is usually a sign of poor design. Are these extra columns mostly diverse and unique, or are you adding something like the following?
mon_price, tue_price, wed_price, ..., mon_qty, tue_qty, ...
EDIT (in response to confirmation by OP):
If it's anything like what I depict above, that's definitely a bad idea. In the case of my example, normalise the schema by creating a subtable with columns parent_id, day_of_week, price, qty.
Marcelo Cantos
2010-04-26 10:14:57
Yes.Something like this.
Artic
2010-04-26 10:16:20
mon_price, tue_price, wed_price, ..., mon_qty, tue_qty, .
Artic
2010-04-26 10:22:51
Not necessarily always a bad idea. I've done this in one application. My first design, fully normalized, with separate tables as suggested, was a beast to get all the constraints right and performed very poorly. My second design, with dozens of columns in one table, was much simpler, all constraints were easily declared, and was much faster. Sure, a partially denormalized design has plenty of disadvantages but if these are accepted then it has its uses.
Jeffrey Kemp
2010-04-27 02:28:48
A:
Large number of columns definitely results in reduced space requirements ( as other fields are not duplicated ). I have a use case where I am thinking of having 120 columns where 93=31*3 columns are used to store 3 distinct daily metrics. I would like to know if there are any specifc performance issues with this approach ( compared to having a 3 columns and a separate column as day-of-month )
Prasenjit
2010-04-29 19:28:05