The application handles users and objects, users rate objects with 3 features (one rate per feature).
应用程序处理用户和对象,用户对具有3个功能的对象进行评级(每个功能一个速率)。
EDIT: the last sentence is unclear : by features I mean criterias shared by all the objects
编辑:最后一句不清楚:通过功能我的意思是所有对象共享的标准
How efficiently design a database for such a system ? What are the best-practices for designing a database dealing with a rating system ?
如何有效地为这样的系统设计数据库?设计处理评级系统的数据库的最佳做法是什么?
what I was thinking of:
我在想什么:
Tables:
- users
- objects
- feat1rates
- feat2rates
- feat3rates
and relationships : An object has many
和关系:一个对象有很多
- feat1rates
- feat2rates
- feat3rates
A user has many
用户有很多
- feat1rates
- feat2rates
- feat3rates
3 个解决方案
#1
Assuming you are not going to increase or decrease the number of rating features to rate, I would make a single table of ratings that would track the user, the product, and three columns (one for each feature)
假设您不打算增加或减少评级功能的数量,我会制作一个评级表来跟踪用户,产品和三列(每个功能一个)
So you have your Users table, an Objects table, and your Ratings table which has the UserID and ObjectID as a combined primary key, that way you enforce the one rating per object per user standard.
因此,您拥有Users表,Objects表和您的Ratings表,其中UserID和ObjectID作为组合主键,这样您就每个用户标准对每个对象强制执行一次评级。
#2
You want to encapsulate data in such a way that each table only contains information directly related to what it needs to deal with. Create linking tables to provide relationships between different sets of data (in this case, users and objects).
您希望以这样一种方式封装数据,即每个表只包含与其需要处理的内容直接相关的信息。创建链接表以提供不同数据集(在本例中为用户和对象)之间的关系。
I would create the following tables:
我会创建以下表格:
User - the user's base information: login, password, user ID, whatever you need.
用户 - 用户的基本信息:登录,密码,用户ID,无论您需要什么。
Object - the object to rate: its name/ID and attributes.
Object - 要评估的对象:其名称/ ID和属性。
Feature - a table describing a type of feature, with some sort of feature name/ID. This may start with your three main types, but you could always expand/modify this. You can also customize which features each object will have available for rating.
功能 - 描述功能类型的表,具有某种功能名称/ ID。这可以从您的三种主要类型开始,但您可以随时扩展/修改它。您还可以自定义每个对象可用于评级的功能。
ObjectFeature - a linking table for object and feature. Contains the primary key of each table, creating a many-to-many relationship between the two.
ObjectFeature - 对象和功能的链接表。包含每个表的主键,在两者之间创建多对多关系。
UserRating - another linking table of sorts, between the ObjectFeature and the User. Contains the primary keys of these two tables, as well as the rating assigned.
UserRating - ObjectFeature和User之间的另一个排序链接表。包含这两个表的主键以及指定的分级。
From a relational standpoint, this is a better way of organizing the data than what you presented. Through its design it makes clear how each set of data is connected and makes expandability (e.g. adding additional features to rate, having different objects have different features to rate) much cleaner.
从关系角度来看,这是一种比您提供的更好的数据组织方式。通过它的设计,它清楚地表明每组数据是如何连接的并且使得可扩展性(例如,添加额外的特征以进行评级,使不同的对象具有不同的特征以进行评级)更加清晰。
#3
Very strange design, I must say. Consider:
我必须说,非常奇怪的设计。考虑:
Object: ID, ...
// Provided features cannot be shared between objects
ObjectFeature: ID, ObjectID, ...
User: ID, ...
UserObjectFeatureRating: UserID, ObjectFeatureID, Rating
#1
Assuming you are not going to increase or decrease the number of rating features to rate, I would make a single table of ratings that would track the user, the product, and three columns (one for each feature)
假设您不打算增加或减少评级功能的数量,我会制作一个评级表来跟踪用户,产品和三列(每个功能一个)
So you have your Users table, an Objects table, and your Ratings table which has the UserID and ObjectID as a combined primary key, that way you enforce the one rating per object per user standard.
因此,您拥有Users表,Objects表和您的Ratings表,其中UserID和ObjectID作为组合主键,这样您就每个用户标准对每个对象强制执行一次评级。
#2
You want to encapsulate data in such a way that each table only contains information directly related to what it needs to deal with. Create linking tables to provide relationships between different sets of data (in this case, users and objects).
您希望以这样一种方式封装数据,即每个表只包含与其需要处理的内容直接相关的信息。创建链接表以提供不同数据集(在本例中为用户和对象)之间的关系。
I would create the following tables:
我会创建以下表格:
User - the user's base information: login, password, user ID, whatever you need.
用户 - 用户的基本信息:登录,密码,用户ID,无论您需要什么。
Object - the object to rate: its name/ID and attributes.
Object - 要评估的对象:其名称/ ID和属性。
Feature - a table describing a type of feature, with some sort of feature name/ID. This may start with your three main types, but you could always expand/modify this. You can also customize which features each object will have available for rating.
功能 - 描述功能类型的表,具有某种功能名称/ ID。这可以从您的三种主要类型开始,但您可以随时扩展/修改它。您还可以自定义每个对象可用于评级的功能。
ObjectFeature - a linking table for object and feature. Contains the primary key of each table, creating a many-to-many relationship between the two.
ObjectFeature - 对象和功能的链接表。包含每个表的主键,在两者之间创建多对多关系。
UserRating - another linking table of sorts, between the ObjectFeature and the User. Contains the primary keys of these two tables, as well as the rating assigned.
UserRating - ObjectFeature和User之间的另一个排序链接表。包含这两个表的主键以及指定的分级。
From a relational standpoint, this is a better way of organizing the data than what you presented. Through its design it makes clear how each set of data is connected and makes expandability (e.g. adding additional features to rate, having different objects have different features to rate) much cleaner.
从关系角度来看,这是一种比您提供的更好的数据组织方式。通过它的设计,它清楚地表明每组数据是如何连接的并且使得可扩展性(例如,添加额外的特征以进行评级,使不同的对象具有不同的特征以进行评级)更加清晰。
#3
Very strange design, I must say. Consider:
我必须说,非常奇怪的设计。考虑:
Object: ID, ...
// Provided features cannot be shared between objects
ObjectFeature: ID, ObjectID, ...
User: ID, ...
UserObjectFeatureRating: UserID, ObjectFeatureID, Rating