Columns vs. rows

Data design question - which takes up more space having several tables with many columns or one table with many rows?

It depends on the application that you are about to design. Row is a composition of columns. So I couldn't get what you are looking for.

Sam

Here is a previous link that touched on that - seems just as relevant here:

http://www.dbasupport.com/forums/sho...?threadid=5155


sambavan - also contains my thoughts on the singular code table, as alluded to in the other thread :) - just thought that was kinda coincidental.


- Chris

we sell products so we have a product table with attributes generic to all products - we add tables for specific types of products and their attributes eg cd_table which contains the attirubutes values of a compact disc (title, artist, label, etc.). My boss has recently redesigned his java code so changes to the java are minimized for adding new products. For the database this meant going from several tables with many columns to one table with many rows. I'm really just wondering what the storage implications are - not which is better.

Sounds like your first design was the correct one - I'm assuming it was a subtype relationship between the Base table and the sub-type tables.

If you are going to a single table with a lot of rows, then I'm assuming you decided to make some sort of generic set of columns, such as

AttribName, AttribType, AttribNumericValue, AttribStringValue, AttribDateValue...

...and have multiple rows per entry. so, a CD has the same generic attribs as the rest, then the additional ones you mentioned. Instead of having a single record in the base table and a single record in the CD table, you now have that base record repeated multiple times, each with a separate attribute title, artist, label, etc.). in the other fields. This is called de-normalization and is a very bad idea for an OLTP database. Since you are increasing the rows in your table:

- The table will grow
- The indexes will grow
- Queries will suffer
- DML will suffer
- Maintenance of the database will suffer

Since you are duplicating a lot of fields multiple times:
- You are introducing the possibilities for data inconsistencies
- DML will suffer

Since you are making generic fields:
- You have lost the ability to do RI on these fields
- You have made the model and data more confusing

Since you are mixing the existence of attributes with the main data rows:
- What do you do for a type that has no 'extra' attributes? You must now have an entry with blank attributes.
- How do you control the requirement of an attribute value? If an artist attribute used to be a NOT NULL field, you used to be able to be positive that this value was filled. You now have no way to do that. They can delete the 'row' that represents just that attribute and you could not (easily or automatically) stop them.


There's probably more, but that's off the top of my head. Your boss may want to *seriously* re-think this decision.

Just my .02,

- Chris

Thx Chris for your .02 - I agree with your observations, from the database point of view this gives us more headaches. I hope that it seriously improves the java coding b/c I don't think I can get the powers that be to change their minds.

Point out to them the performance is going to suffer badly. And it would be next to impossible to tune the performance once it goes into production.

Sam