On 2025-10-19 13:43:09 -0700, Rich Shepard wrote:
> 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.
Replace "sometimes" with "usually", or "conventionally".
"No composite values" is the core of the first normal form. And of
course the other normal forms build on the first normal form, so your
dataase will never be normalized if you have arrays or other composite
values (e.g. json) in your database.
That said, sometimes it is advantageous to eschew normalization (if that
wasn't the case, PostgreSQL wouldn't have arrays, json, etc.).
The main reasons (IMHO) are:
* Performance: It's just a lot faster to get a row from a single table
than to join multiple tables.
* Simplicity: If a set of values is always fetched together and updated
together, you might as well treat it as a unit and not split it over
multiple tables
I think the first reason is moot in your case. But it's the reason why
datawarehouses often contain huge tables with hundreds of columns and
lots and lots of redundant data.
The second may be relevant for you. If you always display and edit the
phone numbers of a contact together and your frontend makes it easier to
edit an array than a subset of rows from a table, you might just stuff
them into a table and ignore "purity".
hjp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
