pgsql 关于数组的关联表设计

今天研究某应用系统的结构，学习到一个以不变应万变的设计方法 ，即以数组保存属性，可以方便的对表进行扩展而不用添加新字段。缺点 是多表关联时性能估计会差一点

表1 ：client 客户表，其中设置 有“client_level_id,client_custom_options_ids"等几个数组类型的字段；

CREATE TABLE IF NOT EXISTS public.client
(
    client_id integer NOT NULL DEFAULT nextval('client_client_id_seq'::regclass),
    client_code character varying COLLATE pg_catalog."default" NOT NULL,
    name character varying COLLATE pg_catalog."default" NOT NULL,
    client_level_id integer[],
    client_custom_options_ids integer[],
    nature smallint,
    province_id integer,
    city_id integer,
    area_id integer,
    location character varying COLLATE pg_catalog."default",
    address character varying COLLATE pg_catalog."default",
    lat double precision,
    lng double precision,
    tel_code character varying COLLATE pg_catalog."default",
    tel character varying COLLATE pg_catalog."default",
    sign_radius integer DEFAULT 200,
    form smallint,
    cls smallint,
    chain_client_id integer,
    chain_brand_id integer,
    remark character varying COLLATE pg_catalog."default",
    mobile character varying COLLATE pg_catalog."default",
    birthday character varying COLLATE pg_catalog."default",
    gender smallint DEFAULT 1,
    is_medical_insurance smallint,
    status smallint DEFAULT 1,
    update_time timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    create_time timestamp(6) without time zone DEFAULT CURRENT_TIMESTAMP,
    bussiness_area character varying COLLATE pg_catalog."default",
    is_chain_brand smallint DEFAULT 0,
    source smallint DEFAULT 1,
    create_user character varying(255) COLLATE pg_catalog."default",
    approval_status smallint DEFAULT 1,
    approval_scene smallint DEFAULT 0,
    is_mult_address smallint DEFAULT 0,
    third_platform_id integer DEFAULT 0,
    doctor_position_id integer,
    doctor_title_id integer,
    is_first_task smallint DEFAULT 1,
    client_property smallint DEFAULT 4,
    is_not_unit smallint DEFAULT 0,
    count_type smallint DEFAULT 0,
    count_number smallint DEFAULT 0,
    company_code character varying(255) COLLATE pg_catalog."default" DEFAULT ''::character varying,
    is_owner_company character varying(50) COLLATE pg_catalog."default" DEFAULT ''::character varying,
    owner_company_name character varying(255) COLLATE pg_catalog."default" DEFAULT ''::character varying,
    owner_company_province integer DEFAULT 0,
    owner_company_distribution smallint DEFAULT 0,
    business_province_ids integer[],
    collect_type integer[],
    is_industrial smallint DEFAULT 0,
    is_industrial_default smallint DEFAULT 0,
    is_fill smallint DEFAULT 1,
    cooperation_type character varying(255) COLLATE pg_catalog."default",
    server_client_id smallint DEFAULT 0,
    is_server_create smallint DEFAULT 0,
    create_server_client_id smallint DEFAULT 0,
    contacts_phone character varying COLLATE pg_catalog."default",
    contacts_name character varying COLLATE pg_catalog."default",
    cooperation smallint DEFAULT 1,
    is_outside bigint DEFAULT 0,
    outside_mobile character varying(255) COLLATE pg_catalog."default",
    outside_read_article bigint DEFAULT 0,
    outside_favorite_article bigint DEFAULT 0,
    outside_score bigint DEFAULT 0,
    outside_time bigint DEFAULT 0,
    outside_record bigint DEFAULT 0,
    outside_record_photo bigint DEFAULT 0,
    outside_questionnaire bigint DEFAULT 0,
    top_chain_brand_id integer DEFAULT 0,
    high_level character varying(255) COLLATE pg_catalog."default",
    tsv_name tsvector,
    is_pit smallint DEFAULT 0,
    is_lock_address smallint DEFAULT 0,
    custom_province_id integer,
    custom_city_id integer,
    custom_area_id integer,
    is_agreement smallint DEFAULT 2,
    brand_sort integer,
    CONSTRAINT client_pkey PRIMARY KEY (client_id)
)

TABLESPACE pg_default;

  表2：client_level 客户等级表，对应表1 的“a.client_level_id”，

  表3：client_custom 客户自定义属性表，对应表1的“client_custom_options_ids”列。

 

   表4：client_custom_options 客户自定义属性键值表，分别对应表1和表3 ，

 

 

select a.client_id,a.name,c.title,array_agg(b.option_val) as blevel ,a.client_level_id
from client a
inner join client_custom_options b on 
b.client_custom_options_id = any(a.client_custom_options_ids)
inner join client_custom c 
on b.client_custom_id = c.client_custom_id

group by a.client_id,a.name,c,title,a.client_level_id

运行结果截图：