为具有多种数据类型的模型设计数据库模式

时间:2023-01-12 12:47:14

This question is about database design and how to best split your entities when they serve more than one purpose.

这个问题是关于数据库设计的,以及当实体服务于多个目的时,如何最好地分割实体。

My database schema models sports events around the world. What type of event and sport, when and where it is played, what the participants are and the winner and so on. One of the entities in the schema is Country which keeps track of in which country in the world the sport event took place in.

我的数据库模式模拟了世界各地的体育赛事。什么类型的活动和运动,何时何地进行,参与者是谁,获胜者是谁等等。该模式中的一个实体是国家,它跟踪体育赛事在世界上哪个国家举行。

This works well, but then I also need to add auxilliary data to the Country model, which is not related to the sport events model per se, but is required for rendering the data nicely on a web page.

这很有效,但是我还需要向国家模型添加辅助数据,这与体育赛事模型本身没有关系,但是需要在web页面上很好地呈现数据。

Examples of that data is the countrys flags sprite offset in the sprite image, a long description of the country, the adjective for the country (China - chinese etc), number of visitors on the country page and the subjective importance of the country on a scale from 1-5 (events in countries rated five are shown on the front page).

的例子,数据是国家国旗雪碧抵消的精灵形象,长描述的国家,国家的形容词(中国-中国等),游客数量的页面和主观的重要性国家规模从1 - 5(事件国家评为五在首页显示)。

I could easily put all those attributes on the Country object itself, but it seems wrong, and pollutes my clean sports event schema. I don't think the structure of the data should be mixed with details like how to render it nicely... So the question is how I should organise it instead?

我可以很容易地将所有这些属性放在Country对象本身上,但这似乎是错误的,并且污染了我的clean sports event模式。我不认为数据的结构应该与细节相混合,比如如何把它做的很好……所以问题是我应该如何组织它?

1 个解决方案

#1


3  

I would keep this data on a 'Countries' table and create a FK to your sports data. This will allow you to easily maintain each Country's attributes as you will only have to update them in one place and the changes will take effect everywhere it is referenced. I don't think this pollutes your data as the information is relevant to your application. Furthermore, if you do try to separate this data it will only make your schema more complex and maintenance more difficult.

我将把这些数据保存在“国家”表上,并为您的体育数据创建一个FK。这将使您能够轻松地维护每个国家的属性,因为您只需在一个地方更新它们,这些更改将在引用它们的任何地方生效。我认为这不会污染您的数据,因为信息与您的应用程序相关。此外,如果您尝试分离这些数据,只会使您的模式更加复杂,维护更加困难。

#1


3  

I would keep this data on a 'Countries' table and create a FK to your sports data. This will allow you to easily maintain each Country's attributes as you will only have to update them in one place and the changes will take effect everywhere it is referenced. I don't think this pollutes your data as the information is relevant to your application. Furthermore, if you do try to separate this data it will only make your schema more complex and maintenance more difficult.

我将把这些数据保存在“国家”表上,并为您的体育数据创建一个FK。这将使您能够轻松地维护每个国家的属性,因为您只需在一个地方更新它们,这些更改将在引用它们的任何地方生效。我认为这不会污染您的数据,因为信息与您的应用程序相关。此外,如果您尝试分离这些数据,只会使您的模式更加复杂,维护更加困难。