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