这是一个好的数据库设计概念吗？

时间：2022-09-27 11:59:17

EDIT1: Tried to clear the question up by renaming the tables AND their relationships. EDIT2: Please don't look at the what TYPE of data i'm holding in the three DB tables. They were made up on the fly. They are NOT my real world scenarios (and no, I can't talk about my real world data .. in fact it's 1 parent and 6 children, currently). Please just ignore what type of data and just look at the fact that some data is required. EDIT3: The two FKs are a 0 or 1 to 1 relationship. NOT 0 to many. Not 1 to 1. I'm trying to avoid the 0 or 1 to 1 relationship to a 1 to 1 relationship so i don't need to have OUTER JOINS but instead have an INNER JOIN.

EDIT1:尝试通过重命名表格及其关系来清除问题。编辑2:请不要查看我在三个数据库表中保存的数据类型。它们是在飞行中组成的。它们不是我真实世界的情景(不,我不能谈论我的真实世界数据......实际上它是1个父母和6个孩子,目前)。请忽略什么类型的数据,只看一些数据是必需的。 EDIT3:两个FK是0或1比1的关系。不是0到很多。不是1比1.我试图避免0或1比1关系到1比1的关系,所以我不需要有OUTER JOINS但是有一个INNER JOIN。

Question: I need to know if the proposed database design is good/bad/lame/etc..

问题:我需要知道建议的数据库设计是好还是坏/跛足等。

Problem: today i tried to make an indexed view, but failed 'cause my tables have outer joins. Sigh. So i was wondering if i can refix this up to be like the following design:

问题:今天我尝试制作索引视图,但失败了因为我的表有外连接。叹。所以我想知道我是否可以将其重新设置为以下设计:

  • Three tables.
  • table_User has a FK on table_Address

    • table_User在table_Address上有一个FK

  • table_User has an FK on table_Vehicle

    • table_User在table_Vehicle上有一个FK

  • etc..

and table B and C (which sorta act like lookup tables now) have..

和表B和C(现在就像查找表一样)具有..

  • Id INT IDENTITY PK

    • Id INT IDENTITY PK

  • Description NVARCHAR(100) NULLABLE

    • 描述NVARCHAR(100)NULLABLE

notice the nullable? this way, something in table_User doesn't exist in table_Address ... the the field is null (because of the inner join).

注意到可空的?这样,table_Address中的某些内容在table_Address中不存在...该字段为null(因为内部连接)。

Before, i made that an LEFT OUTER JOIN, so if there was no data in table_b, i'll get nulls are the result for each field.

之前,我做了一个LEFT OUTER JOIN,所以如果table_b中没有数据,我将得到空值是每个字段的结果。

I'll throw some data examples here...

我会在这里抛出一些数据示例......

Table_User

  • ID: 1, Name: Fred, AddressID: 1 (NULL)

    • ID:1,名称:Fred,AddressID:1(NULL)

  • ID: 2, Name: Joe, AddressID: 2 (1 smith street.....)

    • ID:2,姓名:Joe,地址ID:2(史密斯街1号.....)

  • ID: 3, Name: Jane, AddressID: 2 (1 smith street.....)

    • ID:3,姓名:Jane,地址ID:2(史密斯街1号.....)

Table_Address

  • ID: 1, Description = NULL

    • ID:1,描述= NULL

  • ID: 2, Description = 1 smith street

    • ID:2,描述= 1史密斯街

etc.

So then i can finally put this all into an indexed view. (my real life scenario has around 8 tables).

那么我最终可以把这一切都放到一个索引视图中。 (我的现实生活场景有大约8个表)。

NOTE: DB is Microsoft Sql Server 2008, but this could be for any DB.

注意:DB是Microsoft Sql Server 2008,但这可能适用于任何数据库。

Q1: Does that design seem ok?

Q1:那个设计看起来好吗?

Q2: So what i'm doing here is i'm normalising the data, right? by keeping the inner joins together.

Q2:所以我在这里做的是我正在对数据进行规范化,对吧?通过保持内部连接在一起。

Q3:Lastly, if this is an ok way about it .. can i also make sure the data in the tables are unique (eg. the street addresses) by having some unique constraints or keys or indexes or what (i'm not sure of the proper terminology).

Q3:最后,如果这是关于它的好方法..我还可以确保表中的数据是唯一的(例如街道地址),具有一些独特的约束或键或索引或什么(我不确定)适当的术语)。

