属性可以指定一个表还是另一个表？ [英] Can an attribute designate one table over another?

问题描述

我正在创建一个体育统计数据库。有了它，我想为许多类型的运动的游戏/比赛统计信息。例如，这个数据库将能够告诉你卡罗莱纳黑豹在09-10赛季（足球）得分多少次，或者迈阿密热火在他们最后一场比赛（篮球）中多少罚球。

我在设计一个更基本的称为Matches的表时遇到麻烦。匹配表格有以下列：

• ID（PK match_id ）

< （FK team_1_performance_id 和 team_2_performance_id



效果表包含：






• ID（PK perf_id ）


• 小组ID（FK team_id ）


• 最重要的是，所有其他统计数据：罢工次数（*）


• >
  






（*）问题是，使表演表与各自的运动相关？例如，棒球比赛有罢工，但足球和曲棍球没有（也没有任何其他运动，我可以想到）。我不希望我的「成效」表格有一个专门用于警示的栏，而且只有一部分纪录相关。





还是我？也许我的设计应该是不同的一起？你会怎么样？





现在，我不知道这是否可能，但我有一个想法是可能在匹配中包含某种perfomance表ID列，指的是不同的性能表。所以当我查询比赛的表现时，它会查看一个特定的表。这是这个问题的标题来自哪个属性可以指定一个表在另一个？想象一下SELECT team_1_performance.strikes FROM Matches INNER JOIN appropriate_performance_table AS team_1_performance WHERE Matches.performance_table_id ='Baseball'如果可能，我如何指定appropriate_performance_table？





想法我已经为所有的运动，如Rugby_Matches或Football_Matches创建匹配表，然后为这些运动，如Rugby_Perfomances或Football_Performances相应的性能表。





如果可以，尝试保持您的回答MySQL特定。





谢谢！

解决方案

如果你去Astander这里的查询，你需要为所有的胜利





如果你以正确的方式，在列中，你会看到

  SELECT * FROM Football_stats fs 
 WHERE fs.team_fk =（解析到Carolina）
 fs.outcome ='Win'and fs.touchdowns> ; fs.interceptions 
  

你会得到的EAV世界

  SELECT game_id FROM football_stats WHERE fs.team_fk = [Carolina] and stat_type ='Outcome'a​​nd stat_value ='Wins'）
 INTERSECT 
 SELECT game_ID FROM 
（select game_id，stat_value FROM football_stats WHERE fs.team_fk = [Carolina] and stat_type ='Touchdown'）tds，
（select game_id，stat_value from football_stats WHERE fs.team_fk = [Carolina] and stat_type ='Interceptions'）ints 
 WHERE 
 tds.stat_value> ints.stat_value 
  

所有这一切都给出了一个满足查询的game_ids列表，如果你想要其余的价值观，像点和反对，它是整个新的轮回数据。




I'm creating a sports statistics database. With it, I'd like to catalog game/match statistics for many types of sports. For example, this database would be able to tell you how many touchdowns the Carolina Panthers scored in the 09-10 season (football), or how many free throws were made by the Miami Heat in their last game (basketball).

I'm having trouble designing one of the more fundamental tables called Matches. The Matches table has columns for:

• ID (PK match_id)
• date of play (play_date)
• IDs referring to the performances of the teams (FK team_1_performance_id and team_2_performance_id) in table Performances.

The Performances table holds:

• ID (PK perf_id)
• team ID (FK team_id)
• And most importantly, all the other stats like: number of strikes (*)
• average rushing yards per play (*)
• percent of 3-pointers made (*)

(*)The problem is, how can I make the Performances table relevant to the respective sport? For example, baseball games have strikes, but soccer and hockey do not (nor does any other sport I can think of). I don't want my Performance table to have a column for strikes when its only going to be relevant for a portion of records.

Or do I? Perhaps my design should be different all together? How would you go about this?

Now, I don't know if this is possible, but one idea I had was to maybe include some kind of perfomance table ID column in Matches that refers to different performance tables. So that when I query a match's performances, it will look at a specific table. This is where the title of this question comes from (Can an attribute designate one table over another?). Imagine "SELECT team_1_performance.strikes FROM Matches INNER JOIN appropriate_performance_table AS team_1_performance WHERE Matches.performance_table_id = 'Baseball'" How could I designate appropriate_performance_table, if that's even possible?

And another idea I had was to create matches tables for all the sports, like Rugby_Matches or Football_Matches, and then respective performance tables for those sports, like Rugby_Perfomances or Football_Performances. This just seems like a lot of tables that represent somewhat similar things.

If you can, try to keep your responses MySQL specific.

Thanks!

解决方案

If you go with Astander here's the query you'd need for all the wins for Carolina when the scored more touchdowns then interceptions.

If you did it the right way, in columns, you'd see

SELECT * FROM Football_stats  fs
WHERE fs.team_fk = (something that resolves to Carolina)
fs.outcome = 'Win' And fs.touchdowns > fs.interceptions

in the EAV world you'd get

SELECT game_id FROM football_stats WHERE fs.team_fk = [Carolina] and stat_type = 'Outcome' and stat_value = 'Wins')
INTERSECT
SELECT game_ID FROM 
  (SELECT game_id, stat_value FROM football_stats WHERE fs.team_fk = [Carolina] and stat_type = 'Touchdown' ) tds,
  (SELECT game_id, stat_value FROM football_stats WHERE fs.team_fk = [Carolina] and stat_type = 'Interceptions') ints
WHERE
  tds.stat_value > ints.stat_value

And all that did was give you a list of game_ids that satisfy the query, if you want the rest of the values, like points for and against, it's whole new rounds through the data.

这篇关于属性可以指定一个表还是另一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！