数据库如何建模

笔者从98年进入数据库及数据仓库领域工作至今已经有近八年的时间，对数据建模工作接触的比较多，创新性不敢谈，本文只是将工作中的经验总结出来，供大家一同探讨和指正。

提起数据建模来，有一点是首先要强调的，数据建模师和DBA有着较大的不同，对数据建模师来说，对业务的深刻理解是第一位的，不同的建模方法和技巧是为业务需求来服务的。而本文则暂时抛开业务不谈，主要关注于建模方法和技巧的经验总结。

从目前的数据库及数据仓库建模方法来说，主要分为四类。

第一类是大家最为熟悉的关系数据库的三范式建模，通常我们将三范式建模方法用于建立各种操作型数据库系统。

第二类是Inmon提倡的三范式数据仓库建模，它和操作型数据库系统的三范式建模在侧重点上有些不同。Inmon的数据仓库建模方法分为三层，第一层是实体关系层，也即企业的业务数据模型层，在这一层上和企业的操作型数据库系统建模方法是相同的;第二层是数据项集层，在这一层的建模方法根据数据的产生频率及访问频率等因素与企业的操作型数据库系统的建模方法产生了不同;第三层物理层是第二层的具体实现。

第三类是Kimball提倡的数据仓库的维度建模，我们一般也称之为星型结构建模，有时也加入一些雪花模型在里面。维度建模是一种面向用户需求的、容易理解的、访问效率高的建模方法，也是笔者比较喜欢的一种建模方式。

第四类是更为灵活的一种建模方式，通常用于后台的数据准备区，建模的方式不拘一格，以能满足需要为目的，建好的表不对用户提供接口，多为临时表。

下面简单谈谈第四类建模方法的一些的经验。

数据准备区有一个最大的特点，就是不会直接面对用户，所以对数据准备区中的表进行操作的人只有ETL工程师。ETL工程师可以自己来决定表中数据的范围和数据的生命周期。下面举两个例子：

1)数据范围小的临时表

当需要整合或清洗的数据量过大时，我们可以建立同样结构的临时表，在临时表中只保留我们需要处理的部分数据。这样，不论是更新还是对表中某些项的计算都会效率提高很多。处理好的数据发送入准备加载到数据仓库中的表中，最后一次性加载入数据仓库。

2)带有冗余字段的临时表

由于数据准备区中的表只有自己使用，所以建立冗余字段可以起到很好的作用而不用承担风险。

举例来说，笔者在项目中曾遇到这样的需求，客户表{客户ID，客户净扣值}，债项表{债项ID，客户ID，债项余额，债项净扣值}，即客户和债项是一对多的关系。其中，客户净扣值和债项余额已知，需要计算债项净扣值。计算的规则是按债项余额的比例分配客户的净扣值。这时，我们可以给两个表增加几个冗余字段，如客户表{客户ID，客户净扣值，客户余额}，债项表{债项ID，客户ID，债项余额，债项净扣值，客户余额，客户净扣值}。这样通过三条SQL就可以直接完成整个计算过程。将债项余额汇总到客户余额，将客户余额和客户净扣值冗余到债项表中，在债项表中通过(债项余额×客户净扣值/客户余额)公式即可直接计算处债项净扣值。

另外还有很多大家可以发挥的建表方式，如不需要主键的临时表等等。总结来说，正因为数据准备区是不对用户提供接口的，所以我们一定要利用好这一点，以给我们的数据处理工作带来最大的便利为目的来进行数据准备区的表设计。 行业借鉴经验：

数据仓库架构经验谈

对于数据仓库的架构方法，不同的架构师有不同的原则和方法，笔者在这里来总结一下当前常采用的架构方式及其优缺点。这些架构方式不限于某个行业，可以供各个行业借鉴使用。

