上一回,我们介绍了数据库视图的基本设置,这里所说的都是,我目前觉得比较好的规范,以后如果有些更好的技能,会再分享
这里先插入说明一下,上一引入的表都是干嘛的,(这个应该在前面说的......)
数据库用的是:PostgreSQL 9.3
咱们这次的例子里,有3张表,
2张维度表:
1. 时间维度
建表语句:
[sql] view plaincopy
- CREATE TABLE dm_calendar
- (
- calendar_id bigint NOT NULL,
- date_name character varying(200),
- date_name_cn character varying(200),
- current_day numeric(10,0),
- month_id bigint,
- month_name character varying(200),
- month_name_cn character varying(200),
- month_name_short character varying(200),
- month_name_short_cn character varying(200),
- days_in_month numeric(10,0),
- first_of_month numeric(10,0),
- last_month_id numeric(10,0),
- month_end numeric(10,0),
- quarter_id bigint,
- quarter_name character varying(200),
- quarter_name_cn character varying(200),
- quarter_name_short character varying(200),
- quarter_name_short_cn character varying(200),
- year_id bigint,
- year_name character varying(200),
- year_name_cn character varying(200),
- CONSTRAINT dm_calendar_pkey PRIMARY KEY (calendar_id)
- );
这张表里存放了每一天的日期
2. 行政组织维度
建表语句:
[sql] view plaincopy
- CREATE TABLE dm_dept_org
- (
- dept_dmid bigint,
- levelone_id bigint,
- leverone_name character varying(200),
- leveltwo_id bigint,
- level_two_name character varying(200),
- levelthr_id bigint,
- level_three_name character varying(200),
- level integer,
- name character varying(200),
- org_resp_person_name1 character varying(100),
- org_resp_person_name2 character varying(100),
- org_resp_person_name3 character varying(100)
- );
这张是行政组织维度,平铺存放了每个层级的组织信息
3. 服务监督得分
建表语句:
[sql] view plaincopy
- CREATE TABLE dm_deptserquality_quarterstati
- (
- quarterly_id bigint NOT NULL,
- dept_dmid bigint NOT NULL,
- service_check_score numeric(28,4),
- CONSTRAINT dm_deptserquality_quarterstati_pkey PRIMARY KEY (quarterly_id, dept_dmid)
- );
这张表里,就存了,季度ID,行政组织ID,和一个部门服务得分
4. 关系
添加新评论0 条评论