{"id":3337,"date":"2010-07-07T22:07:48","date_gmt":"2010-07-07T14:07:48","guid":{"rendered":"http:\/\/ihower.tw\/blog\/?p=3337"},"modified":"2011-04-09T01:49:03","modified_gmt":"2011-04-08T17:49:03","slug":"enterprise-rails-database","status":"publish","type":"post","link":"https:\/\/ihower.tw\/blog\/3337-enterprise-rails-database","title":{"rendered":"Enterprise Rails: \u8cc7\u6599\u5eab\u7bc7"},"content":{"rendered":"

<\/a><\/p>\n

\u7a3f\u5b50\u662f 2009 \u5e74\u5e95\u5beb\u7684\uff0c\u672c\u4f86\u60f3\u4e00\u53e3\u6c23\u9023 SOA \u7684\u90e8\u4efd\u4e00\u8d77\u6574\u7406 (\u5c0d SOA \u6709\u8208\u8da3\u7684\u8a71\uff0c\u53ef\u4ee5\u770b\u6211\u4e4b\u524d\u7684\u6295\u5f71\u7247 91 \u9801 ~ 119 \u9801<\/a>)\uff0c\u4e0d\u904e\u4e00\u76f4\u62d6\u5230\u73fe\u5728\u3002\u96d6\u7136\u5167\u5bb9\u9084\u662f\u6709\u9ede\u4e9b\u96dc\u4e82\uff0c\u800c\u4e14\u73fe\u5728\u6d41\u884c NoSQL (?)\uff0c\u4e0d\u904e\u9084\u662f\u5c31\u8cbc\u51fa\u4f86\u5427\u3002<\/p>\n

\u9019\u672c 2009 \u5e74\u5e95\u7684\u66f8\uff0c\u8d81\u8457\u8f49\u8077\u7684\u7a7a\u6a94\u7d42\u65bc\u4e00\u53e3\u6c23\u8b80\u5b8c\u4e86\u3002\u6709\u4e9b\u91cd\u8981\u7684\u80cc\u666f\u77e5\u8b58\uff0c\u5982\u679c\u4f60\u4e00\u76f4\u8ffd\u6240\u8b02\u7684\u6700\u65b0\u6280\u8853\uff0c\u53cd\u800c\u662f\u5b78\u4e0d\u5230\u3002\u9019\u672c\u66f8\u5176\u5be6\u8b1b Rails \u4e0d\u591a\uff0c\u524d\u4e00\u5927\u534a\u90fd\u662f\u5728\u8b1b RDBMS \u95dc\u806f\u5f0f\u8cc7\u6599\u5eab\uff0c\u5f8c\u534a\u5247\u662f SOA \u67b6\u69cb\u3002<\/p>\n

\u50cf RDBMS \u88e1\u9762\u7684 referential integrity\u3001composite key\u3001DB View\u3001triggers\u3001materialized View \u9019\u4e9b\u6771\u897f\uff0c\u56e0\u70ba\u8eab\u70ba MySQL \u4f7f\u7528\u8005\u5f88\u5c11\u7528\u5230\u3001Rails \u4e5f\u6c92\u6709\u5167\u5efa\u652f\u63f4\uff0c\u5be6\u969b\u5beb code \u597d\u50cf\u4e5f\u90fd\u5728 application layer \u61c9\u7528\u5c64\u8655\u7406\u6389\u4e86\uff0c\u6240\u4ee5\u5230\u5e95\u8981\u5e79\u561b\u7528\u7684\u5462? \u4ed6\u5011\u88ab\u767c\u660e\u4e00\u5b9a\u6709\u539f\u56e0\u5427? \u662f\u5728\u4ec0\u9ebc\u60c5\u5883\u4e0a\u4f7f\u7528\u5462?<\/p>\n

\u7b2c\u56db\u7ae0 Database As a Fortress<\/h3>\n

\u4f5c\u8005\u975e\u5e38\u5f37\u8abf\u8cc7\u6599\u5eab\u7684\u91cd\u8981\uff0c\u4e00\u5bb6\u4f01\u696d\u6700\u91cd\u8981\u7684\u8cc7\u7522\u662f\u8cc7\u6599\uff0c\u800c\u4e0d\u662f\u54e1\u5de5 (\u8cc7\u6599\u4e0d\u898b\u5c31\u5168\u5b8c\u4e86\uff0c\u54e1\u5de5\u6c92\u4e86\u518d\u627e\u5c31\u6709\u4e86)\u3002Framework \u6703\u8b8a\u3001\u7a0b\u5f0f\u6703\u6709 bug\u3001\u53ef\u80fd\u4e5f\u4e0d\u6703\u53ea\u6709 Rails \u6703\u53bb\u5b58\u53d6 DB (\u6240\u4ee5\u53ea\u7528 ActiveRecord Validation \u4e26\u4e0d\u53ef\u9760)\uff0c\u7e3d\u4e4b\u53ea\u6709\u4f60\u7684 RDBMS \u53ef\u4ee5\u4fdd\u969c\u8cc7\u6599\u7684\u6b63\u78ba\u6027\uff0cdata integrity \u9577\u5b58\u3002<\/p>\n

PostgreSQL \u4e5f\u6bd4 MySQL \u9069\u5408\u4f01\u696d\u61c9\u7528\uff0c\u56e0\u70ba\u5f88\u591a SQL \u6a19\u6e96 MySQL \u4e26\u4e0d\u652f\u63f4 (\u5f88\u591a\u76f4\u5230 5.0 \u5f8c\u624d\u652f\u63f4)\u3002PostgreSQL \u6703\u8f38\u6389\u5e02\u4f54\u7387\u7684\u539f\u56e0\u662f 1. MySQL \u80cc\u5f8c\u6709\u4e00\u5bb6\u5546\u696d\u516c\u53f8\u652f\u6301 2. PostgreSQL \u6709\u5f88\u9577\u4e00\u6bb5\u6642\u9593\u4e0d\u50cf MySQL \u6709\u63d0\u4f9b Windows \u5b89\u88dd\u5305 (\u8a71\u8aaa\u9019\u672c\u66f8\u5c0d MySQL \u5176\u5be6\u9084\u883b\u63f6\u63c4\u7684)\u3002<\/p>\n

\u4f5c\u8005\u4e5f\u4e0d\u5efa\u8b70\u4f7f\u7528 migration\uff0c\u56e0\u70ba\u5b83\u6c92\u6709\u652f\u63f4\u6240\u6709\u7684 DDL (Data Definition Language)\u3002\u53cd\u6b63\u53ea\u6709\u8dd1\u4e00\u6b21\uff0c\u4e0d\u50cf DML (Data Manipulation Language) \u6703\u4e00\u76f4\u7528\uff0c\u7528 ORM \u6bd4\u8f03\u65b9\u4fbf\u3002<\/p>\n

\u4f5c\u8005\u6301\u7e8c\u5f37\u8abf referential integrity \u5c0d\u4f01\u696d\u5c64\u7d1a\u7684\u4e0a\u7dda\u61c9\u7528\u7a0b\u5f0f\u975e\u5e38\u91cd\u8981\u3002\u6703\u8aaa\u4e0d\u91cd\u8981\u7684\uff0c\u4e00\u5b9a\u662f\u8a72\u6b7b\u7684 MySQL \u4f7f\u7528\u8005(died-hand MySQL users)\uff0c\u5343\u842c\u5225\u56e0\u70ba\u4f60\u7684\u5de5\u5177(i.e. MySQL, Rails)\u6c92\u6709\u9019\u500b\u529f\u80fd\uff0c\u5c31\u8aaa\u4e0d\u91cd\u8981\u3002<\/p>\n

\u96a8\u8457\u8cc7\u6599\u8d8a\u4f86\u8d8a\u591a\uff0c\u4f60\u7684\u8001\u95c6\u6703\u60f3\u8981\u77e5\u9053\u4e00\u4e9b\u6578\u64da\u5206\u6790\u5831\u8868\uff0c\u9019\u88e1\u4f5c\u8005\u9ede\u51fa\u4e00\u500b\u91cd\u8981\u7684\u8b70\u984c\uff1a\u4f60\u7684\u5831\u8868\u6703\u5bb3\u4e86\u4f60\u7684\u7db2\u7ad9!! \u4f60\u8d8a\u5e38\u53bb\u7522\u751f\u4f60\u7684\u5831\u8868\uff0c\u4f60\u7684\u7db2\u7ad9\u5c31\u6703\u8d8a\u4f86\u8d8a\u6162\u3002”Report are killing your site!!” \u70ba\u4ec0\u9ebc\u5462? \u56e0\u70ba\u53bb DB \u6488\u5927\u91cf\u7684\u8cc7\u6599\u505a\u5831\u8868\uff0c\u662f\u975e\u5e38\u9f90\u5927\u8907\u96dc\u7684 SQL \u64cd\u4f5c\uff0c\u4f60\u7684 DB \u7684\u6548\u80fd\u6703\u88ab\u62d6\u6162\uff0c\u6574\u500b\u7db2\u7ad9\u7684\u6548\u80fd\u5c31\u8ddf\u8457 down \u4e0b\u4f86\u3002\u8a72\u600e\u9ebc\u89e3\u5462? \u6488 report \u7684 queries \u4e0d\u8981\u5c0d production database \u505a\u5c31\u6c92\u4e8b\u4e86\u5427?<\/p>\n

\u9019\u88e1\u4f5c\u8005\u53c8\u5c0d MySQL users \u63f6\u63c4\u4e86\uff1a\u56e0\u70ba\u5927\u591a\u6578\u7684 MySQL users \u7b2c\u4e00\u500b\u60f3\u5230\u7684\u65b9\u6848\u5c31\u662f\u4f7f\u7528 master\/slave \u4f86\u89e3\u6c7a\uff0c\u4e5f\u5c31\u662f\u8b93 query report \u5c0d slave DB \u505a\uff0c\u4f46\u662f\u5373\u6642\u5982\u6b64\u4e0d\u6703\u5f71\u97ff\u5230 production \u904b\u4f5c\uff0c\u4e5f\u6c92\u8fa6\u6cd5\u89e3\u6c7a query \u67e5\u8a62\u5f88\u6162\u7684\u554f\u984c\u3002<\/p>\n

\u9019\u88e1\u89c0\u5ff5\u4e0a\u8981\u5340\u5206\u7684\u662f OLTP (Online Transaction Processing) \u548c OLAP (Online Analytical Processing \u9019\u5169\u7a2e\u985e\u578b\u7684 query\uff0c\u524d\u8005\u662f\u4e00\u822c\u7684\u8b80\/\u5beb\/\u66f4\u65b0\uff0c\u9019\u7a2e\u662f\u6703\u8b93\u4f7f\u7528\u8005\u53ef\u4ee5\u99ac\u4e0a\u7b49\u7d50\u679c\uff0c\u5927\u90e8\u5206\u7684\u524d\u53f0\u7db2\u7ad9\u5c31\u662f\u5c6c\u65bc\u9019\u7a2e\u3002\u5f8c\u8005\u5247\u662f\u4e00\u500b query \u5c31\u9808\u9700\u8981\u6536\u96c6\u4e0a\u767e\u842c\u7684\u8cc7\u6599\u53bb\u5206\u6790\uff0c\u4f8b\u5982\uff1a\u6709\u591a\u5c11\u5ba2\u6236\u8cb7\u4e86\u7522\u54c1A\uff0c\u53c8\u8cb7\u4e86\u7522\u54c1 B\uff0c\u4f9d\u7167\u5730\u9ede\u8ddf\u6642\u9593\u3002<\/p>\n

\u56e0\u70ba OLTP \u548c OLAP \u662f\u5982\u6b64\u4e0d\u540c\uff0c\u6c92\u9053\u7406 database \u7684\u8a2d\u8a08\u4e5f\u76f8\u540c\u3002OLAP \u8981\u5feb\uff0c\u5c31\u5fc5\u9808\u4f7f\u7528 denormalized \u975e\u6b63\u898f\u5316\u7684\u65b9\u5f0f\u4f86\u5b58\u653e\u8cc7\u6599\u3002\u4f46\u662f\u5c07\u6b63\u898f\u5316\u8cc7\u6599\u548c\u9006\u6b63\u898f\u5316\u8cc7\u6599\u6df7\u96dc\u5728\u540c\u4e00\u500b DB\uff0c\u5247\u975e\u5e38\u5bb9\u6613\u9020\u6210\u8cc7\u6599\u7684\u6df7\u4e82\u4e0d\u540c\u6b65\uff0c\u5beb\u51fa buggy \u6709\u81ed\u866b\u7684\u7a0b\u5f0f\u3002<\/p>\n

\u8981\u89e3\u6c7a\u9019\u500b\u554f\u984c\u7684\u9818\u57df\u662f\u4f7f\u7528\u7368\u7acb\u7684 data warehouse \u5b58\u653e denormalized \u7684\u8cc7\u6599\u3002(\u66f8\u5c31\u5230\u6b64\u6253\u4f4f\u4e86\uff0c\u4f5c\u8005\u63a8\u85a6\u4e86 The Data Warehouse Toolkit: The Complete Guide to dimensional Modeling<\/a> by Ralph Kimball \u4e00\u66f8)<\/p>\n

\u7b2c\u4e94\u7ae0 Building a Solid Data Model<\/h3>\n

Data layer \u5c64\u7d1a\u7684 constraint \u624d\u4fdd\u8b49\u4e00\u5b9a\u6b63\u78ba\uff0c\u56e0\u70ba\u61c9\u7528\u5c64\u7684 Model validation \u53ef\u4ee5\u8df3\u904e\uff0c\u653e\u5728 code \u88e1\u4e5f\u5bb9\u6613\u88ab\u6539\u6389\u3002\u4f5c\u8005\u4e5f\u793a\u7bc4\u4e86\u9019\u5169\u8005\u90fd\u53ef\u4ee5\u5beb\u55ae\u5143\u6e2c\u8a66\u3002<\/p>\n

referial constraint \u5982\u679c\u53ea\u7528 Model \u505a\uff0cdestroy \u6642\u5c31\u6703\u5931\u6548\u3002\u800c Model \u8981\u505a referial constraint \u53ea\u80fd\u7528 has_many :depentent \u4f46\u662f\u5982\u679c\u8aa4\u7528\u4e86 :delete \u5c31 orz \u4e86\u3002\u56e0\u6b64\u6700\u4fdd\u8b49\u7684\u4f5c\u6cd5\u9084\u662f data layer \u505a\u3002<\/p>\n

\u8cc7\u6599\u5eab\u8a18\u5f97\u52a0 index \u5728 1. foreign key 2. \u4efb\u4f55\u6709 SQL where \u689d\u4ef6\u7684\u5730\u65b9<\/p>\n

\u7b2c\u516d\u7ae0 Refactoring to Third Normal Form<\/h3>\n

3NF (\u4e09\u968e\u6b63\u898f\u5316) \u80fd\u505a\u5230 DB \u8cc7\u6599\u4e0d\u91cd\u8907\uff1a\u53ea\u8981\u4e0d\u662f primary key\u3001\u4e0d\u662f foreign key\u3001\u4e0d\u662f intrinsic data(eg. name)\u3001\u4e0d\u662f measured value (\u4f8b\u5982 time, temperature)\uff0c\u800c\u662f\u4e00\u500b literally bound data\uff0c\u90fd\u61c9\u8a72\u6b63\u898f\u5316\u51fa\u4f86\u65b0\u5efa\u4e00\u500b table\u3002\u4e0d\u5148\u505a\uff0c\u5f8c\u4f86\u8981\u52a0\u6703\u5341\u5206\u75db\u82e6\uff0c\u5c24\u5176\u5728\u4e00\u500b\u5df2\u7d93\u6709\u8cc7\u6599\u7684 production db \u4e0a\u3002<\/p>\n

\u7bc4\u4f8b\u4e2d\u7684\u6709\u5f88\u591a table \u90fd\u6709\u76f8\u540c\u7684\u5730\u5740\u6b04\u4f4d\u5b9a\u7fa9\uff1aPostgres \u652f\u63f4 multipie inheritance\uff0c\u53ef\u4ee5\u8655\u7406\u91cd\u8907\u7684 DDL\uff0c\u518d\u642d\u914d\u4e0a Rails \u53ef\u7528 plugin mixin \u8655\u7406\u91cd\u8907\u7684 code\u3002<\/p>\n

\u9019\u88e1\u7559\u4e0b\u4e00\u500b\u7b2c\u516b\u7ae0\u624d\u56de\u7b54\u7684\u554f\u984c\uff0c\u53ea\u6709\u4e00\u500b primary key \u6c92\u8fa6\u6cd5\u4e00\u6b21\u6488\u51fa has_many \u7684 has_many \u8cc7\u6599\u3002\u591a\u4fdd\u7559\u4e00\u500b foreign key \u53c8\u53ef\u80fd\u9020\u6210 direct \u548c in-direct \u7684\u8cc7\u6599\u4e0d\u4e00\u5b9a\u4e00\u81f4\u3002<\/p>\n

\u7b2c\u4e03\u7ae0 Domain Data<\/h3>\n

Domain data (\u6307\u7db2\u7ad9\u9810\u5148\u5c31\u6709\u7684\u5fc5\u8981\u521d\u59cb\u8cc7\u6599\uff0c\u53c8\u53eb\u505a seed data) \u4e5f\u61c9\u8a72\u4f7f\u7528 table \u5b58\uff0c\u56e0\u70ba 1. \u4fdd\u6301 referential integrity 2. \u7dad\u8b77 3ND \u8ddf\u64f4\u5145\u5f48\u6027\u3002\u5be6\u4f5c\u4e0a\u5247\u53ef\u4ee5\u505a\u6210\u5e38\u6578\u5f62\u5f0f\u3002<\/p>\n

strategy patterns with domain tables \u9019\u62db\u793a\u7bc4\u4e86\u5c07 Order PAYMENT_TYPE \u8b8a\u6210 domain tables \u4f86\u505a\uff0c\u9664\u4e86\u8b8a\u6210 constant object\uff0c\u60c5\u5883\u662f\u5982\u679c\u4e0d\u540c payment_type \u6703\u6709\u4e0d\u540c\u7684 validation\u3002\u9996\u5148\u8b8a\u6210 constant \u5f8c\uff0c\u53ef\u4ee5\u9806\u5229\u5c07 validation code \u90fd\u5f9e order \u642c\u8d70\u3002\u800c\u518d\u9032\u4e00\u6b65 rails single table inheritance \u548c template method \u5c07 validation \u5206\u6563\u5230\u500b\u5225\u7684 domain model\u3002\u7e3d\u4e4b\uff0c\u4f5c\u8005\u5c07 domain data \u7684\u5f48\u6027\u505a\u4e86\u975e\u5e38\u597d\u7684\u793a\u7bc4\u3002<\/p>\n

\u96d6\u7136\u6b63\u898f\u5316\u6703\u5c0e\u81f4\u5927\u91cf\u7684 table \u548c model\uff0c\u4f46\u662f\u4e5f\u56e0\u70ba\u5982\u6b64\u6bcf\u500b\u90fd\u5f88\u5c0f\u975e\u5e38\u5bb9\u6613\u6e2c\u8a66\uff0cbugs \u4e5f\u5c31\u5bb9\u6613\u96c6\u4e2d\u5728\u5c0f\u5340\u57df\u5bb9\u6613\u627e\u5230\u3002<\/p>\n

\u7b2c\u516b\u7ae0 Composite Keys and Domain Key\/Normal Form<\/h3>\n

\u9019\u7ae0\u8a0e\u8ad6 composite key \u7684\u512a\u7f3a\uff0c\u4ee5\u53ca\u5982\u4f55\u4f7f\u7528\u3002<\/p>\n

ID column \u7cfb\u7d71\u7684\u512a\u9ede 1. Rails \u5167\u5efa 2. \u7c21\u55ae\uff0c\u9664\u4e86 primary key \u4e4b\u5916\u7686\u53ef\u4fee\u6539\uff0c\u7269\u4ef6\u7684 primary key \u4e00\u5b9a\u4e0d\u8b8a 3. \u63d0\u4f9b\u8207\u771f\u5be6\u8cc7\u6599\u7684\u9593\u63a5\u6027\uff0c\u56e0\u6b64\u4e5f\u4e0d\u9700\u6709\u4fee\u6539 primary key \u7684\u6a5f\u6703\u3002 4. unique key \u597d\u505a<\/p>\n

composite key \u7684\u512a\u9ede\u5c31\u6c92\u9019\u9ebc\u986f\u800c\u6613\u898b\uff0c\u4e5f\u4e0d\u4e00\u5b9a\u7528\u7684\u5230\u3002\u4ed6\u7684\u7528\u9014\u5728\u65bc\u63d0\u4f9b\u4e00\u7a2e\u7279\u5225\u7684 data integrity\u3002table \u4e2d\u4e0d\u4e00\u5b9a\u53ef\u4ee5\u767c\u73fe\u6709 natural composite key, \u4f46\u662f\u5982\u679c\u6709\u800c\u4f60\u5ffd\u7565\u4ed6\uff0c\u53ef\u80fd\u6703\u6709\u5927\u554f\u984c\u3002\u9019\u500b\u60c5\u5883\u5c31\u662f\uff1a<\/p>\n

\u53ea\u6709\u4e00\u500b primary key \u6c92\u8fa6\u6cd5\u4e00\u6b21\u6488\u51fa has_many \u7684 has_many \u8cc7\u6599\uff0c\u5982\u679c\u53ea\u662f\u52a0\u4e0a\u4e00\u500b reference key, \u53ef\u80fd\u5c0e\u81f4 refential integrity hole. \u9019\u6642 composite key \u624d\u662f\u552f\u4e00\u89e3\u6c7a\u4e4b\u9053\u3002<\/p>\n

DFNF \u6bd4 3NF \u66f4\u8fd1\u4e00\u6b65\u4fdd\u8b49 referential integrity \u5728\u8907\u96dc\u7684 relationship \u4e2d\u3002\u4e0d\u80fd\u56e0\u70ba\u5de5\u5177\u6c92\u6709\uff0c\u5c31\u89ba\u5f97\u4e0d\u91cd\u8981\u3002\u56e0\u70ba Rails \u6c92\u6709\u5167\u5efa\uff0c\u5c0e\u81f4\u5f88\u591a\u4eba\u4e0d\u77e5\u9053 DK\/NF or natural keys \u7b49\u9019\u4e9b\u5df2\u7d93\u767c\u5c55\u6210\u719f\u7684\u8cc7\u6599\u5eab\u57fa\u790e\u7406\u8ad6\uff0c\u5728\u8a2d\u8a08 schema design \u4e0a\u800c\u6709\u5f88\u5927\u7684\u7f3a\u9677\u3002<\/p>\n

\u5148\u4f86\u6aa2\u8a0e single column ID \u662f\u5426\u61c9\u8a72\u7528 Rails \u5167\u5efa\u7684\u6578\u5b57\uff0c\u9996\u5148\u627e\u6709\u6c92\u6709\u5225\u7684 unique column\uff0c\u518d\u4f86\u89ba\u5f97\u5b83\u662f\u5426\u4e0d\u6703 \u6216 \u4e0d\u5e38\u8b8a\u66f4\uff0c\u7279\u5225\u662f\u9023\u7de8\u8f2f\u4ecb\u9762\u90fd\u6c92\u6709\u7684 domain data \u7279\u5225\u7b26\u5408\u9019\u500b\u689d\u4ef6\uff0c\u5982\u679c\u662f\uff0c\u5247\u53ef\u4ee5\u7528 set_primary_key \u63db\u6389\uff0c\u79fb\u9664\u4e0d\u5fc5\u8981\u91cd\u8907 id column\u3002\u4e0d\u904e\uff0c\u5982\u679c\u4e0d\u662f domain data, \u6211\u5011\u5c31\u5fc5\u9808\u7522\u751f\u9019\u500b primary key\uff0c\u7528\u6cd5\u662f\u5beb\u5728 before_create \u88e1\uff0c\u53e6\u5916\u8981\u6ce8\u610f\u9084\u662f\u4f7f\u7528 self.id\u3002\u4e00\u500b\u984d\u5916\u7684\u597d\u8655\u662f\uff0c\u9019\u4e9b\u53ef\u80fd\u88ab\u7576\u505a foreign_key \u7684 nature key\uff0c\u4e5f\u662f\u6709\u7528\u7684\u8cc7\u8a0a\uff0c\u4e0d\u50cf\u672c\u4f86\u7684\u6578\u5b57 id \u4e00\u5b9a\u9084\u8981\u53bb\u672c\u4f86\u7684 table \u67e5\u3002<\/p>\n

\u9019\u88e1\u6211\u6709\u500b\u7591\u554f\u662f Is there a REAL performance difference between INT and VARCHAR primary keys?<\/a> \uff0c\u5728\u6211\u78b0\u5230\u7684\u4f8b\u5b50\u662f\uff0c\u5f88\u591a\u4eba\u64d4\u5fc3\u975e integer \u7576 key \u6703\u5f71\u97ff\u6548\u80fd :\/ (\u984c\u5916\u8a71\uff0c\u5f88\u591a\u4eba\u611b\u7528 type code\uff0c\u4f46\u90fd\u88ab\u6211\u5efa\u8b70\u6539\u6210 string constant ) \u4f46\u662f\u6211\u60f3\u9019\u5dee\u8ddd\u7684\u5fae\u4e4e\u5176\u5fae\uff0c\u5c24\u5176\u5728\u4f60\u6c92\u6709\u4e0a\u767e\u842c\u7684\u8cc7\u6599\u5217\u3002\u91cd\u9ede\u9084\u662f\uff0c\u4f60\u9078\u7684 nature key \u4e0d\u9700\u8981\u6709\u88ab\u4fee\u6539\u7684\u53ef\u80fd :><\/p>\n

Rails \u8981\u652f\u63f4 composite keys \u6709\u5169\u500b\u65b9\u6cd5\uff0c\u4e00\u500b\u662f\u4f7f\u7528 Dr. Nic Williams \u7684 plugin\uff0c\u4e00\u500b\u662f\u672c\u66f8\u4f5c\u8005\u7684 Rails-DK\/NF hybrid \u6cd5\uff1a<\/p>\n

\u5177\u9ad4\u7684\u4f5c\u6cd5\u662f\uff0c\u4fdd\u7559 ID column \u6b04\u4f4d\uff0c\u4f46\u662f DB \u9084\u662f\u52a0\u4e0a composite key \u7684 foreign key referential integrity \u9650\u5236\u3002\u597d\u8655\u5728 Rails \u88e1\u9762\u4e0d\u65b9\u4fbf\u6539 primary key, \u7576\u6709\u4fee\u6539\u7684\u9700\u6c42\u6642\uff0c\u4f7f\u7528 hybird \u6cd5\u5c31\u4e0d\u932f\u7c21\u55ae(\u4e0d\u9700\u8981\u88ddplugin)\u3002\u4f46\u662f\u56de\u904e\u982d\u60f3\uff0c\u5982\u679c\u4f60\u9700\u8981\u4fee\u6539 nature key\uff0c\u53ef\u4ee5\u5148\u60f3\u60f3\u662f\u4e0d\u662f\u6700\u597d\u7684\u4f5c\u6cd5\u61c9\u8a72\u662f\u522a\u6389\u820a\u7684\uff0c\u63d2\u5165\u65b0\u7684\u3002<\/p>\n

\u4e00\u500b\u5c0f\u6280\u5de7\u662f\u9644\u5beb\u6389\u672c\u4f86\u7684 writor\uff0c\u9019\u6a23\u5c31\u4e0d\u9700\u8981\u624b\u52d5\u8a2d\u5b9a composite key \u7684\u503c\u4e86\u3002\u5982\u679c\u8981\u6539\u5df2\u7d93\u88ab reference constraint \u9650\u5236\u7684 composite keys \u600e\u9ebc\u8fa6? \u76f4\u63a5\u6539\u6703\u7206\uff0c\u9019\u6642\u5019\u9700\u8981\u4f7f\u7528 deferrable constraints \u7684\u6a5f\u5236\u548c transaction\u3002<\/p>\n

\u4e0d\u904e hybrid \u6cd5\u7684\u7f3a\u9ede\u662f 1. \u56e0\u70ba\u7dad\u8b77\u5169\u5957 index key \u7684\u95dc\u4fc2, \u65b0\u589e\u4fee\u6539\u522a\u9664\u7684 index cost \u6bd4\u8f03\u9ad8\u3002 2. \u9700\u8981\u591a\u5beb\u4e0a\u8ff0\u7684 code \u624d\u80fd\u7701\u6389\u624b\u52d5\u8a2d\u8a08\u7684\u9ebb\u7169\u3002<\/p>\n

\u66f8\u6c92\u5beb\u54ea\u500b\u6700\u597d\uff0c\u770b\u8d77\u4f86\u662f\u5982\u679c\u975e\u5f97\u6709\u4fee\u6539 nature key \u7684\u9700\u6c42\u624d\u53ea\u7528 hybrid \u6cd5\u3002<\/p>\n

\u7b2c\u4e5d\u7ae0 Guaranteeing Complex Relationships with Triggers<\/h3>\n

stored procedure \u548c triggers
\n\u4f7f\u7528 PL\/pgSQL \u505a\u4f8b\u5b50\u3002<\/p>\n

\u7b2c\u5341\u7ae0 Multiple Table Inheritance<\/h3>\n

Rails \u7684 polymorphic associations \u529f\u80fd\u8b93\u4f60\u53ef\u4ee5\u5b9a\u7fa9\u5169\u500b table \u7684\u95dc\u4fc2\uff0c\u4e0d\u9700\u8981\u4e8b\u524d\u77e5\u9053\u662f\u54ea\u4e00\u500b table\u3002<\/p>\n

\u4e0d\u904e\uff0cpolymorphic associations \u9055\u53cd\u4e86 referential integrity !! \u539f\u56e0\u5f88\u7c21\u55ae\uff0c\u65e2\u7136\u4e0d\u77e5\u9053 _id \u6703\u6307\u5230\u54ea\u500b table\uff0c\u81ea\u7136\u4e5f\u5c31\u6c92\u8fa6\u6cd5\u5728 DB layer \u52a0\u4e0a foreign key constraint<\/p>\n

\u4ec0\u9ebc\u662f\u591a\u578b? <\/p>\n

\u8981\u9054\u6210\u591a\u578b\u7684\u65b9\u5f0f\uff0c\u5be6\u4f5c\u4e0a\u8981\u8003\u616e\u7684\u662f STI \u6216 MTI\uff1a\u524d\u8005 Rails \u6709\u5167\u5efa\uff0c\u5f8c\u8005 Rails \u6709\u7528\u4e86 polymorphic \u65b9\u5f0f\u4f86\u9054\u6210 XOR relationships\uff0c\u4e5f\u7b97\u662f\u4e00\u7a2e\u7c21\u6613\u7684 MTI\u3002<\/p>\n

\u9019\u672c\u66f8\u7528\u4e86 logical \u548c physical models \u4f86\u5206\u5225\u63cf\u8ff0\u6982\u5ff5\u4e0a\u548c\u5be6\u969b\u4e0a\u7684\u5207\u6cd5<\/p>\n

STI \u9069\u7528\u65bc subclass \u5171\u7528\u5f88\u591a data\u3002\u5982\u679c\u5171\u7528\u7684\u4e0d\u591a\uff0c\u9664\u4e86\u6d6a\u8cbb table, model \u4e5f\u6703\u88ab getter \u548c setter \u6c61\u67d3\u3002\u53e6\u5916\u56e0\u70ba class name \u5beb\u5728 table \u4e86\uff0c\u5982\u679c\u8981\u4fee\u6539 class name \u6703\u5f88\u9ebb\u7169\u3002<\/p>\n

\u9019\u88e1\u63d0\u7684 MTI \u4f5c\u6cd5\u662f\uff0c\u9084\u662f\u70ba\u4e86\u8981\u7528\u5230\u7684 foreign key \u958b\u6b04\u4f4d\uff0c\u4e26\u4e14\u4e5f\u70ba\u6bcf\u500b\u95dc\u4fc2\u52a0\u4e0a belongs_to\uff0c\u4f46\u662f\u5141\u8a31 null \u503c\uff0c\u56e0\u70ba\u8981\u505a XOR on columns: \u8981\u7528 boolean \u505a XOR\uff0c\u5169\u500b\u9084\u597d\u505a\uff0c\u8d85\u904e\u4e09\u500b\u5c31\u8907\u96dc\u4e86\u3002\u9019\u88e1\u7684\u4f5c\u6cd5\u5f88\u8070\u660e\uff0c\u8f49\u6210\u6578\u5b57\u518d\u76f8\u52a0\uff0c\u53ea\u8981\u6aa2\u67e5\u662f\u4e0d\u662f\u7b49\u65bc 1\uff0c\u7528\u9019\u6a23\u7684\u65b9\u6cd5\u505a database constrant check\u3002<\/p>\n

\u90fd\u52a0\u4e86 belongs_to\uff0c\u8981\u600e\u9ebc\u505a\u5230\u591a\u578b\u3002\u9019\u88e1\u4f5c\u8005\u4f7f\u7528\u4e86 reflection \u548c inheritance relationship \u7684\u6280\u5de7\uff0c\u5728 parent object \u4e0a\u5be6\u4f5c\u4e86 getter \u548c setter method\uff0c\u53b2\u5bb3\u3002\u6211\u5011\u4e5f\u53ef\u4ee5\u5be6\u4f5c\u4e00\u500b Factory method \u5728 parent \u4e0a\uff0c\u4f86\u5efa\u69cb\u9069\u5408\u7684 sub-class\u3002<\/p>\n

\u7b2c11\u7ae0 View-Backed Models<\/h3>\n

\u6709\u4e9b\u8907\u96dc\u7684 join \u548c conditions \u55ae\u9760 active record \u5beb\u4e0d\u51fa\u4f86(\u7562\u7adf\u7c21\u55ae\u597d\u7528\u7684\u6771\u897f\uff0c\u7562\u7adf\u662f\u72a7\u7272\u4e00\u4e9b\u4e0d\u5e38\u4f7f\u7528\u7684\u529f\u80fd)\uff0c\u800c\u5fc5\u9808\u5beb\u4e00\u4e9b\u5f88\u919c\u7684 SQL \u6df7\u96dc active record\uff0c\u4f46\u662f\u9019\u6a23\u5c31\u5931\u53bb\u4f7f\u7528 active record \u7684\u610f\u7fa9(\u8207DB-independent)\u3002\u5982\u679c\u8981\u6f02\u4eae\uff0c\u53ea\u80fd\u5148\u76e1\u53ef\u80fd\u5148\u6488\u51fa\u4f86\uff0c\u7136\u5f8c\u518d\u5f9e application layer \u5c64\u904e\u6ffe\u8cc7\u6599\uff0c\u4f46\u662f\u9019\u6a23\u53c8\u5931\u53bb\u6548\u80fd\uff0c\u7562\u7adf\u6488\u8cc7\u6599\u662f DB \u7684\u5c08\u9577\u3002<\/p>\n

\u89e3\u6cd5\u662f view-backed model\uff0cmaterialized view\u3002<\/p>\n

database view \u6709\u5169\u500b\u5b9a\u7fa9 1. named subquery 2. a table that is defined by an algorithm
\n\u5f8c\u8005\u7684\u5be6\u4f5c\u662f\uff0c\u6211\u5011\u5148\u7528 SQL \u5b9a\u7fa9 DB view\uff0c\u7136\u5f8c\u5c31\u53ef\u4ee5\u7528 ActiveRecord \u4ee3\u8868\u90a3\u500b view\/table\uff0c\u66f4 cool \u7684\u662f\uff0c\u9084\u53ef\u4ee5\u52a0\u4e0a association!! \u7576\u7136\uff0c\u9019\u662f read-only \u7684\uff0c\u8a18\u5f97\u4e0d\u8981\u52a0 destroy\u3002<\/p>\n

subquery \u7684\u5b9a\u7fa9\u4e5f\u8868\u793a\uff0c\u4f60\u4e0d\u80fd\u5c0d view\/ view-backed models \u64cd\u4f5c insert\/update\/delete\/reference\/constraint\/index\u3002
\nreferences \u4e5f\u4e0d\u9700\u8981\uff0c\u5982\u679c\u4f60\u8981 reference\uff0c\u5c31\u8868\u793a\u4f60\u61c9\u8a72\u52a0\u5230 view \u88e1\uff0cconstraint \u4e5f\u662f\u3002<\/p>\n

indexing \u4e5f\u7121\uff0c\u7562\u7adf\u662f just-in-time \u6488\u51fa\u4f86\u7684\u8cc7\u6599\u3002\u4f46\u662f\u672c\u4f86\u7684 index \u6b04\u4f4d\u7576\u7136\u9084\u662f\u6709\u4f5c\u7528\u3002\u5225\u6c23\u9912\uff0c\u7562\u7adf\u672c\u4f86\u4f7f\u7528 View \u7684\u76ee\u7684\u4e0d\u662f\u6548\u80fd\uff0c\u800c\u662f\u70ba\u4e86\u8b93 AR \u66f4\u7c21\u6f54\u6e05\u695a\u3002\u5982\u679c\u771f\u7684\u9700\u8981\u6548\u80fd\uff0c\u4e0b\u4e00\u7ae0\u7684 materialized view\u3002<\/p>\n

\u7b2c12\u7ae0 Materialized Views<\/h3>\n

materialized view \u5c31\u662f cached \u7684 view\u3002\u65e2\u7136\u662f cache\uff0c\u5c31\u4e00\u6a23\u6709 cache \u7684\u8b70\u984c: \u8cc7\u6599\u7684\u66f4\u65b0\uff0c\u4f55\u6642\u7531\u8ab0 expire?<\/p>\n

materialized view \u662f cache-complete copy of view, \u4e5f\u662f\u5168\u90e8\u90fd\u6709\u7559\u8cc7\u6599\uff0c\u4e0d\u50cf memcached \u6703\u628a\u4e0d\u5e38\u7528\u6216\u8d85\u904e\u8a18\u61b6\u9ad4\u7684\u8cc7\u6599\u79fb\u9664\u3002<\/p>\n

\u5e7e\u500b\u91cd\u9ede: 1. materialized view \u65e2\u7136\u662f\u5be6\u9ad4\u7684 table\uff0c\u4e5f\u5c31\u53ef\u4ee5\u52a0 index 2. \u8981\u52a0\u4e0a refresh function \u7576\u5075\u6e2c\u5230 base table \u6709\u4fee\u6539 3. \u6216\u662f invalidation function \u5982\u679c\u4e0d\u60f3\u99ac\u4e0a\u66f4\u65b0 4. \u5728 base table \u52a0\u4e0a triggers \u4ee5\u5075\u6e2c\u4fee\u6539 5. auxiliart view, reconciler view \u96b1\u85cf\u5be6\u4f5c\u7d30\u7bc0<\/p>\n

\u4e0d\u904e\u770b\u5b8c\u9084\u662f\u89ba\u5f97\u4f7f\u7528 materialized view \u771f\u662f\u633a\u96e3\u7684!! XD<\/p>\n","protected":false},"excerpt":{"rendered":"

\u7a3f\u5b50\u662f 2009 \u5e74\u5e95\u5beb\u7684\uff0c\u672c\u4f86\u60f3\u4e00\u53e3\u6c23\u9023 SOA \u7684\u90e8\u4efd\u4e00\u8d77\u6574\u7406 (\u5c0d SOA \u6709\u8208\u8da3\u7684\u8a71\uff0c\u53ef\u4ee5\u770b\u6211\u4e4b\u524d\u7684\u6295 … <\/p>\n

\u95b1\u8b80\u5168\u6587\u3008Enterprise Rails: \u8cc7\u6599\u5eab\u7bc7\u3009<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[38,53],"tags":[],"class_list":["post-3337","post","type-post","status-publish","format-standard","hentry","category-database","category-rails","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p1q6tG-RP","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts\/3337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/comments?post=3337"}],"version-history":[{"count":69,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts\/3337\/revisions"}],"predecessor-version":[{"id":5515,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/posts\/3337\/revisions\/5515"}],"wp:attachment":[{"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/media?parent=3337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/categories?post=3337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ihower.tw\/blog\/wp-json\/wp\/v2\/tags?post=3337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}