数据库表模式和聚合根 [英] Database Table Schema and Aggregate Roots

问题描述

Applicaiton是单用户，1层（1个），数据库SqlCE。 DataService层将（我想）：Repository返回域对象和查询数据库与LinqToSql（dbml）。显然有很多列，这是简化的视图。

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在单独的表中： http://i53.tinypic.com/9h8cb4.png

LogTime in separate table: http://i53.tinypic.com/9h8cb4.png

ItemTimeLog表中的LogTime（作为时间）： http://i51.tinypic.com/4dvv4.png

LogTime in ItemTimeLog table (as Time): http://i51.tinypic.com/4dvv4.png

alt文字http://i53.tinypic.com/9h8cb4.png

这是我第一次尝试创建一个> 2个表数据库。我认为表模式有意义，但我需要一些保证或批评。因为表关系看起来相当可怕，是诚实的。我希望你能这样做;

• 查看表模式并回答是否有明显的麻烦迹象或错误。如果您有时间，

• 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 :)

计划摘要：

a）一组类别， （1：m）

a) A set of categories, each having a set of strategies (1:m)

b）每天会生成多个项目。每个策略都可以引用它。
（所以可以有50个项目，一个策略可以引用其中的23个）

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）通过多个策略。

d）状态值将以固定时间分数记录在一天中，包括：
- .... each Strategy ..... each StrategyItem .... each item

d) Status values will be logged at fixed time-fractions through the day, for: - .... each Strategy.....each StrategyItem....each item

e）可以通过引用它的策略来执行。
- 记录为ItemAction（可以称为StrategyItemAction）

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)

用户请求

b） - > e）描述了 主活动模式 。仅对每个类别使用 今天的日志 。 第二优先级活动是 检索历史记录，通常是从第x天到第y天的所有类别;获取所有StrategyDailyLog。

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.

问题

1. 整体布局看起来有声音吗？我担心看到在所有方向有这么多的关系，连接一切。这是正常的，还是看起来麻烦？

1. 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用于表示m：m关系。是正确的，因为我注意到1：m / 1：1（标记为红色）？

StrategyItem is made to represent an m:m relationship. Is it correct as I noted 1:m / 1:1 (marked red) ?

StrategyItemTimeLog和ItemTimeLog;记录在检索StrategyItem时需要一起检索的值。我分离的原因是第一个是战略特定的，并且几个策略可以引用相同的项目。所以我想不要重复那些不依赖于没有策略，但只依赖于项目的值。因此，我也拖出了LogTime，因为它似乎是唯一的参数来统一日志。但这一切看起来相当令人不安的那3个表。它有什么意义吗？或者你有建议？

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：
（更新的架构，显示前几个关系的层次结构项目的典型数量，附加说明）

1：1关系：很抱歉。我犯了一个错误。 StrategyDailyLog应为1：m。查看更新的模式。

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：我一直在思考，DayLog应该是层次结构的一部分像这样或不。 DayLog表的目的是保存从同一天的所有StrategyDailyLog表派生的和值。就像今天的表现值一样。它还保存日期值。这允许我在StrategyDailyLog中省略一个日期值（我认为这是日期字段的重复建模），但是相反，DayLog的引用存在于查找日期。我不知道这是否是一个滥用/误解的规范化。

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.

空值：我没有想过这个。我相信我发现2，如现在标记在StrategyDailyLog和ItemAction。它们在创建时不能为null，但如果需要删除策略或StrategyItem，则可以将它们设置为null。这不应该需要删除StrategyDailyLog和ItemAction。因此，它们可以设置为null。

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.

所有ID列 - ：我的想法是将ID（自动生成的整数）表。我相信这也足以作为候选键。这不是一个正确的方式使PKs？这是我的任何表可以识别的唯一方式。

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关系：这是一个很好的方法，但我认为这是一个很好的方法。是我试图做的：StrategyItem是StrategyDailyLog / DailyItem的m：m表。

m:m relation: This is what I have attempted to do: StrategyItem is the m:m table of StrategyDailyLog / DailyItem.

推荐答案

好的。这里是我残酷。我不明白的模型。
因此，我不是试图对这么多的评论，这里有一些想法，当我看着它。在我看来。

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.

我认为你应该看看你的1：1关系（所有）。为什么DayLog和StrategyDailyLog分为两个表？可能是因为你总是有至少一个DayLog项目，但不是所有的DayLog项目都有一个StrategyDailyLog项目。如果是这种情况，你可以在DayLog表中有一个StrategyID FK，允许有null的选项。

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.

所有表都有自己的id列。当做1：1关系和m：m关系时，这可能很混乱。对于1：1关系，通常两个表之间的关系在两个表中的主键上进行。如果你不这样做，你必须在外键列上创建一个候选键。在你的情况下，这意味着StrategyDailyLog应该在DayLogID上有一个候选键。

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.

两个表之间的m：m关系通常通过在它们之间添加一个新表来解决，两个表中的主键。这些字段一起是中间表的主键。
例如，你应该在类别和策略之间有一个m：m关系。然后，您应该创建一个名为CategoryStrategy的表，其中有两个字段CategoryID和StrategyID，它们一起是表CategoryStrategy的主键。

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.

01-17

我不认为你应该在所有表中使用IDENTITY列作为主键。 m：m关系不需要它，所以你不应该这样做。我也认为你误解了我的意思与候选键。候选键是可以用作主键的键。在MS SQL Server中，为您的候选键定义一个UNIQUE CONSTRAINT。
例如：表StrategyItem的id为PK，但StrategyID和DailyItemID的组合是候选键。更好的是删除id并使用StrategyID + DailyItemID作为PK。

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.

下面是我将使用您的描述构建的模式。我可能错过了一些重要的事情，因为我不知道你想做什么的一切。
在设计模式时，不应该考虑查询性能和构建聚合。这可以通过在列上创建索引并在查询中使用sum，count和group by来处理。列上的索引对于日期或日期间隔的查询，需要在以下模型中创建。在MS SQL Server中有一个称为聚集索引的东西。默认表的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.

一个类别有0,1个或多个策略。
LogItem有类别和可选的一个策略
LogItem.Created保存日期和时间。

A Category has 0,1 or more Strategy. LogItem have on Category and optionally one Strategy LogItem.Created holds date and time.

这篇关于数据库表模式和聚合根的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！