zk投票选举_在选举中投票解释SQL表

zk投票选举

by Kevin Kononenko

凯文·科诺年科(Kevin Kononenko)

在选举中投票解释SQL表 (SQL Tables Explained by Voting in an election)

如果您之前投票过，那么您可以使用这种非政治性的类比来理解SQL表。 (If you have voted before, then you can understand SQL tables using this wonderfully non-political analogy.)

After this particularly controversial election, you probably know more about the U.S. voting system than you would ever want to.

在这场特别有争议的选举之后，您可能比您想了解的更多。

But I bet that you never thought it would help you learn how to organize a relational database.

但是我敢打赌，您从没想过它会帮助您学习如何组织关系数据库。

Fortunately, the basic rules of presidential elections are actually great guidelines to organize SQL tables and understand the concept of keys. Keys are the most important tool for setting up scalable and efficient SQL databases. They are also the most confusing part when you are just starting!

幸运的是，总统选举的基本规则实际上是组织SQL表和理解键的概念的重要准则。 密钥是设置可扩展且高效SQL数据库的最重要工具。 当您刚开始时，它们也是最令人困惑的部分！

All you need to know before you read this article are the very basics of SQL- mainly, how tables are organized by rows and columns.

在阅读本文之前，您需要了解的只是SQL的基础知识，主要是表如何按行和列进行组织。

回到选举日 (Rewind to Election Day)

You have just left your voting booth with paper ballot in hand. You head over to the processing machine, and put it in the slot.

您刚刚拿着纸票离开了投票站。 您转到处理机，然后将其放入插槽。

But what happens next? What do you think would be the best way to store your vote for president? Remember that about 136,000,000 other people voted as well in just this election!

但是接下来会发生什么呢？ 您认为存储总统的选票的最佳方法是什么？ 请记住，在这次选举中大约还有136,000,000人也投票了！

Check out the ballot above. You can see that there are three key identifiers that we need to track on the ballot:

查看上面的选票。 您可以看到我们需要在选票上跟踪三个关键标识符：

1. The election that this ballot is associated with (2016)
   该选票相关的选举(2016)
2. A ballot number (012)
   投票号码(012)
3. A voter ID, in this case, Social Security Number (012 34 5678)
   选民ID，在这种情况下为社会安全号码(012 34 5678)

Your initial thought might be to store this whole thing as one entry, like this:

您最初的想法可能是将整个内容存储为一个条目，如下所示：

But let’s think this through, considering that there are over 100 million other voters! Do we really need to store every piece of information on the ballot in every entry in the database? For example, do we really need to list “candidateA” and “candidateB” as the two choices 100 million times? Surely not!

但是，考虑到还有超过一亿选民，让我们仔细考虑一下！ 我们是否真的需要将选票上的每条信息都存储在数据库的每个条目中？ 例如，我们是否真的需要将“ candidateA”和“ candidateB”列出为1亿次的两个选择？ 当然不会！

And this does not even consider multiple elections. Imagine if we wanted to view Jennifer Hardy’s voting activity from the past 3 elections. Even then, it would not make sense to have 3 entries in our database that included her full name, SSN, gender etc. with every vote. This encourages bugs and inconsistent information when you update your database to change her party affiliation, for example.

而且这甚至不考虑多次选举。 想象一下，如果我们想查看过去3次选举中詹妮弗·哈迪(Jennifer Hardy)的投票活动。 即使这样，每次投票在我们的数据库中保留3个条目(包括她的全名，SSN，性别等)也是没有意义的。 例如，当您更新数据库以更改其党派隶属关系时，这会导致错误和信息不一致。

Let’s take a step back and consider some basic principles of the voting system.

让我们退后一步，考虑一下投票系统的一些基本原理。

1. One election has many votes (100 million+)

   一次选举有很多票 (超过1亿)

2. One voter has many votes over the course of their lifetime

   一名选民一生中有很多票

3. One voter can participate in many elections (as many as 20)

   一名选民可以参加许多选举 (多达20个)

Our three unique IDs- ballot #, social security number, election date- show that there are really three components in this system. If we put all three components in one entry, like we did above, we cannot capture the relationships between them. And we will repeat a lot of information.

我们的三个唯一ID(投票号，社会保险号，选举日期)表明，该系统中确实包含三个部分。 如果像上述那样将所有三个组件放在一个条目中，我们将无法捕获它们之间的关系。 我们将重复很多信息。

连接多个表 (Connecting Multiple Tables)

With our three key identifiers, we have now outlined the three tables that are needed to properly store this information.

使用我们的三个关键标识符，我们现在概述了正确存储此信息所需的三个表。

1. Election table, with a date, Party A nominee and Party B nominee. Date is the unique identifier because some candidates may run for President twice, but two elections never happen on the same date.

   选举表，带有日期，甲方提名人和乙方提名人。 日期是唯一的标识符，因为某些候选人可能会竞选总统两次，但两次选举从未在同一日期举行。

