Question

我至少要尝试简明扼要。

让我们重新跟踪长期账户余额。因此,我们的事实表将设一栏,如......。

www.un.org/Depts/DGACM/index_spanish.htm 账户平衡情况表

(FK)AccountID
(FK)DateID
...
Balance
...

Obviously you have an Account Dimension Table and a Date Dimension Table. So now we can easily filter on Accounts or Dates (or date ranges, etc.).

但这里却 s笑...... 账户可归属各组——任何特定日期的任何小组。团体只是合乎逻辑的抽象概念,除了报告的目的外,它们没有实际意义。账户为0、1或17个集团,丝毫不影响其平衡。例如,第1账户可能位于38、76、104和159组。第2账户可能属于第1组(有“未组合”的集团说明)。第3账户可能位于17个集团(real example)。

作为附加奖金,我们的用户完全是非技术性的。他们没有知识,在关系数据库方面没有经验,并且历来在综合的Excel解决方案中做了全部工作。现在,我们重新树立了他们能够用PowerPivot进行切片和过滤的层面模式,尽管这些账户集团威胁要把一个别无情的Star Schema变成一个足以使用户bal平和回到他们目前的间谍网解决办法的复杂体。

因此,请看我们的各种选择......

Boolean Method The Boolean method is not feasible. We have about 570,000 different accounts, but more importantly, 26,000 different groups. This would also be a devil for end-users to filter, since they re non-technical and are relying on very simple tools to get this done.

Multiple Column Method In theory this could work, however, we do have some accounts that belong to 17 groups. Again, the groups are really just logical groups -- they have no meaning, but they are required by the business for reporting purposes. Having end-users filter out groups from 17 different columns isn t going to go over well in user-acceptance, and would likely result in users refusing to use the solution (and I don t blame them).

Bridge Table This count work, but we do have 26,000 different groups. I m not finding this to be user-friendly.

自2006年以来我不喜欢我的选择,但我只能认为,除觉醒外,还有更好的办法......除非只想弄清国际会计准则。如果有人可以举手并解释其理由,那将不胜感激。

UPDATE: For clarification, an example I think everyone here can relate to is imagine you can list keyword skills on a resume. They all relate to the same person, but you can have any number of skills. The skills don t effect any of individual measures on a resume -- i.e. C++ isn t more valuable than C# -- you can t put all the resume/skill combinations in the fact table or you d end up double counting (or well more than double ;) ).

我认为,我能在这里做的最好办法是为各团体设立一个夸张的桌子。我不是狂热的,但我认为这是我唯一真正的选择。

So now we have...

www.un.org/Depts/DGACM/index_spanish.htm 账户平衡情况表

(FK)AccountID
(FK)DateID
...
Balance
...

<>Account Dimension/strong>

(PK)AccountID
Account Name
...
(FK)Account Group Key

Account Group Outrigger

(PK)AccountGroupID
(PK)AccountID)
Account Group Name

Answer 1

我要说,你要从接口开始。用户如何在理想世界中进行过滤?

我认为,我最后将坐下来谈谈桥梁或毫无事实的表象。也许从这个表上把钥匙放在表上,从这个表到集团成员之间可能有许多联系表。

这无疑是艰难的——必须使接口和使用案例能够发挥作用,因此我从那里开始。也许有些东西会掩盖他们如何做这种报告,例如集团中的等同课,或在某种程度上分割账户空间。也许有等级或组织,可使其更易于管理,并可提供更简单的设计。

Answer 2

If I correctly understood your question this should be okay:

CREATE TABLE IF NOT EXISTS `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `accounts_groups` (
  `account_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  UNIQUE KEY `account_group` (`account_id`,`group_id`,`start_date`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `account_balances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `balance` decimal(11,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `accounts_groups`
  ADD CONSTRAINT `accounts_groups_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`),
  ADD CONSTRAINT `accounts_groups_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`);

ALTER TABLE `account_balances`
  ADD CONSTRAINT `account_balances_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`);

友情链接