选举结果Web应用数据库三表关联设计咨询及架构疑问

我的目标是开发一款展示本国选举结果的Web应用，数据涵盖每一场选举中各城市所有候选人的得票情况。

关联关系说明 #

• 一场选举对应多名候选人和多个城市
• 一名候选人参与多场选举，对应多个城市
• 一个城市参与多场选举，对应多名候选人

示例数据（上届总统选举第二轮） #

我该如何将选举、候选人、城市这三张表关联起来？是否可以创建如下的三表连接表？

create_table "results", force: :cascade do |t|
  t.integer "election_id", null: false
  t.integer "candidate_id", null: false
  t.integer "city_id", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["city_id"], name: "index_results_on_city_id"
  t.index ["candidate_id"], name: "index_results_on_candidate_id"
  t.index ["election_id"], name: "index_results_on_election_id"
end

但这种情况下，选举对应的城市维度统计信息（示例数据的第2、3、4列，即某场选举中该城市的登记选民数、投票人数、有效票数）应该存储在哪里？我曾设计过一个数据库架构，但该架构无法实现特定选举中某城市内某候选人得票结果的查询，因为城市与候选人之间未建立关联。

核心思路：拆分"城市-选举"统计与"候选人-城市-选举"得票数据 #

你需要把数据拆成两张关联表，分别存储城市级选举统计和候选人在城市的得票结果，既满足关联查询需求，又避免数据冗余。

1. 基础表定义 #

先确认三张基础表的核心结构（可根据业务补充字段）：

• elections：存储选举基本信息（ID、名称、类型、举办日期等）
• candidates：存储候选人信息（ID、姓名、党派、竞选口号等）
• cities：存储城市信息（ID、名称、所属地区、人口规模等）

2. 新增city_election_stats表存储城市维度统计 #

这张表专门存储某场选举中某个城市的整体投票数据，对应示例里的inscrits、votants、exprime字段：

create_table "city_election_stats", force: :cascade do |t|
  t.integer "election_id", null: false
  t.integer "city_id", null: false
  t.integer "inscrits", null: false # 登记选民数
  t.integer "votants", null: false # 投票人数
  t.integer "exprime", null: false # 有效票数
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  # 联合唯一索引，确保一个城市在一场选举中只有一条统计数据
  t.index ["election_id", "city_id"], name: "index_city_election_stats_on_election_city", unique: true
end

3. 用candidate_results表存储候选人得票数据 #

你之前设计的results表思路正确，可优化命名并添加得票数字段：

create_table "candidate_results", force: :cascade do |t|
  t.integer "election_id", null: false
  t.integer "candidate_id", null: false
  t.integer "city_id", null: false
  t.integer "votes", null: false # 候选人在该城市的得票数
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  # 联合唯一索引，确保一个候选人在某场选举的某个城市只有一条得票记录
  t.index ["election_id", "candidate_id", "city_id"], name: "index_candidate_results_on_election_candidate_city", unique: true
  # 单独索引优化各类查询性能
  t.index ["election_id"], name: "index_candidate_results_on_election_id"
  t.index ["candidate_id"], name: "index_candidate_results_on_candidate_id"
  t.index ["city_id"], name: "index_candidate_results_on_city_id"
end

4. 常用关联查询示例 #

• 查询某场选举中某城市的所有候选人得票及城市统计数据：

SELECT c.name, cr.votes, ces.inscrits, ces.votants, ces.exprime
FROM candidate_results cr
JOIN candidates c ON cr.candidate_id = c.id
JOIN city_election_stats ces ON cr.election_id = ces.election_id AND cr.city_id = ces.city_id
WHERE cr.election_id = 1 AND cr.city_id = 2;

• 查询某候选人在某场选举中各城市的得票情况：

SELECT ci.name, cr.votes
FROM candidate_results cr
JOIN cities ci ON cr.city_id = ci.id
WHERE cr.election_id = 1 AND cr.candidate_id = 3;

拆分的优势 #

• 避免数据冗余：如果把城市统计字段放进候选人得票表，每个候选人的记录都会重复存储相同的inscrits、votants数据，既浪费空间又容易出现数据不一致问题。
• 覆盖业务场景：既能单独查询城市整体投票数据，也能关联查询候选人得票，完全满足你需要的各类查询需求。

内容的提问来源于stack exchange，提问作者Lazare Boddaert