首先需要说明的一点是，目前在数据仓库领域比较一致的意见是在数据仓库中需要保留企业范围内一致的原子层数据。而独立的数据集市架构(Independent data marts)没有企业范围内一致的数据，很可能会导致信息孤岛的产生，除非在很小的企业内或只针对固定主题，否则不建议建立这样的架构方式。联邦式的数据仓库架构(Federated Data Warehouse Architecture)不管是在地域上的联邦还是功能上的联邦都需要先在不同平台上建立各自的数据仓库，再通过参考(reference)数据来实现整合，而这样很容易造成整合的不彻底，除非联邦式的数据仓库架构也采用Kimball的总线架构(Bus Architecture)中类似的功能，即在数据准备区保留一致性维度(Conformed Table)并不断更新它。所以，这两种架构方式不在讨论范围之内。下面主要讨论剩下的三种架构方式。

1)三范式(3NF)的原子层+数据集市

这样的数据仓库架构最大的倡导者就是数据仓库之父Inmon，而他的企业信息工厂(Corporate Information System)就是典型的代表。这样的架构也称之为企业数据仓库(Enterprise Data Warehouse，EDW)。企业信息工厂的实现方式是，

首先进行全企业的数据整合，建立企业信息模型，即EDW。对于各种分析需求再建立相应的数据集市或者探索仓库，其数据来源于EDW。三范式的原子层给建立OLAP带来一定的复杂性，但是对于建立更复杂的应用，如挖掘仓库、探索仓库提供了更好的支持。这类架构的建设周期比较长，相应的成本也比较高。

2)星型结构(Star Schema)的原子层+HOLAP

星型结构最大的倡导者是Kimall，他的总线架构是该类架构的典型代表。总线架构实现方式是，首先在数据准备区中建立一致性维度、建立一致性事实的计算方法;其次在一致性维度、一致性事实的基础上逐步建立数据集市。每次增加数据集市，都会在数据准备区整合一致性维度，并将整合好的一致性维度同步更新到所有的数据集市。这样，建立的所有数据集市合在一起就是一个整合好的数据仓库。正是因为总线架构这个可以逐步建立的特点，它的开发周期比其他架构方式的开发周期要短，相应的成本也要低。在星型结构的原子层上可以直接建立聚集，也可以建立HOLAP。笔者比较倾向于Kimball的星型结构的原子层架构，在这种架构中的经验也比较多。

3)三范式(3NF)的原子层+ROLAP

这样的数据仓库架构也称为集中式架构(Centralized Architecture)，思路是在三范式的原子层上直接建立ROLAP，做的比较出色的就是MicroStrategy。在三范式的原子层上定义ROLAP比在星型结构的原子层上定义ROLAP要复杂很多。采用这种架构需要在定义ROLAP是多下些功夫，而且ROLAP的元数据不一定是通用的格式，所以对ROLAP做展现很可能会受到工具的局限。这类架构和第一类很相似，只是少了原子层上的数据集市。

总结来说，这三种数据仓库的架构方式都是不错的选择。对于需要见效快、成本低的项目可以考虑采用第二种总线架构，对于资金充足并有成熟业务数据模型的企业可以考虑采用第一种架构或第三种架构。

应用难点技巧：

变化数据捕获经验谈

在数据仓库系统中，一个很重要的目的就是保留数据的历史变化信息。而变化数据捕获(Change Data Capture，CDC)就是为这个目的而产生的一项技术。变化数据捕获常用的方法有：1)文件或者表的全扫描对比，2)DBMS日志获取，3)在源系统中增加触发器获取，4)基于源系统的时间戳获取，5)基于复制技术的获取，6)DBMS提供的变化数据捕获方法等。其中，由DBMS提供变化数据捕获的方法是大势所趋，即具体的捕获过程由DBMS来完成。

像银行、电信等很多行业的操作记录生成后就不会改变，只有像客户、产品等信息会随时间发生缓慢的变化，所以通常的变化数据捕获是针对维度表而言的。Kimball对缓慢变化维的分析及应对策略基本上可以处理维度表的各种变化。

