构建数据库关系以跟踪应用程序设置的不同变化

问题描述

我目前正在设计的应用程序允许用户创建自定义的竞争性联赛（认为它类似于幻想体育），并且每个用户可以加入不同的联赛，并且每个联赛都由多个回合组成，其中用户（以下称为球员） ）将为每个联赛建立不同的标准/成就并竞争并获得积分。以下是一些需要注意的关键信息：

• 在联赛/赛季中所有回合都累积积分


• 自定义分数标准/权重设置可以在每个回合中更改。我认为大多数情况下，这些回合点设置会在整个联赛（即赛季）内保持相对一致，但联赛当然可以选择将其调高很多。通过我目前的设置方式，联赛将建立一组默认的联赛级别积分设置，这将是每一轮的默认设置，但他们始终可以决定更新积分权重，添加新的积分设置或停用/激活他们。由于这些子设置可能会逐轮变化，因此，我应该为一张表指定默认的联赛级别积分设置，然后为另一张表提供轮级子设置，其中包括针对每个联赛实际实现的所有设置


• 与上述类似，每个回合可以由不同的玩家组成；不一定是每个回合中都有所有的联赛成员/球员或同一组球员，因此我目前有一张联赛球员表和一局球员表，然后有一张表跟踪基本上每个球员都有的积分外键链接到其他表，对我来说似乎有点奇怪。

这种方法有意义吗？或缺少什么？下面是我制作的实体关系图的图像的链接，这让我感到有些奇怪，尤其是使用Point Earned表时，但这可能是由于我缺乏设计关系数据库的经验所致。请注意，用 CPK列出的字段是复合主键，由该表中相应外键的串联组成。欢迎任何反馈，谢谢！

，自1993年以来建立关系数据库建模的标准

我的对于初学者来说是必不可少的阅读

是那些过失的人的复习。

注意•内容

• 关系密钥

  
  
  
  
  
  • 所有键都是逻辑关系键，它们是复合键
  
  
  • 所有数据均受以下约束关系密钥（这是关系完整性）
  
  
  • 必须在<$ c $中注册 round_player c>同盟 回合在
  
  
  • A round_default_weight 必须是为联盟设置的有效 league_weights 之一，而轮次在
  
  
  • 其他条件
  
  
  • 如果您在（a）声明或（b）中遇到困难编码外键（它们是复合键）会让我知道，我会提供它。
  
  



• 专有子类型

  
  
  
  
  
  • 每个回合具有一个 round_default_weight x或一个 round_custom_weight
  
  
  • 请参阅以获取有关子类型实现的完整详细信息。
  
  



• I无法理解 point_setting 的意思。我理解它是对得分进行模拟的权重。




• 我不明白您为什么拥有获得的积分作为一个单独的文件（即，与多个父母的问题分开）。这似乎是每得分一个记录。假设只有玩家可以得分，您可以将积分累积到 round_player 行。




• 您的设计允许每个联赛多个管理员，而不是一个。请确认。

享受。请随时提出具体问题。可能需要澄清：确定后，我将发布进度数据模型。

评论

是。

好。尝试这个。

• 每个<$都不需要维护一行每个回合每个点 c $ c> round_player 。我们可以为每个 point_type 保留一行，而不包含每个 point_type 的总点数。




• 您需要指定 point_types （我以橄榄球点类型为例）。




• 这是一个普通表，而不是引用表或查找表，因为该关系是可识别的。

An app I'm currently designing allows users to create custom competitive leagues (think of it kind of like fantasy sports) and each user can join different leagues and each league consists of multiple rounds where the users (hereafter referred to as Players) will compete and can earn points for different criteria/accomplishments established for each league. Here's some key info to note:

• Points are accrued across all the rounds during a league/season
• The custom point criteria/weight settings can change across each round. I’m thinking that for the most part these round-by-round point settings will stay relatively consistent during the span of a league (i.e. season) but a league could certainly choose switch it up a lot. With the way I currently have things setup, the league will establish a group of default league-level point settings that will be the default for each round but they can always decide to either update the point weights, add new point settings or deactivate/activate them. Because of these sub-settings that could vary from round-to-round, should I have one table for the default league-level point setting and then another for round-level sub-settings that will include all of the settings actually implemented for each round?
• Similar to the above, each round can consist of different players; it’s not necessarily going to be all the league members/players or the same group of players in each round so I currently have one table for league player and one for round player and then a table that tracks the points earned for each player that basically has foreign keys linking to a bunch of other tables, which seems a little weird to me.

Does this approach make sense or am I overthinking or missing something? Below is a link to an image of an entity relationship diagram that I’ve concocted and it just feels a little wonky to me, especially with the Point Earned table, but that may just be due to my lack of experience with designing relational databases. Note that the fields listed with ‘CPK’ are composite primary keys that consist of the concatenation of the corresponding foreign keys in that table. Any and all feedback is welcome, thanks!

