2

Possible Duplicate:
Database Design: New Table versus New Columns

I'm in the process of building a DB schema that contains information about something we call "blocks" (not very important to the question). Each tuple in the blocks table can have a lot of metadata associated with it. Based on initial thought, the relation might look like:

block
-----------
bid : integer PK
name : varchar
prop1 : numeric
prop2 : numeric
...
propN : numeric

Correct me if wrong, but I would think this is normalized because every attribute is solely dependent on the PK.

However, I'm wondering if there are performance benefits to separating the "properties" into a separate relation such as:

block
-----------
bid : integer PK
name : varchar

block_metadata
-----------
bid : integer FK (references block(bid))
prop1 : numeric
prop2 : numeric
...
propN : numeric

One immediate benefit is that I can save space when I have a block that doesn't have any block_metadata (which is possible in my application). However, the downside is that a join is needed to access this information.

Are there any other benefits/trade-offs? Any details about how a RDBMS manages wide (30-50 column) tables? How does width affect performance? I'm using Postgres, FYI.

Any input or external references appreciated!

Community
  • 1
Jmoney38
  • 1,155
  • 5
  • 13
  • 22
  • Not necessarily a duplicate of the question - but Todd's answer in that post is awesome and does address many of the questions I listed in the OP. – Jmoney38 May 25 '12 at 15:09
  • It's not an exact duplicate, no, but "duplicate" does not necessarily mean the question is exactly the same - the answers that already exist there address the points you're raising, and while they're database agnostic, I think that's a good thing. – Aaron Bertrand May 25 '12 at 15:15

0 Answers0