将数据库结构从SQL Server映射到DynamoDB [英] Mapping database structure from SQL Server to DynamoDB

问题描述

我正在考虑使用NoSQL数据库来扩展数据库读取。请参阅下面的关系数据库结构：

I am thinking about using a NoSQL database to scale database reads. Please see the relational database structure below:

CREATE TABLE Person(
      ID uniqueidentifier not null, 
      Name varchar(100), 
      DateOfBirth datetime)

CREATE TABLE Sport (
      ID uniqueidentifier not null, 
      Description varchar(50)) -- e.g. Football; Tennis; Badminton etc

CREATE TABLE PersonPlaysSport (
      PersonID uniqueidentifier FOREIGN KEY REFERENCES Person(ID), 
      SportID uniqueidentifier FOREIGN KEY REFERENCE Sport (ID), 
      primary key (PersonID, SportID)

在上面的示例中，某人玩了很多体育运动，在我的实际应用中，我有很多

In the example above a Person Plays many Sports. In my real application; I have many-to-many relationships like this that do not perform well.

如何将它们存储在NoSQL文档数据库（DynamoDB）中？

How would these be stored in a NoSQL document database (DynamoDB)?

推荐答案

免责声明-我不熟悉 DynamoDb ，但是使用了其他多个NoSql数据库

Disclaimer - I'm not familiar with DynamoDb, but have used several other NoSql databases

常用方法是选择最重要的主题实体作为文档的根（在您的情况下，我想说的是 Person ）

The common approach is to choose the most important subject entity as the root of the document (in your case, I would say this is Person)

然后为每个人创建了一个文档，包括所有关联实体的以人为本视图（即关联的运动）：

A document is then created for each person, and will include the "person centric" view of all associated entities (i.e. linked sports):

Joe (Person, Keyed on a natural, or surrogate id).
+ Fields of Joe (Date of Birth, etc)
+ SportsPlayed: (Collection)
--> Golf (Sport)
--> Tennis (Sport)

如果从以运动为中心的方法中查看关系很重要（例如，您需要知道哪些人订阅了哪个运动）：

If it becomes important to view the relationship from a Sport centric approach (e.g. you need to know which persons are 'subscribed' to which Sport):

• 您可以尝试对 Person.Sport （如果NoSql数据库允许的话）。这将允许诸如谁打高尔夫球？之类的查询，尽管这种方法在NoSql术语中经常被皱眉。

• You could attempt a secondary index on Person.Sport, if the NoSql database allows this. This would allow for queries like "Who plays Golf?", although this approach is often frowned upon in NoSql terms.

或者，最好创建第二个文档集合，这次由 Sport ：

Alternatively, and preferably, create a second collection of documents, this time keyed by Sport:

Golf (Sport)
- Joe
- Jim
...

等。显然，在更改 Person ， Sport 或它们之间的关系，但是好处是在读取端具有高性能-仅需要检索一个文档即可提取整个实体图-用SQL术语来说，这将需要一个Query联接3个不同的表

etc. Obviously there's extra work to be done in keeping both sets of documents up to date when a change is made to a Person, a Sport, or the relationship between them, however the benefit is high performance on the read side - only a single document needs to be retrieved to pull the entire entity graph - In SQL terms, this would have required a Query joining 3 distinct tables.

这篇关于将数据库结构从SQL Server映射到DynamoDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！