I started developing business and science databases in the mid-1980s using
dBASE III, then Paradox on DOS. I defenestrated in 1997 and have used only
linux for both business and personal needs ever since and using only
PostgreSQL for my databaes (primarily for my own use.) I read Joe Celko's
monthly column in 'Database Advisor' and all his SQL books (in multiple
editions.)
Now I'm slowly cleaning up my business tracking database using features not
available way back when I developed it. That's why I ask questions that must
seem obvious to all of you who work with postgres everyday and have for
years. I'm reading 'SQL Queries for Mere Mortals' and just ordered the 4th
edition of 'Database Design for Mere Mortals' to update my SQL knowledge.
In the former book I read that sometimes it's better to have multiple values
for an atribute in a single row by creating a separate table for that
attribute rather than using the postgres array capability. The people table
in my database (1706 rows) has two attributes using the array type
(direct_phone and email), and only a few indivuals have multiple landline
phone numbers (cell_phone is a separate column) or email addresses (office
and personal?). Would it make sense for me to create new landline and email
address tables and replace the array contents?
Other than remembering to use curly braces {} when entering data into an
array column I don't think there'd be any performance benefit for making the
change. I'm curious to learn about arrays vs separate tables.
TIA,
Rich
