请帮助我为我的项目设计关系数据库。 [英] Please help me with relational database design for my project.

问题描述

你好再说一遍。我会给自己一个快速的背景。我问这个是因为这是我第一次使用关系数据库。我做了一些研究（感谢论坛和Youtube），我认为RDBMS将真正帮助我完成这个项目。我认为我也是vb.net的初学者。



无论如何。我的项目案例是这样的。

日志用户上传和下载。



我认为用户可以上传多个文件并下载多个文件...也可以由不同的用户下载文件。所以，我创建了一个这样的表。 （pri）是主键。

Hello there again. I'll give myself a quick background. I'm asking this because this is my first time using a Relational Database. I did some research (Thank you forums and Youtube) and I think RDBMS will really help me with this project. I am also at a beginner level in vb.net I think.

anyway. My project case is this.
Log users upload and download.

I think users can upload several files and download several files... also a file can be downloaded by different users. So, I created a table like this. (pri) is primary key.

[Users]      [UserFiles]           [Files]
UID (pri)    UserFilesID (pri)     FID (pri)
Username     UID (pri)             Filename
Realname     FID (pri)             Description
             Operation

Q1：我的数据库设计是否合适？



这是我的逻辑

Q1: Is my DB design right?

Here is my Logic

1. verify user if existing. if yes then get UID.<br />
2. If downloading, get the FID of the filename from FILES.<br />
3. update USERFILES based on the fetched UID and FID and Operation = 'Download'

所以我可以得到这样的USERFILES表。 （这还不存在。我只是想象一下我的桌子会是什么样子）

So I could get a USERFILES table like this. (This is not existing yet. I'm just trying to imagine what my table would look like)

UserFilesID    UID   FID  Operation
1              22    10   Upload
2              22    11   Upload
3              22    12   Upload
4              23    10   Download
5              24    10   Download

Q2: Is this a correct logic? or what I should expect?<br />
Q3: How can I check if my design is correct? I'm thinking a select statement that would check the uploads of user with UID=22 and the result is files with FID = 10,11,12<br />
<br />
Q4: What select statement should I use?

问候，



我尝试过：



我尝试在Server Management Studio上创建数据库。但是，到目前为止，我还没有创建一个表单来获取数据。我只是想检查一下我的设计是否正确，因为这是我第一次尝试关系数据库。

Regards,

What I have tried:

I Tried creating a database on on Server Management Studio. But, so far I haven't created a form to get data yet. I just would like to check if my design is right as this is my first time trying out Relational database.

推荐答案

你是一个好方法和第一次工作与RDB你做得很好。但是你需要考虑一些积分。同时也认识到了一些后果。



请记住，如果不了解更多有关你的决赛的信息，你的布局是否合适是不可能给出最终答案的。目标。但有可能突出一些要点，让您了解设计的后果。所以，让我们逐个表格。对于以下内容，我假设所有字段 * ID 的数据类型为int。



[用户]

UID（pri）

可能内部自动生成的密钥，定义为用于管理重新分区的主密钥。通常，最终用户永远不会接触到这个值。广泛使用的技术，顺便说一句。我这样做也总是这样。



用户名

目前没有从您提供的来源可识别的约束。但想想如果两个人使用相同的用户名会出现什么混乱。

- >所以很可能你会为 UserName 应用一个独特的约束。



讨论：

正如您所看到的，引入内部数字主要将迫使我们引入类似用户主要的内容，这会导致一个索引更多。不要担心，它确实是一种广泛使用的技术。



注意：

如果你不注意技术解决方案并严格按照理论设计您的数据库布局，然后用户名将成为真正的主键。结果外键也变成了同一类型......



[文件]

FID（pri）

与上面的UID相同，确定。

文件名

与UserName相同，但可能不是那么重要。我个人也会对文件名>



[UserFiles] 应用一个唯一约束

让我们先来看看更简单的部分：

UserFiles.UID

应定义为 Users.UID 的外键。在这里，您应该考虑在外键定义中包含ON DELETE CASCADE。这意味着，如果您删除用户，UserFiles中的所有条目也将被删除给相应的用户。



ON DELETE CASCADE很酷，我们不必费心删除UserFiles中的详细信息。嗯.... 真的很酷？

在某些情况下它很棒，但它也适用于你的布局吗？只有你可以决定。重点是：如果您允许/需要删除用户，则表示您需要删除UserFiles（如果您将UserFiles.UID设为外键）。但最后这意味着删除相应的用户文件也会删除哪些文件已被哪个用户上传的信息。再次，由你决定是否可以忍受。



UserFiles.FID

应定义为 Files.FID 的外键。不要对此外键应用ON DELETE CASCADE，否则所有与已删除用户相关的文件也将被删除，以防其他用户不使用;）



现在更难的部分：

目前你定义了三个字段的序列 UserFilesID，UID，FID 作为主键对于UserFiles。但是真的是主要的吗？



V1。）乍一看它应该是 UID，FID 。但是像这样，用户只能上传或下载文件。

V2。）让用户允许上传文件然后下载。这意味着主要需要像 UID，FID，Operation 。但是像这样，用户只能下载一次文件...

- > V1和V2没有意义，让我们忘了它们。



那么现在呢？为此，我们需要定义允许用户执行的操作。

a。）用户可以多次下载文件吗？

b。）用户是否可以上传一个文件多次？



上述问题的答案将有助于决定如何继续。所以，我会这样决定：

a。）是的，用户可以多次下载文件。但是，我们还需要另外一个字段来制作主要字段，即计数器或日期时间。

b。）使用相同的FileName不能上传同一个文件多次。

- >停在这里...这两个方面我们不会链接到UserFiles的主键问题。



毕竟以上我会像这样使用UserFiles _t这样：

You are on a good way and for a first work with RDB you did it quite nice. But there are some points you need to think about resp. to recognize also some consequences.

Keep in mind, it is not possible to give you a final answer, whether your layout is ok or not without knowing more about your final target. But it is possible to highlight some points to give you an idea about the consequences of the design. So, let's go through it, table by table.For the following I assume that all fields *ID have the data type int.

[Users]
UID (pri)
Probably an internal auto generated key, defined as primary used to manage realations. Usually the end user will never get in contact with this value. A widely used technics, btw. I do it also always like this.

Username
At the moment no contstraint recognizable from the source given by you. But think about what confusion comes up if two person will use the same Username.
-> So most probably you will apply a unique constraint for UserName.

Discussion:
As you can see, introducing a "internal numeric primary" will force us to introduce also something like a "user primary" which leads to one index more. Don't worry about that, it is really a widely used technics.

Note:
If you do not pay attention to the technical solution and design your database layout strictly based on the theory, then "Username" will be the real primary key. With the consequence that also foreign keys becomes the same type...

[Files]
FID (pri)
Same as UID above, ok.
Filename
Same as above with UserName, but maybe not that critical. Personally I would also apply a unique constraint for Filename>

[UserFiles]
Lets first have a look to the more easy part:
UserFiles.UID
should be defined as Foreign Key to Users.UID. Here you should think about to also include a "ON DELETE CASCADE" in the foreign key definition. That means, in case you delete a User, all entries in UserFiles will also be deleted for the respective user.

"ON DELETE CASCADE" is cool, we do not have to bother about to delete the details in UserFiles. Hmmm.... really cool?
In some situation it is great, but is it also here for your layout? Only you can decide it. What is the point: In case you allow/need to delete a User it means you need to delete the UserFiles (in case you make UserFiles.UID a foreign key). But finally that means also deleting the corresponding UserFiles removes also the information, which files have been uploaded by which user. Again, it's up to you to decide whether you can live with that.

UserFiles.FID
should be defined as Foreign Key to Files.FID. Don't apply a "ON DELETE CASCADE" for this foreign key, otherwhise all Files related to a deleted user will also be deleted in case they are not used by other users ;)

Now the harder part:
At the moment you defined a sequence of the three fields UserFilesID, UID, FID as the Primary key for UserFiles. But was is really the primary?

V1.) On a first glance it should be UID, FID. But like this, a user can only either upload or download a file.
V2.) Let's the user allow to upload a file and later download it. That means the primary needs to be like UID,FID,Operation. But like this, the user can download a file only one time...
-> V1 and V2 don't makes sense, let's forget them.

