数据库建模删除桥表 [英] Database modelling remove bridge table

问题描述

我设计了一个具有外部帮助的数据库，由于在Power BI中创建报表时遇到问题，我正在考虑对数据库模型进行重大更改，我最近在这里遇到过:

我想知道简化这种结构并将直接将单一类型的茶几直接连接到公司茶几是否更好.我仍然可以使用桥接表来记录公司的类型，也可以将End_Table_Type_All连接到公司的表.我像这样成像.

我希望这可以避免在汇总 Firms 多年以来的终端表内容时遇到的另一个问题，并减少我们的数据模型的复杂性，因为大多数表只是单一类型茶几.对于大多数查询，我可以排除一个联接.

恐怕我丢失了一些东西，而当前的方法是对此建模的正确方法.

如果将"Firm_Type"添加到"Firms"，然后将单个Type终端表连接到"End_Table_Type_A"，那么在更改的模型中会发生什么.是否会为每种类型获取单端类型表?

然后，我的想法会很愚蠢，我需要找到解决问题的另一种方法.

解决方案

只需给出此结束符即可.这似乎确实是一个愚蠢的想法，因为一旦将类型信息与Firms表合并，我就会复制连接到终端表的行.

I have designed a database with some outside help and I am thinking about a major change to the database model because of a problem creating reports in Power BI I have recently encountered here: SQL Power BI Report with Bridge Table

Disclaimer: if I could ask anyone within my firm, I would, but I can't.

We have a three-layer structure

1. A main table Firms, with information about the year and unique key/name for each firm
2. A bridge table Firm_Bridge which information about the type of the firm linked to Firm_Types
3. Many end Tables with information about sales and stuff

For these end tables, there are two types

• Single type tables that only matter for one Type (End_Table_Type_A or End_Table_Type_B) (often the case)

• Multi type tables that matter for more than one type (End_Table_Type_all) (rarely the case)

This is a made-up example similar to the real model

I am wondering if it was better to simplify this structure and directly connect the single type end tables to the firm table. I can still use the bridge table to document the type of firm and also connect the End_Table_Type_All to the firm's table. I imaging like this.

I hope this would avoid the problem that I have in my other question when summarizing stuff of end tables across years from Firms and reduce the complexity of our data model, as most tables are only single type end tables. I can exclude one join for most queries.

I am afraid I am missing something and that the current way is the proper way to model this.

What would happen in the changed model if I were to join the 'Firm_Type' to 'Firms' and then the single Type end table 'End_Table_Type_A'. Would it fetch the single end type table for each type?

Then, my idea would be stupid and I need to find another solution to my problem.

解决方案

Just to give this closure. It seems to be indeed a stupid idea, because I would duplicate rows connected to the end tables, as soon as I merge the information of the type with the Firms table.

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