thanks gurus!

4 个解决方案

#1

5  

I find your question confusing, but maybe I can help a little.

我发现你的问题令人困惑,但也许我可以帮助一点。

First of all, tables don't have joins, queries have. You don't make a table with a join to another table. There are just 2 tables that may be related, and you can query those tables using joins.

首先,表没有连接,查询有。您不创建具有到另一个表的连接的表。只有两个表可能相关,您可以使用连接查询这些表。

I recommend you to read about db normalization. Wikipedia has a great article: http://en.wikipedia.org/wiki/Database_normalization

我建议你阅读有关db规范化的内容。*有一篇很棒的文章:http://en.wikipedia.org/wiki/Database_normalization

About your current case, I'm not sure of what are you trying to do. Having an ID for an address seems ok if that address is repeated in different rows. However, needing several "address tables" seems weird. The most important things to remember when designing are: - Have a correct primary key in every table, so you can correctly join tables. - Do not repeat data unless you have a very very good reason. But I again recommend the previous article.

关于你目前的情况,我不确定你想做什么。如果该地址在不同的行中重复,那么拥有地址的ID似乎没问题。但是,需要几个“地址表”似乎很奇怪。设计时要记住的最重要的事情是: - 在每个表中都有一个正确的主键,这样你就可以正确地连接表。 - 除非你有充分的理由,否则不要重复数据。但我再次推荐上一篇文章。

hope that helps! :)

希望有所帮助! :)

#2

1  

A very confusing question, so please look up normalization of databases. The 3rd normal form (hopefully it is called like that in english) should solve most problems.

这是一个非常令人困惑的问题,请查看数据库的规范化。第三种常规形式(希望它被称为英语中的那种)可以解决大多数问题。

Quick tip: if you have data that is repeated then you need a separate table which you reference in the first one via a foreign key. Everything else is just queries.

快速提示:如果您有重复的数据,那么您需要一个单独的表,您可以通过外键在第一个表中引用。其他一切都只是查询。

#3

0  

So you are basically adding bogus records in tables B and C in order to have the same number of rows as in A? I wouldn't do it if I were you, because if your dataset is large, then you are increasing the number of rows without any real need and also you are running a risk of an inconsistency (your layout heavily depends on your ability to have these bogus records inserted). Apart from that, what do you want to achieve with an indexed view? Performance gain? You are not writing what DBMS you are using, but from my experience in MSSQL this ain't going to give you a lot of gain, because provided that you have proper indexes in tables A, B and C the server will be able to use them to build a good query plan even without an indexed view.

所以你基本上是在表B和C中添加虚假记录,以便拥有与A中相同的行数?如果我是你,我不会这样做,因为如果你的数据集很大,那么你就是在没有任何实际需要的情况下增加行数而且你也存在不一致的风险(你的布局在很大程度上取决于你的能力插入这些虚假记录)。除此之外,您希望通过索引视图实现什么?性能提升?您没有编写您正在使用的DBMS,但根据我在MSSQL中的经验,这不会给您带来很多好处,因为只要您在表A,B和C中有适当的索引,服务器就能够使用即使没有索引视图,它们也可以构建一个好的查询计划。

#4

0  

I'd personally say 'no' to this design. Reasons:

我个人对这个设计说'不'。原因:

  • (might not apply) trying to keep the address field normalized implies you need to process this field to make sure you avoid unintended duplicates. I.e. you must make sure the user enters the address in the correct format (otherwise '1 mystreet' could also be entered as '1, mystreet' or whatever - need to check this to avoid duplicates, otherwise normalization is good for nothing)

    • (可能不适用)尝试保持地址字段规范化意味着您需要处理此字段以确保避免意外的重复。即你必须确保用户以正确的格式输入地址(否则'1 mystreet'也可以输入为'1,mystreet'或其他 - 需要检查这一点以避免重复,否则标准化对任何事情都没有好处)

  • even if you find a reason to normalize (i.e. keep separate table for address), the concept of "dummy" address is strange to me. Why not use a nullable FK relationship, i.e. store a NULL address ID in the parent user table, instead of just putting a dummy ID in there.

    • 即使你找到了归一化的理由(即保留单独的地址表),“虚拟”地址的概念对我来说也很奇怪。为什么不使用可空的FK关系,即在父用户表中存储NULL地址ID,而不是仅仅在其中放置一个虚拟ID。