So what now? For this we need to define what we allow a user to do.
a.) Can a user download a file more than one time?
b.) Can a user upload a file more than one time?

The answers to the above question will help to decide how to go on. So, I would decide like this:
a.) Yes, a user can download a file several times. But then we need one other field more to make a primary, i.e. a counter or a datetime.
b.) The same file can't be uploaded more than one time with same FileName.
-> Stop here... this two aspects we will not link to the question of primary key for UserFiles.

After all above I would make UserFiles _at the moment_ like this:

CRATE TABLE UserFiles
(
  UserFilesID integer not null,
  UID integer not null,
  FID integer not null,
  Operation   nchar...,
  LogDate     datetime,  // Information only

  CONSTRAINT PK_UserFiles PRIMARY KEY (UserFilesID),
  CONSTRAINT FK_UserFiles_Users FOREIGN KEY (UID) REFERENCES Users (UID)
    ON DELETE CASCADE,
  CONSTRAINT FK_UserFiles_Files FOREIGN KEY (FID) REFERENCES Files (FID)
)

摘要：

Q1：我的数据库设计是否合适？

Q2：这是正确的逻辑吗？或者我应该期待什么？

见上面的讨论以及你需要决定的一些细节。

Q3：怎么能我检查一下我的设计是否正确？我正在考虑一个选择语句来检查UID = 22的用户的上传，结果是FID = 10,11,12的文件

这是一个测试，还需要更多。问问自己你想要展示什么，然后为它制作SQL并测试它并将结果与​​预期结果进行比较。

Q4：我应该使用哪种选择语句？ / code>



Abstract:
Q1: Is my DB design right?
Q2: Is this a correct logic? or what I should expect?
See above discussion and some details you need to decide.
Q3: How can I check if my design is correct? I'm thinking a select statement that would check the uploads of user with UID=22 and the result is files with FID = 10,11,12
That is one test, more will be needed. Ask yourself what you like to show and then make the SQL for it and test it and compare the result against your expected result.
Q4: What select statement should I use?

SELECT
  Users.UID,
  Users.UserName,
  Users.RealName,
  UserFiles.UserFilesID,
  UserFiles.UID
  UserFiles.FID
  UserFiles.Operation,
  Files.FileName,
  Files.Description
FROM Users
LEFT JOIN UserFiles ON UserFiles.UID = Users.UID
LEFT JOIN Files ON Files.FID = UserFiles.FID
WHERE Users.UID = @UID
ORDER BY ....

外键约束的提示：

检查您选择的SQL Server，定义外键是否也会创建一个非唯一的索引。当您的数据库变大时，这变得很重要。即MSSQL不会为外键约束创建索引，如果你有一个包含数十万条记录的详细信息表，这通常会成为一个瓶颈。



祝你好运，我希望它有所帮助。

A hint for Foreign Key Constraints:
Check with your choosen SQL Server, whether defining a foreign key does also creates an non unique index for it. This becomes important when your database becomes big. I.e. MSSQL does not create an index for a Foreign Key Constraint and in case you have a detail table with hundred of thousands records this becomes usually a bottleneck.

Good luck, and I hope it helps.

这篇关于请帮助我为我的项目设计关系数据库。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！