建议对数据库设计进行改进 [英] Suggest improvements for the database design

问题描述

设计： -

1）故事板上有许多幻灯片，每张幻灯片可以有一个或多个字幕或图片。

1)A story board has a number of slides, each slide can have one or more captions or images.

2）每个故事板可以有一个或多个作者，零个或多个评论者。

2)Each storyboard can have one or more author , and zero or more reviewers.

3）每个对象图像，幻灯片和字幕（可能更多的对象将来会像音频等）可以有他们的意见。这些评论可以由作者或评论者提供。为了存储注释，我创建了一个注释表。

3) Each of the objects image,slide and caption(probably more objects will come later like audio etc) can have comments on them . These comments can be given by authors or reviewers. For storing comments I have created a comment table.

4）有必要记住幻灯片的顺序和幻灯片中的图像顺序，我使用幻灯片和图像号字段。

4)It is necessary to remember the order of slides and order of images in a slide , for which I am using slide and image number fields.

5）由于可以对任何对象给出注释，我需要为每个对象都有一个全局ID，因为它们的引用将被存储对于维护全局ID，以name GID结尾的所有字段都是使用Default：NEWID（）生成的唯一标识符。

5)Since comment can be given on any object , I needed to have a Global ID for each of the object,since their reference will be stored in a comment table.For maintaining a global id all the fields ending with name GID are uniqueidentifiers generated using Default: NEWID().

6）用户存储在用户表。

6)Users are stored in user table.

7）用户应该只能查看和加载他作为作者，评论者或所有者的故事板。

7)User should be able to view and load only the storyboards in which he is a author,reviewer or owner.

请为此数据库设计提出改进建议。

Please suggest improvements for this db design.

推荐答案

我看到并非所有的表都有PK，实际上我只看到用户表有一个，故事板使用名称和所有者作为PK？

i see that not all your tables have a PK, actually i only see that the user table has one, and the storyboard is using the name and owner as PK?

我喜欢对我的所有表使用一个id，除了用于NxN关系的中间表，就像你对作者和评论家一样。你也这样做，但你叫它GID。我会使用它们作为PK（和自动增量也）。

i prefer to use an id to all my tables, except the intermediary tables used for NxN relation, like you did on the authors and reviewers. You did the same but you called it GID. I'd use them as PK (and auto incremental also).

不要担心一个对象与另一个表上的另一个对象具有相同的GID，因为在您的注释表中有一个 ObjType ，指定与对象相关的表。因此，如果用户的GID与StoryBoard的GID相同，就会确定。

Don't worry if an object has the same GID as another object on a different table, because in your comments table you have an ObjType that specifies the table related to the object. So it will be ok if a User's GID is the same as the StoryBoard's GID.

我不习惯于sql-server图，但是似乎关系没有很好的定义：我看到用户和故事板在1xN相关，但它看起来像故事板和幻灯片相关的1x1关系，当它也应该是1xN

I'm not used to the sql-server diagrams, but it seems that the relations are not well defined: i see that user and storyboard are related in a 1xN but it looks like the storyboard and the slides are related in a 1x1 relation, when it should also be a 1xN

一个故事板有一个所有者和不同的作者，这意味着如果我创建一个故事板，故事板将有我的ID在OwnersUserId和我的ID也将存储在作者表中？如果是，似乎不必要。或..好..它取决于你的模式的概念。我看到两个选项：

A storyboard has a owner and different authors, that means that if i create a storyboard, the storyboard will have my id in the OwnersUserId and my id will also be stored in the authors table? if yes, it seems unnecessary. Or.. well.. it depends on the conception of your model. I see two options:

• 一个StoryBoard有一位作者和一位
  或多位合作者

• a StoryBoard has ONE author and ONE OR MANY collaborators

（不要更改您的模型，只能将作者表重命名为协作者或更精确）

(dont change your model, only rename the authors table to collaborators or something more precise)

StoryBoard有一个OR更多
作者，其中一个是
所有者

a StoryBoard has ONE OR MORE authors and one of them is the owner

（删除用户和storyboard之间的1xN关系并添加

(remove the 1xN relation between Users and storyboard and add a type columm to the Authors table)

最终观察结果：

用户应该只能查看和加载他创建的故事板。

User should be able to view and load only the storyboards created by him.

它不会改变数据模型.. =）但是，如果只有作者可以查看故事板，那么谁将审查他们？

it wont change the data model.. =) but, if only the authors can view the storyboards, then who will review them?

只是表面的，我发现模型很好地适应了问题

My observations are just superficial, I find the model well adjusted to the problem

好运

这篇关于建议对数据库设计进行改进的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！