代码之家 › 专栏 › 技术社区 › SuperFrog

在SQL Server中存储详细数据

database-design sql-server

SuperFrog · 技术社区 · 8 年前

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

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 表有1000个 like_votes 对于特定id,我需要知道按位置划分的明细,例如:

美国340

法国155

丹麦25

巴西290

澳大利亚190

Im以逗号分隔的字符串形式从客户端获取数据,例如: (1,2,45,67,87112234)和位置的国家代码(美国、澳大利亚、加利福尼亚等)。

我一直在考虑存储这些数据的几种可能性,但想知道哪种方法最适合(如果有的话)。

由于国家代码的数量有限,我可以扩展 users_votes 表中,并为每个标准添加带有国家代码的列。例如。 like_votes_us, dislike_votes_us, comment_votes_us, comment_likes_us. 在这种情况下,我可能会使用动态SQL来插入/更新数据。
为每个列创建新表,例如,我将有一个名为 like\u投票 ,其中我将有一个 id, external_id 这将是users\u Voces(table)id, country_code 和 count 柱因此数据将存储在 users\u投票 而且在 like\u投票 桌子我会记录下 external_id 和 country code . 在这种情况下,我将需要迭代插入的数据,以确定是否存在这个external\u id组合(然后只是增加它),或者需要插入它。

哪种方法(如果有的话)是存储此数据的最佳方法,以便易于插入/更新和查询?

1 回复 | 直到 6 年前

Thom A 8 年前

老实说,你目前的这种桌子设计不是一个好主意。构建良好关系数据库的一个重要方面是使用范式。我不打算解释这是什么,因为互联网上有成千上万篇文章解释它,以及它的不同迭代(从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语句,以便您能够理解:

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','Bob@gmial.com'),
       ('Ms Penelope Vert',2,'19930509','PVert@mfn.com');
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;

这将为您提供每个内容项的喜欢投票数,并将其拆分为多个国家/地区。如果你想把这些国家纳入他们自己的专栏,那么我 强烈地 建议在您的表示层而不是SQL中执行此操作(因为您必须使用动态SQL,而且(无意冒犯)根据您当前的数据库设计选择,我认为这超出了您的技能范围)。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