数据库设计NBA [英] Database Design NBA

问题描述

我是数据库设计的新手，所以很抱歉，这是一个显而易见的初学者问题。我使用python和sqlalchemy，尽管我认为这无关紧要（以下示例代码为伪代码），尽管可能是错误的。我浏览了之前的一些问题，但没有解决。无论如何，关于这个问题。这里的目标是建立一个NBA信息数据库，该数据库将包含所有已打比赛的信息以及每个球员和每个比赛的得分。该数据库的设计方法有几种。

I am new to database design so sorry if this is obvious beginner question. I use python and sqlalchemy though I don't think that is relevant (the sample code below is psuedo code), though may be wrong. I have looked through some previous questions and didn't see this addressed. Anyway, on to the question. The goal here is to develop a database of NBA information which will have info on all games played and also box scores for every each player, for each game. There are a couple ways this DB can be designed.

Game(game_id, date, home_name, away_name, score)
Box_Score(game_id, player_name, date, points, rebounds)

在这种情况下，如果我想得到洛杉矶湖人队所有的比赛我都可以做

In this situation if I want to get all the games the Los Angeles Lakers played I can just do

query(Game).filter(home_name=="lakers" or away_name=="lakers").all()
query(Box_Score).filter(player_name="kobe bryant")

这里是设计数据库的第二种方法：

Here is the second option for how to design this database:

Game(game_id, date, home_name=(foreignkey=Team.team_name), away_name, score)
Box_Score(game_id, player_name=foreignkey=Player.player_name), date, points, rebounds)
Team(team_name, home_games=relationship("Game"))
Player(player_name, box_scores=relationship("Box_Score"))

然后我可以做

query(Team).filter(name=="lakers").first().games
query(Player).filter(name=="kobe bryant").first().box_scores

一方面，似乎使用关系数据库的全部目的是像在情况2中那样进行设置。另一方面，我不确定它会给我带来什么额外的功能。所以我想我的问题是，您推荐哪种设计？这两种设计是否存在一些优点或缺点，这些优点或缺点会在我看不到的线下变得显而易见？并且，如果您推荐不使用表关系的简单设计＃1，为什么我要存储大量的相关信息却不需要使用关系数据库呢？谢谢!!

On the one hand it seems like the whole point of using a relational database is to set it up like in situation #2. On the other hand, I am not sure what extra functionality it gives me. So I guess my question is, which design do you recommend? Are there some benefits or disadvantages to either design that will become apparent down the line which I cannot see yet? And if you recommend the simpler design #1 which does not use table relationships, why is it that I am storing a decent amount of related information but don't need to use relational database? Thanks!!

推荐答案

任何数据库的理想数据模型都是高度主观的。如果您是数据库设计的新手，则可能在之后创建了自己的应用程序并对其进行了长时间的测试之后，您才会找到理想的架构。我建议您阅读一些设计基础知识，尤其是数据库规范化，因为您可能会从中受益高度标准化的架构，可以用许多不同的方式引用数据。高度规范化的数据库如果很大（可能看起来不那么大），可能会对性能部门造成损害，但是您始终可以使用材料视图或其他缓存方法。

The ideal data model for any database is highly subjective. If you are new to database design, you probably will not find the ideal schema until after you have created your application and tested it for an extended period of time. I would recommend reading up on some design basics, particularly Database Normalization, since you would probably benefit from a highly normalized schema, where data can be referenced in many different ways. Highly-normalized databases can suffer in the performance department if very large (which this does not seem like it would be), but you can always de-normalize data through the use of Materialized Views or other methods of caching.

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