而对于一些零售行业，像合同表中的合同金额类似的数值在录入后是有可能会发生改变的，也就是说事实表的数据也有可能发生变化。通常对于事实表数据的修改属于勘误的范畴，可以采用类似缓慢变化维TYPE 1的处理方式直接更新事实表。笔者不太赞同对事实表的变化采用快照的方式插入一条新的事实勘误记录，这样会给后续的展现、分析程序带来太多的麻烦。

接下来要讨论的是笔者曾经遇到的一个颇为棘手的事实表数据改变的问题，该事实表的主键随表中某些数据的变化发生改变。以其中的一个合同表为例，该

合同表的主键是由“供货单位编号”+“合同号”生成的智能主键，当其中的“供货单位编号”和“合同号”中任何一个发生变化时，该合同表的主键都会发生变化，给变化数据捕获带来了很大的麻烦。

项目中，笔者的处理方式是采用触发器的办法来实现变化数据捕获。具体的实现方式是：

1)建立一个新表作为保存捕获的数据表使用，其中字段有“原主键”、“修改后主键”、及其他需要的字段，称为“合同捕获表”。

2)在原合同表Delete和Update时分别建立触发器，当删除操作发生时，建在Delete上的触发器会插入一条记录到“合同捕获表”，其中“修改后主键”字段为空，表示该记录是删除的记录;当发生更新时，将“原主键”、“修改后主键”及其他需要记录的字段都保存入“合同捕获表”中，表示该记录被修改过，如果“原主键”和“修改后主键”不同，则表示主键被修改，如果相同，则表示主键没有被修改。

3)由于操作系统中的主键通常会成为数据仓库中事实表的退化维度，可能仍会起主键的作用。所以在数据加载时，需要分情况判断“合同捕获表”的数据来决定是否更新事实表中的退化维度。

可以说，这样的基于触发器的变化数据捕获方法并不是一个很好的选择。首先这需要对源系统有较大的权限;其次，触发器会给源系统的性能带来很大的影响。所以除非是没有别的选择，否则不建议采用这种方法。

而对于这样的情况，我们在建立操作型数据库系统时完全可以避免。下面是对操作型数据库系统建立者的几点建议：1)操作型系统的主键不要建立成智能型的，至少不要建立成会变化的。2)操作型系统的表中需要加入操作人和操作时间

字段，或者直接加入时间戳。3)操作型系统中操作型数据最好不要直接在原值上修改，可以采用“冲红”的方式加入新的记录。这样后续建立数据仓库时就不需要考虑事实表数据的变化问题。

最后，期待各大数据库管理系统的厂商能尽快在DBMS层提供功能强大、简单好用的变化数据捕获功能，目前Oracle已经有了这个功能。毕竟技术方面复杂的事情留给厂商做是一个趋势，而我们做应用的则更关注于业务。

第二篇：MySQL数据库导入SqlServer实践经验总结

MySQL数据库导入SqlServer实践经验总结

工作中有个任务是把MySQL数据库导入SqlServer中，因为任务紧急，这周一直在加班做，过程非常曲折，软件重装了几遍，网上资料也查了不少，基本上所有的问题都遇到了，不过总算是弄好了，期间也学到不少东西，还是总结一下！

一.MySQL的安装和基本操作（之前完全没用过）

1.安装的是mysql-essential-5.0.51b-win32.msi，同时安装了mysql-gui-tools-5.0-r17-win32.msi（这是个图形用户化界面，这个工具据说比较强大，但是我只用到其中最基本的功能，其他还有待学习）。安装过程没什么好说的，基本是按照向导提示，但是我第一次安装的时候没有提示输入用户名和口令，直接导致连接MySQL的时候连不上，试过网上一些强制重置口令的方法，都没有成功，所以也不写出来了，后来卸载了重装就好了。

2.连接MySQL：

.进入安装目录的bin目录下，输入命令mysql -u root -p回车，会提示输入密码，输入后，会出现mysql提示符，表明连接成功。

3.基本的命令：

1、显示当前数据库服务器中的数据库列表：

mysql> SHOW DATABASES;

2、显示数据库中的数据表：

mysql> USE 库名;

mysql> SHOW TABLES;

3、显示数据表的结构：

