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!