这是一个好的数据库设计理念吗？
编辑1：尝试通过重命名表及其关系来澄清问题。 EDIT2：请不要查看我在三个数据库表中保存的数据类型。 他们是即时化妆的。 它们不是我的真实世界场景（不，我不能谈论我的真实世界数据.. 事实上，目前有 1 个父母和 6 个孩子）。 请忽略什么类型的数据，只看需要某些数据的事实。 EDIT3：两个 FK 是 0 或 1 对 1 的关系。 不是0到很多。 不是 1 对 1。我试图避免 0 或 1 对 1 关系与 1 对 1 关系，因此我不需要 OUTER JOINS，而是需要 INNER JOIN。
问题：我需要知道建议的数据库设计是否好/坏/蹩脚/等等。
问题：今天我尝试创建一个索引视图，但失败了，因为我的表有外连接。 叹。 所以我想知道是否可以将其重新修复为如下设计：
- 三张桌子。
- table_User 在 table_Address 上有一个 FK
- 上有一个 FK
- table_User 在 table_Vehicle等
，表 B 和 C（现在有点像查找表）有..
- Id INT IDENTITY PK
- Description NVARCHAR(100) NULLABLE
通知可空的？ 这样，table_User 中的某些内容在 table_Address 中不存在...该字段为空（因为内部联接）。
之前，我做了一个 LEFT OUTER JOIN，所以如果 table_b 中没有数据，我会得到每个字段的结果为空。
我将在这里抛出一些数据示例...
Table_User
- ID：1，姓名：Fred，AddressID：1（NULL）
- ID：2，姓名：Joe，AddressID：2（1 smith street.....）
- ID：3 ，名称：Jane，AddressID：2（1 smith street.....）
Table_Address
- ID：1，Description = NULL
- ID：2，Description = 1 smith street
等等。
然后我终于可以将这一切放入索引视图中。 （我的现实生活场景大约有 8 张桌子）。
注意：数据库是 Microsoft Sql Server 2008，但这可以适用于任何数据库。
Q1：这个设计看起来还可以吗？
Q2：那么我在这里所做的就是标准化数据，对吗？ 通过将内部连接保持在一起。
Q3：最后，如果这是一个好的方法..我还可以通过一些唯一的约束或键或索引或什么（我不确定）来确保表中的数据是唯一的（例如街道地址）正确的术语）。
谢谢各位大师！
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.
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 has an FK on table_Vehicle
- etc..
and table B and C (which sorta act like lookup tables now) have..
- Id INT IDENTITY PK
- Description 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).
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.
I'll throw some data examples here...
Table_User
- ID: 1, Name: Fred, AddressID: 1 (NULL)
- ID: 2, Name: Joe, AddressID: 2 (1 smith street.....)
- ID: 3, Name: Jane, AddressID: 2 (1 smith street.....)
Table_Address
- ID: 1, Description = NULL
- ID: 2, Description = 1 smith street
etc.
So then i can finally put this all into an indexed view. (my real life scenario has around 8 tables).
NOTE: DB is Microsoft Sql Server 2008, but this could be for any DB.
Q1: Does that design seem ok?
Q2: So what i'm doing here is i'm normalising the data, right? by keeping the inner joins together.
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).
thanks gurus!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我发现你的问题令人困惑，但也许我可以提供一点帮助。
首先，表没有联接，而查询有。 您不会创建一个与另一个表连接的表。 只有 2 个可能相关的表，您可以使用联接查询这些表。
我建议您阅读有关数据库规范化的内容。 百科有一篇很棒的文章：http://en.wikipedia.org/wiki/Database_normalization
维基 你目前的情况，我不确定你想做什么。 如果该地址在不同的行中重复，则拥有地址的 ID 似乎没问题。 然而，需要几个“地址表”似乎很奇怪。 设计时要记住的最重要的事情是：
- 每个表都有正确的主键，这样您就可以正确连接表。
- 除非有非常充分的理由，否则不要重复数据。
但我再次推荐之前的文章。
希望有帮助！ :)
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
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.
hope that helps! :)
这是一个非常令人困惑的问题，所以请查找数据库的规范化。 第三范式（希望它的英文名称是这样的）应该可以解决大多数问题。
快速提示：如果您有重复的数据，那么您需要一个单独的表，您可以通过外键在第一个表中引用该表。 其他一切都只是查询。
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.
因此，您基本上是在表 B 和 C 中添加虚假记录，以便具有与 A 中相同的行数？ 如果我是你，我不会这样做，因为如果你的数据集很大，那么你会在没有任何真正需要的情况下增加行数，而且你还面临着不一致的风险（你的布局在很大程度上取决于你的能力）这些插入的虚假记录）。 除此之外，您希望通过索引视图实现什么目的？ 性能增益？ 您没有编写您正在使用的 DBMS，但根据我在 MSSQL 中的经验，这不会给您带来很多收获，因为只要您在表 A、B 和 C 中有适当的索引，服务器就能够使用即使没有索引视图，它们也能构建良好的查询计划。
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.
我个人对这种设计说“不”。 原因：（
I'd personally say 'no' to this design. Reasons: