What are the pros and cons of storing two values in an array instead of two columns?

Question

I have a project that plans to store an away team and a home team. I'm wondering if it is better to store the teams in a single array column like this:

{Harvard,Colorado}

Or create away_team and home_team columns. What are the pros and cons of each method?

It's likely that teams would need to be queried by array position on occasion (whether they are the home or away team in the array).

Lets see. If you use the array design, can you forbid a value of {Harvard, Harvard}? I assume that a team cannot play against itself. Can you forbid a value of {Harvard, Colorado, Florida} (I assume that there no games with 3 teams) Or {Hhhhhh, Ffffff, Cccccc, FFCCAA} where the names are not actual teams? Or {Harvard}? Or just {}? (a game with 0 teams!) — ypercubeᵀᴹ, May 11 '16 at 18:53
Read also this: Is using multiple foreign keys separated by commas wrong, and if so, why? and this: Is storing a delimited list in a database column really that bad? Short answer: Yes, it's really that bad (and using an array instead of a comma-separated string is a slight improvement but still bad.) — ypercubeᵀᴹ, May 11 '16 at 18:58
Thanks for the great answer. I wish I could upvote but I don't have the required reputation on the site yet. — user465175, May 11 '16 at 19:07
Breach of Codd's second rule, the cause of endless problems/questions here. Don't do it!. Use lookup tables! — Vérace, May 11 '16 at 20:11
There are no pros for you use case, only cons. Even storage size is much bigger for an array, which imposes 24 bytes of overhead for the array type. Plus, elements of an array occupy space as usual. — Erwin Brandstetter, May 12 '16 at 01:05

What are the pros and cons of storing two values in an array instead of two columns?

0 Answers0