将这些数据存储在MySQL模式中的正确方法是什么？ [英] What's the proper way to store this data in a MySQL schema?

问题描述

我在MySQL数据库中有一个电影。一个电影包含永远不会改变的数据属性，例如：

• 条码：025192018626


• 格式：DVD


• 运行时间：121分钟


• 光碟：1


• 标题：12猴子


• 年份：1995

表中的一行。

但是我想给我的用户完全自定义这个信息，以防万一根据他们不正确，或者如果他们只是想修改数据以某种方式显示。我不在乎为什么，我只想给我的用户一个选择，做他们想要的。

让我们说，用户＃1想改变他们的标题是12个猴子（1号货架），这就是他们所有的变化。

让我们说用户＃2想把DVD改成数字复制。 b
$ b

我们假设用户＃3希望将标题更改为十二只猴子，因为它是另一个标题。

我的问题是，如何仅将 存储到该用户名的一个字段中，而不修改原始数据？在与所有字段的单独相同的表中，除了一个字段之外的完全相同的数据？或者我可以把这一个单一的变化（例如Title）存储在某个地方，然后再参考电影其余的数据？

正确的设计方法是什么，特别是如果我有1000的用户主要在一个或两个字段上进行自定义数据修改？

解决方案

使用属性值表替代每部电影的单行。然后添加一个附加字段，指定用户，这将为原始默认值为 0 。所以表格如下所示：

  MovieID UserID属性值
 1 0标题12猴子
 1 0格式DVD 
 1 1标题Twelve Monkeys 
  

然后一个查询获得标题将如下所示：

  SELECT MovieID，IFNULL（my.Value，default.Value）AS title 
 FROM movies AS默认
 LEFT JOIN movies AS my ON default.MovieID = my.MovieID AND my.Attribute ='Title'AND my.userID = @user 
 WHERE default.UserID = 0 AND default.Attribute ='Title'
  

有些数据库设计者也喜欢使用 AttributeID 而不是一个字符串作为属性名称，以及将属性名称映射到ID的单独表。

I have a movie in a MySQL database. A movie contains data attributes that will never change, such as the following:

• Barcode: 025192018626
• Format: DVD
• Runtime: 121 min.
• Discs: 1
• Title: 12 Monkeys
• Year: 1995

It's one single row in a table.

But I want to give my user's complete customization over this information in case something is incorrect according to them or if they just want to modify how the data is displayed in some way. I don't care why, I just want to give my users an option to do what they want.

Let's say User #1 wants to change the Title for them to be "12 Monkeys (Shelf 1)" and that's all they change.

And let's say User #2 wants to change DVD to Digital Copy instead.

And let's say User #3 wants to change the Title for them to be "Twelve Monkeys" because it's the alternative title.

etc.

My question is, how do I store just that one change to that one field for that username only, without modifying the original data? In a separate identical table with all fields the exact same data except that one field? Or can I just store that one single change (Title for example) somewhere and refer back to the movie data for the rest?

What's the proper way to design this, especially if I have 1000's of users making customized data modifications mostly on just one or two fields?

解决方案

Instead of a single row for each movie, use an attribute-value table. Then add an additional field to this that specifies the user, which would be 0 for the original default. So the table looks like:

MovieID UserID  Attribute   Value
1       0       Title       12 Monkeys
1       0       Format      DVD
1       1       Title       Twelve Monkeys

Then a query to get the title would look like:

SELECT MovieID, IFNULL(my.Value, default.Value) AS title
FROM movies AS default
LEFT JOIN movies AS my ON default.MovieID = my.MovieID AND my.Attribute = 'Title' AND my.userID = @user
WHERE default.UserID = 0 AND default.Attribute = 'Title'

Some database designers also like to use an AttributeID rather than a string as the attribute name, and a separate table that maps attribute names to IDs.

这篇关于将这些数据存储在MySQL模式中的正确方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！