mysql> DESCRIBE 表名;

4、建立数据库：

mysql> CREATE DATABASE 库名;

5、建立数据表：

mysql> USE 库名;

mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1));

6、删除数据库：

mysql> DROP DATABASE 库名;

7、删除数据表：

mysql> DROP TABLE 表名；

8、将表中记录清空：

mysql> DELETE FROM 表名;

9、显示表中的记录：

mysql> SELECT * FROM 表名;

10、往表中插入记录：

mysql> INSERT INTO 表名 VALUES (”hyq”,”M”);

11、更新表中数据：

mysql-> UPDATE 表名 SET 字段名1=’a',字段名2=’b’ WHERE 字段名3=’c';

关于MySQL基本网上的介绍很多，可以查询，需要说明的是MySQL的配置文件是安装目录下的my.ini，这个文件很重要，里面存在了很多配置信息，这个文件出错很可能导致MySQL服务不能启动，这个文件在C:WINDOWS目录下也有一个，需和安装目录下的一致，修改的时候这两个都要修改。还有就是MySQL安装的时候会让选择默认的字符集，安装时如果已经明确字符集可以提前选好，可以省去很多麻烦，如果没有选择也不要紧，安装好后可以修改，关于字符集下面会专门说下。

二 SqlServer安装及注意事项

1.安装

本来安装的是SqlServer 2005 express版本，这个版本免费，但是很多功能受限制，比如导入导出数据功能就没有，只好卸载了重装SqlServer 2000，卸载了安装有时会出现问题，原因是注册表有个项没删干净，打开注册表，在HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager中找到PendingFileRenameOperations项目，并删除它。这样就可以清除安装暂挂项目。

还有一个问题就是xp上不好安装SqlServer2000企业版，方法如下：

1、找一张SQL server服务器版光盘，在光盘上找到目录“MSDE”并进入，运行SETUP.EXE文件，并按照程序要求进行安装。安装完成重新启动计算机。

2、运行光盘中的，AUTORUN.EXE文件，或让光盘自动运行，打开安装界面后，点击“安装SQL server 2000组件（C）”=》“安装数据库服务器（S）”这里程序将提示你“....服务器组件在此系统上不受支持，.....”点“确

定”。进入新的安装界面，点击“下一步”，选择默认的 “本地计算机”=》“创建新的SQL server”=》“仅客户端工具”...，一路点击“下一步”。直至安装结束。重新启动计算机。

3、计算机名要改成全大写的，机器名在系统属性的“计算机名”里可以找到，比如取名“SERVER”，改名后，关闭“企业管理器” 。

4、到“开始”--“程序”-“Microsoft SQL Server”中打开“客户端网络实用工具”，点“别名”如果“服务器别名配置”里没有数据，我们需要手工添加，点 “添加”按钮。在“添加网络库配置”的“网络库”选项中，我们选择默认的“Named Pipes(P)”项，并为服务器取个别名“SERVER”，这时管道名称会自动添加“\\SERVER\pipe\sql\query”我们就不要管它了。点“确定”退出。

5、这一步我们要进入注册进行一下修改了，打开注册表编辑器，找到

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\

MSSQLServer\MSSQLServer]，这一项，里面有一个键值LoginMode默认值是“1”，现在将该值改为“2”（安装MSDE时，默认的SQL Server身份验证方式为“仅 Windows"的身份验证方式，即sa用户无法验证，通过修改以上的注册表键值来将身份验证方式改为SQL Server和Windows混合验证以后，就可以用sa用户登录了）。修改完毕，重启电脑成功了。

三 MySQL数据库导入

目标是目标机的MySQL数据库导到我自己的机器上。试过三种方法，把目标机器上的数据库用phpmyadmin导出为.sql温江，然后用phpmyadmin，或者命令行导入MySQL，都不行，都是出现字符集不匹配，后来把目标机器上的数据库文件都拷贝到自己的机器上竟然可以，十分诡异，下面详细说下

1、用phpmyadmin

