数据库设计
编者按：这是涉及可自由地获得的Linux DBMS的系列文章的第二部分。你可在这里找到系列的第一部分。
为了测试并比较3个数据库治理系统，我当然需要一个数据库来治理他们。在已读了《SQL傻瓜书》后，我有了一些如何设计现实的数据库的基本知识，因此我拣选了一非凡无聊的真实世界情形(一个因特网书店，其他？)并且写下了一个小型数据库设计以便探讨一点Sql的能力。
在这个数据库里有5个表，BOOK保存可得到的书的登记信息；CUSTOMER包含登记的顾客。对每份订单，BOOKORDER创建一行，参照其顾客。对每个定购的项目，在ORDER_POSITION里引用它的订单号。作为一种奖励，针对书的排名我增加了一RATING表。
下列的SQL代码可装入到一个数据库系统的SQL监控程序并且应该没有任何问题被接受。所有的桌子在创造前被掉落因此假如，手迹不会外面保释他们的一些已经存在。
DROP TABLE BOOK; CREATE TABLE BOOK ( ARTICLE_NO INTEGER PRIMARY KEY, AUTHOR_FIRST_NAMES CHARACTER(30), AUTHOR_LAST_NAMES CHARACTER(30), TITLE CHARACTER(30), ISBN CHARACTER(13) UNIQUE, WHOLESALE_PRICE NUMERIC(4,2), RETAIL_PRICE NUMERIC(4,2), COPIES_AVAILABLE INTEGER ); DROP TABLE CUSTOMER; CREATE TABLE CUSTOMER ( CUSTOMER_NO INTEGER PRIMARY KEY, FIRST_NAMES CHARACTER(30), LAST_NAMES CHARACTER(30), STREET CHARACTER(30), HOUSE_NO SMALLINT, POSTCODE CHARACTER(7), TOWN CHARACTER(30), ISO_COUNTRY_CODE CHARACTER(2) ); DROP TABLE BOOKORDER; CREATE TABLE BOOKORDER ( ORDER_NO INTEGER PRIMARY KEY, CUSTOMER_NO INTEGER NOT NULL, ORDERED DATE, DELIVERY DATE, STATUS CHARACTER(8) CHECK (STATUS IN ('ACCEPTED', 'DELAYED', 'SHIPPED', 'RETURNED', 'PAID')), CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO) REFERENCES KUNDE (KUNDENNAME) ); DROP TABLE ORDER_POSITION; CREATE TABLE ORDER_POSITION ( POSITION_NO INTEGER PRIMARY KEY, ORDER_NO INTEGER NOT NULL, ARTICLE_NO INTEGER NOT NULL, NUMBER SMALLINT, CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO) REFERENCES BOOKORDER (ORDER_NO), CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO) REFERENCES BOOK (ARTICLE_NO) ); DROP TABLE RATING; CREATE TABLE RATING ( RATING_NO INTEGER PRIMARY KEY, ARTICLE_NO INTEGER NOT NULL, SCORE NUMERIC(1,0), COMMENT CHARACTER VARYING(300), CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO) REFERENCES BOOK (ARTICLE_NO) );
当然，这是一个极其简单的数据库。它看上去真实，但是它不适用于真实世界的应。它不保存顾客记录或任何东西，并且它甚至没有书的出版商的列。它只是一个测试环境。
注重我不想花大气力强制CUSTOMER.ISO_COUNTRY_CODE为今天是实际有效的编码。我在代码做了一点限制以测试数据库系统是否接受他们；我没尝试使数据库无懈可击。
改变设计适应PostgreSQL
当我将遵循ANSI标准的CREATE TABLE语句装入Postgresql的psql监控视程序是，我碰到的困难是很少的。我得到一些警告：外部要害字限制被接受但还没有实现，而且我不得不裁减RATING的COMMENT字段到255个字符，因为这是PostgreSQL的CHARACTER VARYING类型的字段的最大字段宽度。系统为存储大量数据提供BLOB数据类型，但是它们不在标准版本内，因此我决定了不使用他们。另外的问题是相当愚蠢--因为我不能找到有关PostgreSQL如何强制NUMERIC到C数据类型，也因为我不想使用float以避免舍入，我决定使得货币字段为分值(cent)的整数数字。
我最后得到了这个略有不同的脚本：
DROP TABLE BOOK; CREATE TABLE BOOK ( ARTICLE_NO INTEGER PRIMARY KEY, AUTHOR_FIRST_NAMES CHARACTER(30), AUTHOR_LAST_NAMES CHARACTER(30), TITLE CHARACTER(30), ISBN CHARACTER(13) UNIQUE, WHOLESALE_PRICE INTEGER, RETAIL_PRICE INTEGER, COPIES_AVAILABLE INTEGER ); DROP TABLE CUSTOMER; CREATE TABLE CUSTOMER ( CUSTOMER_NO INTEGER PRIMARY KEY, FIRST_NAMES CHARACTER(30), LAST_NAMES CHARACTER(30), STREET CHARACTER(30), HOUSE_NO SMALLINT, POSTCODE CHARACTER(7), TOWN CHARACTER(30), ISO_COUNTRY_CODE CHARACTER(2) ); DROP TABLE BOOKORDER; CREATE TABLE BOOKORDER ( ORDER_NO INTEGER PRIMARY KEY, CUSTOMER_NO INTEGER NOT NULL, ORDERED DATE, DELIVERY DATE, STATUS CHARACTER(8) CHECK (STATUS IN ('ACCEPTED', 'DELAYED', 'SHIPPED', 'RETURNED', 'PAID')), CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO) REFERENCES KUNDE (KUNDENNAME) ); DROP TABLE ORDER_POSITION; CREATE TABLE ORDER_POSITION ( POSITION_NO INTEGER PRIMARY KEY, ORDER_NO INTEGER NOT NULL, ARTICLE_NO INTEGER NOT NULL, NUMBER SMALLINT, CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO) REFERENCES BOOKORDER (ORDER_NO), CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO) REFERENCES BOOK (ARTICLE_NO) ); DROP TABLE RATING; CREATE TABLE RATING ( RATING_NO INTEGER PRIMARY KEY, ARTICLE_NO INTEGER NOT NULL, SCORE SMALLINT, COMMENT CHARACTER VARYING(255), CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO) REFERENCES BOOK (ARTICLE_NO) );
使设计适应mysql
MySQL象PostgreSQL一样忽略外部要害字的限制，但是它搞了个UNIQUE限制。
最后的脚本与PostgreSQL脚本差不多：
DROP TABLE BOOK; CREATE TABLE BOOK ( ARTICLE_NO INTEGER PRIMARY KEY, AUTHOR_FIRST_NAMES CHARACTER(30), AUTHOR_LAST_NAMES CHARACTER(30), TITLE CHARACTER(30), ISBN CHARACTER(13), WHOLESALE_PRICE INTEGER, RETAIL_PRICE INTEGER, COPIES_AVAILABLE INTEGER ); DROP TABLE CUSTOMER; CREATE TABLE CUSTOMER ( CUSTOMER_NO INTEGER PRIMARY KEY, FIRST_NAMES CHARACTER(30), LAST_NAMES CHARACTER(30), STREET CHARACTER(30), HOUSE_NO SMALLINT, POSTCODE CHARACTER(7), TOWN CHARACTER(30), ISO_COUNTRY_CODE CHARACTER(2) ); DROP TABLE BOOKORDER; CREATE TABLE BOOKORDER ( ORDER_NO INTEGER PRIMARY KEY, CUSTOMER_NO INTEGER NOT NULL, ORDERED DATE, DELIVERY DATE, STATUS CHARACTER(8), CONSTRAINT CUSTOMER_FK FOREIGN KEY (CUSTOMER_NO) REFERENCES KUNDE (KUNDENNAME) ); DROP TABLE ORDER_POSITION; CREATE TABLE ORDER_POSITION ( POSITION_NO INTEGER PRIMARY KEY, ORDER_NO INTEGER NOT NULL, ARTICLE_NO INTEGER NOT NULL, NUMBER SMALLINT, CONSTRAINT ORDER_FK FOREIGN KEY (ORDER_NO) REFERENCES BOOKORDER (ORDER_NO), CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO) REFERENCES BOOK (ARTICLE_NO) ); DROP TABLE RATING; CREATE TABLE RATING ( RATING_NO INTEGER PRIMARY KEY, ARTICLE_NO INTEGER NOT NULL, SCORE NUMERIC(1,0), COMMENT CHARACTER VARYING(255), CONSTRAINT BOOK_FK FOREIGN KEY (ARTICLE_NO) REFERENCES BOOK (ARTICLE_NO) );
使设计适应 mSQL
因为mSQL是一个精简的数据库治理器(的确，有些人可能怀疑MySQL和mSQL是否是数据库治理系统)，它放弃了大多数Sql的功能而仅仅接受SQL的一个严格限制的子集。这样，mSQL的脚本看上有很大不同：
DROP TABLE BOOK CREATE TABLE BOOK ( ARTICLE_NO INTEGER NOT NULL, AUTHOR_FIRST_NAMES CHARACTER(30), AUTHOR_LAST_NAMES CHARACTER(30), TITLE CHARACTER(30), ISBN CHARACTER(13), WHOLESALE_PRICE MONEY, RETAIL_PRICE MONEY, COPIES_AVAILABLE INTEGER ) DROP TABLE CUSTOMER CREATE TABLE CUSTOMER ( CUSTOMER_NO INTEGER NOT NULL, FIRST_NAMES CHARACTER(30), LAST_NAMES CHARACTER(30), STREET CHARACTER(30), HOUSE_NO SMALLINT, POSTCODE CHARACTER(7), TOWN CHARACTER(30), ISO_COUNTRY_CODE CHARACTER(2) ) DROP TABLE BOOKORDER CREATE TABLE BOOKORDER ( ORDER_NO INTEGER NOT NULL, CUSTOMER_NO INTEGER NOT NULL, ORDERED DATE, DELIVERY DATE, STATUS CHARACTER(1) ) DROP TABLE ORDER_POSITION CREATE TABLE ORDER_POSITION ( POSITION_NO INTEGER NOT NULL, ORDER_NO INTEGER NOT NULL, ARTICLE_NO INTEGER NOT NULL, NUMBER SMALLINT ) DROP TABLE RATING CREATE TABLE RATING ( RATING_NO INTEGER NOT NULL, ARTICLE_NO INTEGER NOT NULL, SCORE SMALLINT, COMMENT TEXT(255) )
几乎所有的约束都不见了，并且NUMERIC和CHARACTER VARYING分别由MONEY和TEXT代替。
在mSQL的监视程序中有令人沮丧的不足：它似乎不能接受从标准输入输入SQL脚本，这样, 需要剪切/粘贴代码。mSQL也讨厌分号；最终我只能一个一个地输入命令并用\g（“go”斜杠命令)终止每条命令 。
实现测试客户
为了比较3个数据库治理器，我决定为执行在bookstore数据库上的交易的目的用C写了一个测试客户。结果，我实现了一些操作，它们能比较API。为了性能比较，我随后充分实现了它们，并且把一个非交互式模式加入客户程序，因此它能自己运行，产生随意的数据且随机执行交易。
我决定了在样品数据库上实现下列行动：
增加一本新书： INSERT INTO BOOK (...) VALUES (...);
删除一本存在的书： DELETE FROM BOOK WHERE ARTICLE_NO=...;
增加一个顾客： INSERT INTO CUSTOMER (...) VALUES (...);
删除一个顾客： DELETE FROM CUSTOMER WHERE CUSTOMER_NO=...;
订书的一个顾客： INSERT INTO BOOKORDER (...) VAL
|
