数据库表架构和聚合根
应用程序是单用户、1 层（1 台）、数据库 SqlCE。 DataService 层将是（我认为）：返回域对象的存储库并使用 LinqToSql (dbml) 查询数据库。显然还有很多列，这是简化视图。
单独表中的 LogTime： http://i53.tinypic.com/9h8cb4.png
LogTime in ItemTimeLog 表（作为时间）：http://i51.tinypic.com/4dvv4.png
替代文本 http://i53.tinypic.com/9h8cb4.png
这是我第一次尝试创建>2个表的数据库。我认为表模式是有道理的，但我需要一些保证或批评。因为说实话，餐桌关系看起来相当可怕。我希望您能；
查看表架构，如果您立即发现明显的问题或错误迹象，请做出响应。如果您有时间，
查看程序摘要/问题，并查看表布局是否对这些点有意义。< /p>
请残酷一点，我会尽力捍卫:)
程序摘要：
a) 一组类别，每个类别都有一组策略 (1:m)
b ）每天都会生产一些物品。每个策略都可以引用它。 （因此可以有 50 个项目，一个策略可以引用其中 23 个）
c) 一个项目可以被多个策略引用。所以我认为这是一种m:m关系。
d) 状态值将在一天中的固定时间部分进行记录，用于： - .... 每个策略.....每个策略项....每个项目
e) 对某个项目的操作可以由引用该项目的策略执行。 - 这被记录为 ItemAction（可以将其称为 StrategyItemAction）
用户请求
b) -> e) 描述了程序的主要活动模式。对于每个类别，仅使用今天的 DayLog 。 第二优先级活动是检索历史记录，通常来自所有类别，从第 x 天到第 y 天;获取所有策略每日日志。
问题
首先，整体布局看起来合理吗？我很担心看到四面八方有这么多的关系，连接着一切。这是正常现象还是看起来有问题？
StrategyItem 用来表示 m:m 关系。正如我所说的 1:m / 1:1（标记为红色）是否正确？
StrategyItemTimeLog 和 ItemTimeLog；检索 StrategyItem 时需要一起检索的日志值。我分开的原因是第一个是特定于策略的，多个策略可以引用同一个项目。所以我认为不要重复那些不依赖于任何策略而仅依赖于项目的值。因此，我还拖出了 LogTime，因为它似乎是统一日志的唯一参数。但这三张桌子看起来很令人不安。这有道理吗？或者你有什么建议吗？
粉色圆圈显示了我对聚合根路径的模糊尝试。我一直在思考“哪个实体负责删除”。虽然我不确定真正的根源。我认为这是类别。它与上述用户请求相关吗？
编辑1： （更新架构，显示 365 天前几个关系的层次结构项目的典型数量，以及其他说明）
1:1 关系： 抱歉。我犯了一个错误。 StrategyDailyLog 应为 1:m。请参阅更新的架构。每个策略每天都有一个。
DayLog / StrategyDailyLog：我一直在思考DayLog是否应该像这样成为层次结构的一部分。 DayLog 表的目的是保存从同一天的所有 StrategyDailyLog 表派生的“总和值”。就像今天的表现值一样。它还保存日期值。这允许我省略 StrategyDailyLog 中的日期值（我觉得这有点像日期字段的重复建模），而是存在对 DayLog 的引用来“查找”日期。我不确定这是否是对正常化的滥用/误解。
空值：我没有考虑过这一点。我相信我找到了 2 个，正如现在在 StrategyDailyLog 和 ItemAction 中标记的那样。它们在创建时不能为 null，但如果需要删除策略或策略项，则可以将它们设置为 null。这不需要删除 StrategyDailyLog 和 ItemAction。因此它们可以设置为空。
所有 Id –列： 我的想法是将 ID（自动生成的整数）作为我所有表的 PK。我相信这也足以作为候选键。这不是PK的正确方式吗？这是识别我的任何表的唯一方法。我之前问过一个问题，这是否可以，也许我误解了，但认为这是一个很好的方法。
m:m 关系： 这就是我尝试做的事情：StrategyItem 是 StrategyDailyLog / DailyItem 的 m:m 表。
Applicaiton is single user, 1-tier(1 pc), database SqlCE. DataService layer will be (I think) : Repository returning domain objects and quering database with LinqToSql (dbml). There are obviously a lot more columns, this is simplified view.
LogTime in separate table: http://i53.tinypic.com/9h8cb4.png
LogTime in ItemTimeLog table (as Time): http://i51.tinypic.com/4dvv4.png
alt text http://i53.tinypic.com/9h8cb4.png
This is my first attempt of creating a >2 tables database. I think the table schema makes sense, but I need some reassurance or critics. Because the table relations looks quite scary to be honest. I'm hoping you could;
Look at the table schema and respond if there are clear signs of troubles or errors that you spot right away.. And if you have time,
Look at Program Summary/Questions, and see if the table layout makes makes sense to those points.
Please be brutal, I will try to defend :)
Program summary:
a) A set of categories, each having a set of strategies (1:m)
b) Each day a number of items will be produced. And each strategy MAY reference it.
(So there can be 50 items, and a strategy may reference 23 of them)
c) An item can be referenced by more than one strategy. So I think it's an m:m relation.
d) Status values will be logged at fixed time-fractions through the day, for:
- .... each Strategy.....each StrategyItem....each item
e) An action on an item may be executed by a strategy that reference it.
- This is logged as ItemAction (Could have called it StrategyItemAction)
User Requsts
b) -> e) described the main activity mode of the program. To work with only today's DayLog , for each category. 2nd priority activity is retrieval of history, which typically will be From all categories, from day x to day y; Get all StrategyDailyLog.
Questions
First, does the overall layout look sound? I'm worried to see that there are so many relationships in all directions, connecting everything. Is this normal, or does it look like trouble?
StrategyItem is made to represent an m:m relationship. Is it correct as I noted 1:m / 1:1 (marked red) ?
StrategyItemTimeLog and ItemTimeLog; Logs values that both need to be retrieved together, when retreiving a StrategyItem. Reason I separated is that the first one is strategy-specific, and several strategies can reference same item. So I thought not to duplicate those values that are not dependent no strategy, but only on the item. Hence I also dragged out the LogTime, as it seems to be the only parameter to unite the logs. But this all looks quite disturbing with those 3 tables. Does it make sense at all? Or you have suggestion?
Pink circles shows my vague attempt of Aggregate Root Paths. I've been thinking in terms of "what entity is responsible for delete". Though I'm unsure about the actual root. I think it's Category. Does it make sense related to User Requests described above?
EDIT1:
(Updated schema, showing typical number of hierarchy items for the first few relations, for 365 days, and additional explanations)
1:1 relation: Sorry. I made a mistake. The StrategyDailyLog should be 1:m. See updated schema. It is one per Strategy, per day.
DayLog / StrategyDailyLog: I’ve been pondering over wether DayLog shall be a part of the hierarchy like this or not. The purpose of the DayLog table is to hold “sum values” derived from all the StrategyDailyLog tables for the same day. Like performance values for this day. It also holds the date value. Which allows me to omit a date value in the StrategyDailyLog (Which I feel would kind of be a duplicate modeling of the date-field), but instead the reference to DayLog exist to “find” the date. I’m not sure if this is an abuse/misconception of normalization.
Null value: I haden’t thought about this. I believe I found 2, as now marked in StrategyDailyLog and ItemAction. They can not be null on creation, but they can be set to null if one need to delete either a Strategy, or a StrategyItem. That should not require a delete of the StrategyDailyLog and the ItemAction. Hence they can be set to null.
All Id –columns: My idea was to have ID (autogenerated Integer) as PK for all my tables. I believed that also would be sufficient as candidate key. Is this not a proper way to make PKs? It’s the only way any table of mine can be identified. I asked a question before if that was ok, maybe I misunderstood, but thought that was a good approach.
m:m relation: This is what I have attempted to do: StrategyItem is the m:m table of StrategyDailyLog / DailyItem.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
好的。这是我的残酷。我不明白这个模型。
因此，我不想对此发表太多评论，而是在看到它时想到了一些想法。
我认为你应该看看你的一对一关系（所有这些）。为什么 DayLog 和 StrategyDailyLog 分开在两个表中？可能是因为您始终至少有一个 DayLog 项目，但并非所有 DayLog 项目都有 StrategyDailyLog 项目。如果是这种情况，您可以在 DayLog 表中使用带有允许空选项的 StrategyID FK。
如果您可以显示哪些字段是必需的以及哪些字段接受 null 作为值，这将有助于理解模型。
您的所有表都有自己的 id 列。在进行 1:1 关系和 m:m 关系时，这可能会非常令人困惑。对于 1:1 关系，通常两个表之间的关系是根据两个表中的主键建立的。如果不这样做，则必须在外键列上创建候选键。在您的情况下，这意味着 StrategyDailyLog 应该在 DayLogID 上有一个候选键。
两个表之间的 m:m 关系通常通过在其间添加一个新表（并使用两个表中的主键）来解决。这些字段加在一起就是中间表的主键。
举例来说，类别和策略之间应该有 am:m 关系。然后，您应该创建一个名为 CategoryStrategy 的表，其中包含两个字段 CategoryID 和 StrategyID，这两个字段共同构成表 CategoryStrategy 的主键。
我希望我的评论有意义并且对您有用。
编辑 2011-01-17
我认为您不应该将 IDENTITY 列用作所有表中的主键作为原则。 m:m 关系不需要它，所以你不应该这样做。我还认为您误解了我所说的候选键的含义。候选键是可以用作主键的键。在 MS SQL Server 中，您为候选键定义唯一约束。
例如：表 StrategyItem 的 id 为 PK，但 StrategyID 和 DailyItemID 的组合是候选键。更好的办法是删除 id 并使用 StrategyID+DailyItemID 作为 PK。
以下是我根据您的描述构建的架构。我可能错过了一些重要的事情，因为我不知道你想做的一切。
在设计模式时，您不应该过多考虑查询性能和构建聚合。这可以通过在列上创建索引并在查询中使用 sum、count 和 group by 来处理。对于您对日期或日期间隔的查询，需要在下面的模型中创建的列上建立索引。在 MS SQL Server 中，有一种称为聚集索引的东西。默认表的 PK 是聚集索引，但在这种情况下，我会将 Created 列上的索引设为聚集索引。
一个类别有 0,1 个或多个策略。
LogItem 有一个类别和一个可选的策略
LogItem.Created 保存日期和时间。
Ok. Here is me being brutal. I do not understand the model.
So instead of trying to comment on that so much, here are some thoughts that came to my mind when I looked at it.
I think you should have look at your 1:1 relationships (all of them). Why is DayLog and StrategyDailyLog separated in two tables? Probably because you will always have at least one DayLog item but not all DayLog items have a StrategyDailyLog item. If that is the case you can have a StrategyID FK in DayLog table with allow nulls option.
It would help to understand the model if you could show which fields are required and which fields accept null as a value.
All your tables have its own id column. That can be quite confusing when doing 1:1 relations and m:m relations. For a 1:1 relation, usually the relation between the two tables is made on the primary key in both tables. If you do not do that you have to create a candidate key on the foreign key column. In your case that means that StrategyDailyLog should have a candidate key on DayLogID.
A m:m relation between two tables is usually solved by adding a new table in between, with the primary keys from both tables. Those fields together is the primary key for the table in the middle.
Lets say for example that you should have a m:m relationship between Category and Strategy. You should then create a table called CategoryStrategy with two fields CategoryID and StrategyID that together is the primary key for table CategoryStrategy.
I hope my comments makes sense and that they are useful to you.
EDIT 2011-01-17
I do not think that you should have as a principle to use a IDENTITY column as primary key in all tables. A m:m relation does not need it so you should not do it. I also think that you have misunderstood what I meant with a candidate key. A candidate key is a key that could have been used as the primary key. In MS SQL Server you define a UNIQUE CONSTRAINT for your candidate key.
Ex: Table StrategyItem have id as PK but the combination of StrategyID and DailyItemID is the candidate key. Better would be to remove id and use StrategyID+DailyItemID as PK.
Below is the schema that I would have built with your description. I might have missed something important because I do not know everything about what you want to do.
You should not think so much about query performance and building aggregates when designing the schema. That can be handled by creating indexes on columns and using sum, count and group by in your queries. An index on column Created in the model below would be necessary for your queries on a date or date interval. In MS SQL Server there is something called the clustered index. Default the PK of a table is the clustered index but in this case I would make the index on Created column the clustered index.
A Category has 0,1 or more Strategy.
LogItem have on Category and optionally one Strategy
LogItem.Created holds date and time.