另一個小型應用 • PostgreSQL 的藝術

先前介紹陣列時，我們用過一份 20 萬筆美國地理定位推文的資料集，其資料模型直接照搬 Excel 試算表格式——單一張大表，用 psql 的 \copy 就能直接載入。

begin;

create table tweet
 (
   id        bigint primary key,
   date      date,
   hour      time,
   uname     text,
   nickname  text,
   bio       text,
   message   text,
   favs      bigint,
   rts       bigint,
   latitude  double precision,
   longitude double precision,
   country   text,
   place     text,
   picture   text,
   followers bigint,
   following bigint,
   listed    bigint,
   lang      text,
   url       text
 );

\copy tweet from 'tweets.csv' with csv header delimiter ';'

commit;

這個模型哪裡違反了正規化#

以先前介紹的正規化形式（normal forms）檢驗，這個模型全都不及格：

違反 1NF：沒有唯一約束（unique constraint）也沒有主鍵，無法防止重複資料插入。
違反 2NF：非鍵屬性不依賴於鍵——推文本身與發文帳號的資料混在同一張表，如 nickname、bio、picture、followers、following、listed 都是使用者屬性。
違反 3NF：存在遞移相依（transitive dependency）——
- country 與 place 依賴於位置資訊，應拆到獨立資料表（例如前面「反正規化資料型別」一章用過的 geonames 資料）；
- hour 依賴於 date，單獨的小時無法表達推文發送時間。
longitude 與 latitude 其實應合併為單一 location 欄位——PostgreSQL 有幾何資料型別 point 可以直接用。

不遵守正規化形式會直接傷害應用程式效能：使用者每次修改 bio，就得回頭更新他發過的每一則推文；若只讓新推文帶新 bio，查詢舊推文時要取得目前 bio 又變得昂貴。

從並行的角度看，正規化的 schema 也有助於避免生產環境中頻繁發生「多個交易同時更新同一批資料列」的情況。

重寫 schema：第一步#

把使用者與訊息拆開，並移除 country、place（改由 geonames schema 維護）：

begin;

create schema if not exists tweet;

create table tweet.users
 (
   userid     bigserial primary key,
   uname      text not null,
   nickname   text not null,
   bio        text,
   picture    text,
   followers  bigint,
   following  bigint,
   listed     bigint,

   unique(uname)
 );

create table tweet.message
 (
   id        bigint primary key,
   userid    bigint references tweet.users(userid),
   datetime  timestamptz not null,
   message   text,
   favs      bigint,
   rts       bigint,
   location  point,
   lang      text,
   url       text
 );

commit;

進一步：以關聯表取代統計欄位#

followers、following、listed 只是「我們本來該有、卻沒有的資訊」的摘要值——原始資料集偏統計用途的簡化 schema 不該限制我們的設計。更好的做法是用關聯表記錄「誰追蹤誰」「誰把誰列入哪個清單」：

begin;

create schema if not exists tweet;

create table tweet.users
 (
   userid     bigserial primary key,
   uname      text not null,
   nickname   text,
   bio        text,
   picture    text,

   unique(uname)
 );

create table tweet.follower
 (
   follower   bigint not null references tweet.users(userid),
   following  bigint not null references tweet.users(userid),

   primary key(follower, following)
 );

create table tweet.list
 (
   listid     bigserial primary key,
   owner      bigint not null references tweet.users(userid),
   name       text not null,

   unique(owner, name)
 );

create table tweet.membership
 (
   listid     bigint not null references tweet.list(listid),
   member     bigint not null references tweet.users(userid),
   datetime   timestamptz not null,

   primary key(listid, member)
 );

create table tweet.message
 (
   messageid  bigserial primary key,
   userid     bigint not null references tweet.users(userid),
   datetime   timestamptz not null default now(),
   message    text not null,
   favs       bigint,
   rts        bigint,
   location   point,
   lang       text,
   url        text
 );

commit;

有了這個模型，接下來就能開始實際操作資料了。