将详细数据存储在SQL Server中

Question

我正在设计一个保存投票的数据库。我创建了一个表：

CREATE TABLE [dbo].[users_votes](
    [id] [bigint] NOT NULL,
    [like_votes] [int] NOT NULL DEFAULT ((0)),
    [dislike_votes] [int] NOT NULL DEFAULT ((0)),
    [commented_votes] [int] NOT NULL DEFAULT ((0)),
    [comments_likes] [int] NOT NULL DEFAULT ((0))

问题是，还需要按位置存储故障数据。因此，例如，如果user_votes表具有1,000 like_votes的特定ID，我需要按位置知道分解，例如：

美国340

法国155

丹麦25

巴西290

澳大利亚190

我从客户端获取数据作为逗号界的字符串，例如：(1,2,45,67,87,112,234(和国家代码(美国，AU，CA等...(。

我一直在考虑存储这些数据的一些可能性，但想知道这些方法中的哪一个最适合(如果有(。

1. 由于国家代码的数量是有限的，因此我可以扩展users_votes表，并为每个条件添加带有国家代码的列。例如。like_votes_us, dislike_votes_us, comment_votes_us, comment_likes_us.在这种情况下，我可能会使用动态SQL插入/更新数据。

2. 为每列创建新表格，例如，我将拥有一个名为like_votes的表，其中我将拥有一个id, external_id，该表将为users_votes(table(ID，country_code和count列。因此，数据将存储在users_votes和like_votes表中。我将有external_id和country code组合的记录。在这种情况下，我将需要迭代插入的数据，以确定此外部_id组合是否存在(然后将其增加(或需要插入。

哪种方法(如果有的话(是存储此数据的最佳方法，因此易于插入/更新以及查询？

Answer 1

诚实，您目前拥有的这种表台设计并不是一个好主意。构建良好关系数据库的一个重要重要点是使用正常形式。我不会解释这是什么，因为互联网上有10篇文章中有10篇文章及其不同的迭代(从1NF到6NF IIRC(。

无论如何，您可以使用几张桌子轻松地完成此操作。我必须在这里猜测您的很多设置，但是希望您能够推断需要的东西，并调整不需要的东西。

首先，让我们从客户端表开始：

CREATE TABLE dbo.Client (ClientID int IDENTITY(1,1),
                         ClientName varchar(100), --You should really split this into Title, Forename and Surname, I'm just being "lazy" here
                         ClientCountryID int, --Not sure if a Client is related to a country or the vote is, i've guessed the client is.
                         DOB date,
                         EmailAddress varchar(100));
GO

所以，我们现在有一个简单的客户端表。接下来，我们想要一张乡村桌子。这很简单：

CREATE TABLE dbo.Country (CountryID int IDENTITY(1,1),
                          CountryName varchar(100),
                          CountryCode char(2)); --For example UK for United Kingdom, FR for France, etc
GO

您可能想在那里存储其他内容，但我不知道您的设置。

现在，这是我真正猜到的地方。我假设您的喜欢和不喜欢等与某物相关联。我不知道的是什么，所以，我将有一个称为"内容"的表，但是，我不知道这些喜欢的是什么，我对此表没有上下文，因此它将非常基本：

CREATE TABLE dbo.Content (ContentID int IDENTITY(1,1),
                          ContentType int, --Guessing might be types, maybe videos, Comments, articles? I have no idea to be honest)
                          ContentParent int, --Comments are joined to a Content (just like here on SO)? I'll guess it's possible
                          Content nvarchar(MAX)); --because I have no idea what's going in there
--Very simple Content Type Table
CREATE TABLE dbo.ContentType (TypeID int IDENTITY(1,1),
                              TypeDescription varchar(100));
GO

现在，最后，我们可以进行您要存储的选票；可能看起来像这样：

CREATE TABLE dbo.Vote (VoteID int IDENTITY(1,1),
                       ClientID int,
                       ContentID int,
                       Liked bit); --1 for Liked, 0 for Disliked, NULL for N/A perhaps?
GO

好，现在我们有一些桌子。现在，我意识到我没有给您任何类型的示例数据，所以我会为您提供一些插入语句，因此您可以得到这个想法：

INSERT INTO dbo.Country (CountryName, CountryCode)
VALUES ('United Kingdom','GB'),
       ('France','FR'),
       ('Germany','DE');
GO
INSERT INTO dbo.Client (ClientName, ClientCountryID, DOB, EmailAddress)
VALUES ('Mr John Smith',1, '19880106','[e-mail]'),
       ('Ms Penelope Vert',2,'19930509','[e-mail]');
GO
INSERT INTO dbo.ContentType (TypeDescription)
VALUES ('Video'),('Article'),('Comment');
GO
INSERT INTO dbo.Content (ContentType, ContentParent, Content)
VALUES (2, NULL, 'This is my first article, hi everyone!'),
       (3, 1, 'Nice! Good to see you''re finally posting!'),
       (1, NULL, 'http://youtube.com');
GO
--And now some votes:
INSERT INTO dbo.Vote (ClientID, ContentID, Liked)
VALUES (1, 1, 1),
       (2, 1, 1),
       (2, 2, 1),
       (2, 3, 0);
GO

请注意我如何投票。我没有在表中汇总；这样做是一个糟糕的主意。而是单独存储每个投票并使用查询进行汇总。您可以轻松执行此操作，例如：

SELECT C.ContentID,
       Cy.CountryName,
       COUNT(CASE V.Liked WHEN 1 THEN 1 END) AS LikedVotes,
       COUNT(CASE V.Liked WHEN 0 THEN 1 END) AS DisLikedVotes
FROM dbo.Content C
     JOIN dbo.Vote V ON C.ContentID = V.ContentID
     JOIN dbo.Client CV ON V.ClientID = CV.ClientID
     JOIN dbo.Country Cy ON CV.ClientCountryID = Cy.CountryID
GROUP BY C.ContentID,
         Cy.CountryName;

这为您提供了每个内容项目喜欢的票数的数量，并将其分为国家/地区。如果您想将这些国家放入自己的列中，那么i 强烈建议您在演示层中进行此操作，而不是您的SQL(因为您必须使用Dynamic SQL，并且(无犯罪(I想象一下，基于您当前的数据库设计选择，目前这超出了您的技能。Excel非常擅长使用枢轴表进行此操作。如果要保留在SQL Server中的过程，请考虑使用SSR和矩阵。

如果您有任何疑问，请询问

注意：这里没有任何形式的外国键，约束，默认值等。对于任何优质的数据库设计，这些都是定义。

清理脚本：

DROP TABLE dbo.Client;
DROP TABLE dbo.Country;
DROP TABLE dbo.Vote;
DROP TABLE dbo.Content;
DROP TABLE dbo.ContentType;
GO