#1

5  

I find your question confusing, but maybe I can help a little.

我发现你的问题令人困惑,但也许我可以帮助一点。

First of all, tables don't have joins, queries have. You don't make a table with a join to another table. There are just 2 tables that may be related, and you can query those tables using joins.

首先,表没有连接,查询有。您不创建具有到另一个表的连接的表。只有两个表可能相关,您可以使用连接查询这些表。

I recommend you to read about db normalization. Wikipedia has a great article: http://en.wikipedia.org/wiki/Database_normalization

我建议你阅读有关db规范化的内容。*有一篇很棒的文章:http://en.wikipedia.org/wiki/Database_normalization

About your current case, I'm not sure of what are you trying to do. Having an ID for an address seems ok if that address is repeated in different rows. However, needing several "address tables" seems weird. The most important things to remember when designing are: - Have a correct primary key in every table, so you can correctly join tables. - Do not repeat data unless you have a very very good reason. But I again recommend the previous article.

关于你目前的情况,我不确定你想做什么。如果该地址在不同的行中重复,那么拥有地址的ID似乎没问题。但是,需要几个“地址表”似乎很奇怪。设计时要记住的最重要的事情是: - 在每个表中都有一个正确的主键,这样你就可以正确地连接表。 - 除非你有充分的理由,否则不要重复数据。但我再次推荐上一篇文章。

hope that helps! :)

希望有所帮助! :)

#2

1  

A very confusing question, so please look up normalization of databases. The 3rd normal form (hopefully it is called like that in english) should solve most problems.

这是一个非常令人困惑的问题,请查看数据库的规范化。第三种常规形式(希望它被称为英语中的那种)可以解决大多数问题。

Quick tip: if you have data that is repeated then you need a separate table which you reference in the first one via a foreign key. Everything else is just queries.

快速提示:如果您有重复的数据,那么您需要一个单独的表,您可以通过外键在第一个表中引用。其他一切都只是查询。

#3

0  

So you are basically adding bogus records in tables B and C in order to have the same number of rows as in A? I wouldn't do it if I were you, because if your dataset is large, then you are increasing the number of rows without any real need and also you are running a risk of an inconsistency (your layout heavily depends on your ability to have these bogus records inserted). Apart from that, what do you want to achieve with an indexed view? Performance gain? You are not writing what DBMS you are using, but from my experience in MSSQL this ain't going to give you a lot of gain, because provided that you have proper indexes in tables A, B and C the server will be able to use them to build a good query plan even without an indexed view.

所以你基本上是在表B和C中添加虚假记录,以便拥有与A中相同的行数?如果我是你,我不会这样做,因为如果你的数据集很大,那么你就是在没有任何实际需要的情况下增加行数而且你也存在不一致的风险(你的布局在很大程度上取决于你的能力插入这些虚假记录)。除此之外,您希望通过索引视图实现什么?性能提升?您没有编写您正在使用的DBMS,但根据我在MSSQL中的经验,这不会给您带来很多好处,因为只要您在表A,B和C中有适当的索引,服务器就能够使用即使没有索引视图,它们也可以构建一个好的查询计划。

#4

0  

I'd personally say 'no' to this design. Reasons:

我个人对这个设计说'不'。原因:

  • (might not apply) trying to keep the address field normalized implies you need to process this field to make sure you avoid unintended duplicates. I.e. you must make sure the user enters the address in the correct format (otherwise '1 mystreet' could also be entered as '1, mystreet' or whatever - need to check this to avoid duplicates, otherwise normalization is good for nothing)

    • (可能不适用)尝试保持地址字段规范化意味着您需要处理此字段以确保避免意外的重复。即你必须确保用户以正确的格式输入地址(否则'1 mystreet'也可以输入为'1,mystreet'或其他 - 需要检查这一点以避免重复,否则标准化对任何事情都没有好处)

  • even if you find a reason to normalize (i.e. keep separate table for address), the concept of "dummy" address is strange to me. Why not use a nullable FK relationship, i.e. store a NULL address ID in the parent user table, instead of just putting a dummy ID in there.

    • 即使你找到了归一化的理由(即保留单独的地址表),“虚拟”地址的概念对我来说也很奇怪。为什么不使用可空的FK关系,即在父用户表中存储NULL地址ID,而不是仅仅在其中放置一个虚拟ID。

标签：

相关文章