Postgres db design规范化表或使用数组列 [英] Postgres db design Normalize tables or Use Array Columns

问题描述

新手试图找出针对以下用例场景设计Postgres数据库的最佳方法.

Newbie trying to figure out the best way to design a Postgres db for the following use case scenario.

有一个用于业务客户的帐户"表，还有一个具有列关系的联系人表.

There is an Account table for the business customers and there is a contacts table with a column relationship.

account.pk_id，....

account.pk_id, ….

contacts.pk_id，contacts.fk_accountid…

contacts.pk_id, contacts.fk_accountid …

帐户"表中的数千个不同业务将在联系人"表中存储数百万个联系人.

Thousands of different businesses in the Accounts table will be storing millions of contacts each in the Contacts table.

随着时间的推移，每个联系人记录将属于1到100个不同的类别，列表和产品.

Each contact record will over time belong to between 1 and 100 different categories, lists and products.

如果我使用经典的sql主/子关系，则我最终可能会在表中使用数以百万计的行，例如contacts_categories，contacts_lists和contacts_products，这些行会引用Categories，Lists&产品表.

If I use a classic sql master/child relationship I potentially end up with millions and millions of rows in tables such as contacts_categories, contacts_lists and contacts_products which would reference from Categories, Lists & Products tables.

或者，我可以将类别，列表和产品的相关键(uuid)存储在联系人记录行中的3个字符可变的array []列中.这将消除对相当大的contacts_categories，contacts_lists和contacts_products表的需要.

Alternatively, I could store the related keys ( uuid’s) for categories, lists and products in 3 character varying arrays[] columns in the contact record row. This would eliminate the need for the contacts_categories, contacts_lists and contacts_products tables that would be quite large.

使用诸如Select unnest，array_append()和数组索引选项之类的工具，这似乎是一个聪明的解决方案，但很想知道是否最好坚持规范化的关系以及更多的表和行数以提高性能和/或存储内存/费用.

With tools like Select unnest, array_append() and the array index options it seems like a smart solution but am curious to know if it is better to stick to normalized relations and more tables and row counts for performance and / or storage memory / cost.

有人尝试过吗?

推荐答案

有太多人尝试过，但这不是一个好主意.您的许多查询(尤其是联接)将变得复杂而缓慢.此外，您将没有外键约束来保证数据完整性.

Too many people have tried that, and it is a bad idea. Many of your queries, particularly joins, will become complicated and slow. Besides, you won't be able to have foreign key constraints to guarantee data integrity.

关系数据库擅长处理表中的数百万行.保持架构规范化.

Relational databases are good at coping with millions of rows in a table. Keep your schema normalized.

这篇关于Postgres db design规范化表或使用数组列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！