2. Vote table, that includes the ballot number, the SSN of the voter that it is associated with, the election that the ballot is associated with, the issue that is being voted on (president), and a 0 or 1 for which candidate was chosen. 0 stands for Party A, 1 for Party B due to alphabetical order.

   投票表，其中包括选票编号，与之相关的选民的SSN，与选票相关的选举，正在投票的问题(总统)以及为候选人选择的0或1 。 由于字母顺序，0代表甲方，1代表乙方。

3. Voter table, which includes the voter’s first name, last name, Social Security #, year of birth, party affiliation, gender and state registered to vote.

   选民表，其中包括选民的名字，姓氏，社会保险号，出生年月，政党所属，性别和登记投票的州。

This does a much better job of describing the different pieces of the voting system. There are actually three distinct events.

这在描述投票系统的不同部分方面做得更好。 实际上有三个不同的事件。

• You had to register to vote at some point before voting day! You were added to the voter database. Your SSN is the primary key, in this case. That means that it is the unique identifier for that row.

  您必须在投票日之前的某个时间注册才能投票！ 您已添加到选民数据库。 在这种情况下，您的SSN是主键 。 这意味着它是该行的唯一标识符。

• Each party held a National Convention to finalize their candidates in July 2016 for the November 8 election. This created a new entry in the election table. The date is the primary key.

  每个政党在2016年7月举行了一次国民大会，以最终确定11月8日选举的候选人。 这在选举表中创建了一个新条目。 日期是主键 。

Now we are back to the original scenario. It’s November 8, 2016. You just put your ballot in the processing machine. So how should the machine really handle your ballot? First, your ballot should have the minimum amount of information necessary.

现在我们回到原始场景。 现在是2016年11月8日。您只需将选票放入处理机即可。 那么，机器应该如何真正处理您的选票？ 首先，您的选票应具有必要的最少信息量。

The machine then needs to answer a few questions to determine if the ballot is valid.

然后，机器需要回答一些问题以确定投票是否有效。

• Have you already voted in this election?
  您已经在这次选举中投票了吗？
• Is the ballot connected to the 2016 election, or did you save one from a past election?
  选票与2016年大选相关吗，还是您从上次大选中省了一把？
• Are you a registered voter?
  您是注册选民吗？

Each one of these is a separate SQL query. I want to focus on the first three pieces of info on the ballot above. The ballot ID is the primary key for the votes table. The SSN and date are actually foreign keys. That means that they reference primary keys from the other two tables.

其中的每一个都是单独SQL查询。 我想集中讨论上面选票上的前三部分信息。 投票ID是投票表的主键 。 SSN和日期实际上是外键。 这意味着它们引用了其他两个表中的主键。

If we want to check if the ballot is coming from a registered voter, we are going to need to use a table join. A join references info from multiple tables using the primary/foreign key system. This means that our vote table must store both a unique identifier for the voter, and for the election as a whole. But it also means that we do not need to store all the voter’s info or the election info in that row! We just need a reference to the corresponding table.

如果我们要检查选票是否来自已注册的选民，则需要使用表join 。 联接使用主/外键系统从多个表中引用信息。 这意味着我们的投票表必须既存储投票者的唯一标识符，又存储整个选举的唯一标识符。 但这也意味着我们不需要在该行中存储所有选民信息或选举信息！ 我们只需要引用相应的表即可。

The primary/foreign key system forms connections between tables.

主/外键系统在表之间形成连接。

We want to check if Jennifer Hardy is a registered voter after a ballot with Hardy’s name is submitted. We just need to confirm that she has an entry in the voters table. We use the SSN foreign key from the votes table, and the primary key from the voters table to link the two records. We also probably need to check if the state registered on her record matches the state where the vote was processed.

在提交带有哈代名字的选票后，我们要检查詹妮弗·哈迪是否是注册选民。 我们只需要确认她在选民表中有一个条目即可。 我们使用投票表中的SSN 外键和投票器表中的主键来链接两个记录。 我们可能还需要检查记录在她的记录中的状态是否与处理投票的状态相匹配。

And that is it! If you want to practice, try and set up a database with the last 4 US presidential elections. Add 20 sample votes from 10 voters over the course of the 4 elections. And see if you can write the query that will check if a voter is attempting to submit a 2nd ballot in one election!

就是这样！ 如果您想练习，请尝试建立最近4次美国总统选举的数据库。 在这4次选举中，从10个选民中增加20个样本投票。 看看您是否可以编写查询来检查选民是否试图在一次选举中提交第二张选票！

If you enjoyed this post, you may also enjoy my other explanations of challenging CSS and JavaScript topics, such as positioning, Model-View-Controller, and callbacks.

如果您喜欢这篇文章，那么您可能还会喜欢我对具有挑战性CSS和JavaScript主题的其他解释 ，例如定位，Model-View-Controller和回调。

And if you think this might help other people in the same position as you, give it a “heart”!

而且，如果您认为这可以帮助与您处于同一位置的其他人，请给它一个“心脏”！

翻译自: https://www.freecodecamp.org/news/sql-tables-explained-by-voting-in-the-infamous-2016-election-de638dd9db7/

zk投票选举