link to image of ERD

解决方案

Confirmation

Yes. What you are implicitly declaring is, a round has one setting, which is:

• either the league-level setting
• or a custom setting.

That is a typical OR Gate in Logic, and there is a correct method to implement that in a RElational database: an Exclusive Subtype.

That is correct: they are two discrete Facts, the latter is Dependent on the former. That means:

• a player has to first be registered as a league_player
• consequently, a player can be registered as a round_player
  • and only in a league in which he is registered as a league_player.

Your data model goes off the rails at that point.

---

Problem

1. Evidently, you have learned the value of data integrity. Eg. you are attempting to ensure that a player in a round in a league is actually a player who is registered in that league; etc. Excellent. You are trying to achieve Relational Integrity, which is logical (and distinct from Referential Integrity, which is a physical feature of SQL).

   • You have figured out that the only way to do that is to use Composite Keys. Excellent. Composite Keys are ordinary fare in a Relational database.

2. The second problem, however, is that you don't have keys, you have physical Record IDs ... declared as "keys". Therefore the logical thing (data is logical) that you are trying to constrain isn't constrained. And that effort, that attempt, results in scores of relationships ... that do not achieve the desired result.

3. Relational Key

   • As defined by Dr E F Codd in the Relational Model:
     • a [Relational, logical] Key is made up from the data
     • references are by logical Relational Key
   • In 1960's Record Filing Systems, which are heavily marketed by the "theoreticians" and fraudulently named "relational":
     • references are by physical Record ID.
     • Such primitive systems have no Relational Integrity; no Relational Power; and no Relational Speed.

4. You are using Record IDs declared as "keys" (that will confuse the hell out of you, because it is not a Key, and it has none of the properties of a Key). And then trying to get some Relational Integrity (which you intuitively know only the Relational Model provides) through Composite Keys ... but you are using the declared Non-Keys, so it fails, and makes a complex model in the attempt.

   • The correction is to use logical Relational Keys, and to avoid physical Record IDs

5. Also, your CPK is a great attempt to overcome the limitations of the "theoreticians", but it does not specify precisely what columns make it up. That is easily corrected if you use IDEF1X (the standard for modelling Relational data): the columns that make up the Key, Primary or Alternate, are explicit.

6. The next problem is, your logical rows (as distinct from physical records) are not unique, and the RM demands that logical rows are unique.

   • Eg. in User, username is not unique
     
   • username is actually the logical Key (which would make the rows unique)
   • you also need uniqueness on (first_name, last_name), which is a second logical Key
   • If you understand the above, you will understand that:
     • user_id is 100% useless (achieves nothing, it is merely an additional column and an additional index, which is to be avoided)
     • username is the real, logical, PRIMARY KEY, which is migrated as FOREIGN KEY wherever it is referenced.
     • The FK reference is therefore logical, to the thing itself, and not physical, to the pointer to a record in which the thing might be.

7. Likewise, you can get rid of all the Record IDs.

Relational Data Model

You have been schooled in the physical (falsely named "relational"), and you have tried to 'move up' into the logical. Great idea, but that does not work. The logical comes first, and when ready, one 'moves down' into the physical. The physical serves the logical, not the reverse.

Try this.

Note • Notation

• All my data models are rendered in IDEF1X, the Standard for modelling Relational databases since 1993

• My IDEF1X Introduction is essential reading for beginners

• The IDEF1X Anatomy is a refresher for those who have lapsed.

Note • Content

• Relational Key

  • All Keys are logical Relational Keys, which are Composites
  • All data is constrained by Relational Keys (this is Relational Integrity)
  • A round_player must be registered in the league that the round is in
  • A round_default_weight must be one of the valid league_weights that have been set up for the league that the round is in
  • Et cetera
  • If you have difficulty either (a) declaring or (b) coding the Foreign Keys, which are Composites, let me know and I will provide it.

• Exclusive Subtype

  • Each round has either one round_default_weight xor one round_custom_weight
  • Refer to Subtype for full details on Subtype implementation.

• I do not understand what you mean precisely by point_setting. I understand it to be a weight that is applied to the score, which is modelled.

• I do not understand why you have Point Earned as a separate file (ie. separate to the issue of multiple parents). That appears to be one record per point scored. Assuming that only players can score points, you can instead accumulate points into the round_player row.

• Your design allows multiple admins per league, not one. Please confirm.

Enjoy. Please feel free to ask specific questions. There may be clarifications: when identified, I will issue a progressed data model.

Comments

Yes.

Ok. Try this.

• There is no need to maintain one row per round_player per round per point. We can maintain a row per point_type containing total points per point_type instead.

• You need to specify the point_types (I have given rugby point types as an example).

• It is a normal table, not a Reference or "look-up" table, because the relation is Identifying.

这篇关于构建数据库关系以跟踪应用程序设置的不同变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！