CODEWITHSUNDEEP
database-design
Elle H
Elle H

Reputation: 12237

Database Design: Many ugly columns, or separated into rows

I'm designing a database and I've hit a spot where I'm not sure which is the preferable design, so I decided to get some input here.

The problem is that I have several repeating pieces of data for columns, along the lines of

page1:

aName, aSize, aColor
bName, bSize, bColor
cName, cSize, cColor

page2:

aName, aSize, aColor
bName, bSize, bColor
cName, cSize, cColor

etc.

So I could have a design like this:

[pageId] [aName] [aSize] [aColor] [bName] [bSize] [bColor] etc.

Or split it into rows

[pageId] [letter] [name] [size] [color]

The former is better for performance, while the latter appears more clean. Is there a better way to approach the problems than these two options? I'm working with PHP and MySQL, but I'm interested in any solutions regardless of platform.

Upvotes: 0

Views: 190

Answers (2)

Peter Tillemans
Peter Tillemans

Reputation: 35351

I would use option 2 as you are likely to end up with cleaner code because you can now iterate over the letter.

Maybe that's not useful in the production site, but might come in handy in scripts or the admin site.

Also the performance should not be too bad, as the roundtrip to the server will outweigh fetch 3 smaller rows.

Upvotes: 0

Steven A. Lowe
Steven A. Lowe

Reputation: 61262

Normalize

If you have a, b, and c sections today, you may have d and e sections tomorrow. Or the c section may go away.

Upvotes: 3

Related Questions