phpmyadmin较新的版本中有Import这个功能，老版本没有，phpmyadmin的配置就不讲了，主要是config.inc.php文件里面有不少选项，网上讲的比较多，就不重复了。

要注意的是

$cfg['Servers'][$i]['only_db'] = 'XXX';指定这个选项后，phpmyadmin中就只会出现指定的数据库了。

phpmyadmin限制上传文件的大小，默认是2M，可以通过修改配置文件php.ini（在php的安装目录下）来指定你想上传的大小。主要是这三个参数：post_max_size、upload_max_filesize、 memory_limit，还有的说max_execution_time 和max_input_time 也需修改，不过我只改了前面的就ok了。

用phpmyadmin也可以建立数据库和数据表，这里需注意新建的时候字符集的选择必须和MySQL的一致，否则中文会出现乱码。

这种方法可以导入，但是字符集一直有问题，最后没有成功。

2、用命令，命令很简单

连接上MySQL后，选择数据库命令 use 数据库名；导入命令：source XXX.sql这里的这个XXX.sql需要提前拷到MySQL的bin目录下，但是这种方式也是字符集问题，最后没有成功。

3.直接拷贝数据库文件

这种方法是把目标机器上的.frm，.MYD，.MYI文件拷贝到自己机器上MySQL安装目录data文件下对应数据库的文件夹中。必须保证你机器上MySQL的字符集和数据库源的MySQL字符集一致，我这里选的都是gb2312。然后用phpmyadmin访问，成功，中文也不是乱码了。

四、MySQL导入SqlServer

方法：

1.安装mysql数据库的ODBC驱动mysql-connector-odbc-5.1.5-win32.msi（最好去官网下最新版本，否则对中文字符集支持的不好，我开始用的版本比较老，导进去之后，中文都是问号）

2.打开控制面板\管理工具\数据源ODBC，在用户DSN中添加一个MySQL ODBC 5.1数据源。

3.在登录login选项卡中输入数据源名称Data Source Name，此处输入MysqlDNS;然后输入服务器Server，用户User，密码Password，输入正确后选择要导入的数据库。在连接选项connect options中根据需要设置MySql使用的端口port和字符集Character Set。

4.打开sql server企业管理器，新建一数据库MySql。选择该数据库，单击右键选择所有任务\导入数据。

5.选择数据源为其它(ODBC数据源)，用户/系统DSN为MysqlDNS。其余根据向导进行，即可将数据从MySql数据库导入到MSSQL数据库中。

五、MySQL字符集

MySQL字符集一直比较乱，出的问题比较多，网上的解决方案也五花八门，我觉得这个主要是看rp。。。这里只简单介绍下相应的知识。

查看MySQL字符集的命令 mysql> show variables like '%char%';出现

+--------------------------+-------------------------------------+------

| Variable_name | Value |......

+--------------------------+-------------------------------------+------

| character_set_client | utf8 |...... -- 客户端字符集

| character_set_connection | utf8 |......

| character_set_database | utf8 |...... -- 数据库字符集

| character_set_filesystem | binary |......

| character_set_results | utf8 |......

| character_set_server | utf8 |...... -- 服务器字符集 | character_set_system | utf8 |......

| character_sets_dir | D:\MySQL Server 5.0\share\charsets\ |......

+--------------------------+-------------------------------------+------

这里除了character_set_system 没办法改以外，其他都可以用命令修改，如SET character_set_client='gbk';不过这种修改重启MySQL服务器以后就恢复成原来的了，需要到my.ini中修改default-character-set（有两处都要改）。

字符集不匹配导致中文乱码的问题比较多，网上说的解决方案也不定奏效，可以多修改几种编码方案都试一下，记得让MySQL，phpmyadmin,还有数据库的字符集一直，不确定字符集可以用上面介绍的命令查看。 总结：以上是完成MySQL导入SqlServer时碰到的问题，数据库由于配置较多，有时候会出现很诡异的问题，大家设置或者修改配置时一定得记得自己改了什么，还要把原始的那份备份下，以防出现意外错误。