将此数据存储在MySQL模式中的正确方法是什么？

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

我在MySQL数据库中有一部电影。电影包含永不改变的数据属性,如下所示:

Barcode: 025192018626
Format: DVD
Runtime: 121 min.

运行时间:121分钟

Discs: 1
Title: 12 Monkeys

标题:12只猴子

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.

假设用户#1想要将他们的标题更改为“12 Monkeys(Shelf 1)”,这就是他们改变的全部内容。

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

让我们说用户#2想要将DVD改为数字拷贝。

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

让我们说用户#3想要将他们的标题改为“十二只猴子”,因为它是替代标题。

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?

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

4 个解决方案

#1

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:

而不是每部电影的单行,使用属性值表。然后为此添加一个指定用户的附加字段,对于原始默认值,该字段为0。所以表格如下:

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.

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

#2

I suggest there is no 'proper' way. But you might like this...

我建议没有'正确'的方式。但你可能会喜欢这个......

Your Movie table stays as is. (I assume there is an id.)

您的电影表保持不变。 (我假设有一个id。)

Another table, UserMovie with the same columns except:
- All the columns except id are NULL
- It has another column: user NOT NULL
- PRIMARY KEY(id, user)

另一个表,UserMovie具有相同的列,除了:除了id之外的所有列都是NULL它有另一列:user NOT NULL PRIMARY KEY(id,user)

When a user modifies something, use INSERT INTO UserMovie .. ON DUPLICATE KEY UPDATE .. to change whatever field(s) he wants to set. Note that IODKU will INSERT a new row if none exists, or UPDATE the existing row (because the user is modifying another column). For example, to override just the "title" for id=$id,

当用户修改某些内容时,请使用INSERT INTO UserMovie .. ON DUPLICATE KEY UPDATE ..更改他想要设置的任何字段。请注意,如果不存在,IODKU将插入新行,或者更新现有行(因为用户正在修改另一列)。例如,要仅覆盖id = $ id的“标题”,

INSERT INTO UserMovie
    (id, title)
    VALUES
    ($id, '$title')
ON DUPLICATE KEY UPDATE
    title = '$title';

When a user wants to see what he has,

当用户想要看到他拥有的东西时,

SELECT  coalesce(u.title, m.title) AS title,
        coalesce(u.format, m.format) AS format,
        coalesce...
    FROM Movie AS m
    LEFT JOIN UserMovie AS u
            ON u.id = m.id
            AND u.user = $user
    WHERE m.id = $id;

The COALESCE quietly pics either u.xxx if NOT NULL, or m.xxx.

如果NOT NULL或m.xxx,COALESCE会静默地显示u.xxx。

This design has the advantage of being very compact. (NULLs take almost no space.)

这种设计具有非常紧凑的优点。 (NULL几乎没有空间。)

If a user changes the "title" twice, only the last version is kept.

如果用户两次更改“标题”,则仅保留最后一个版本。

To "revert" the title:

要“恢复”标题:

UPDATE UserMovie SET title = NULL
    WHERE id = $id
      AND user = $user;

(Sure, this could leave a row of all NULLs, but the rest of the code still works.)

(当然,这可能会留下一行所有NULL,但其余的代码仍然有效。)

#3

My first thought is, Why would you want to do this?

我的第一个想法是,你为什么要这样做?

My second thought is to have a customizations table something like

我的第二个想法是有一个类似的自定义表

+--------+---------+-------------+------------+
| userid | barcode | column_name | custom_val |
+--------+---------+-------------+------------+

then, when a user queries the system, look up their userid and the column_name in the customizations table to find the alternative display value.

然后,当用户查询系统时,在自定义表中查找其用户标识和column_name以查找替代显示值。

This would allow a user to replace one value in one row. To replace one value in all rows where it exists would be a much more difficult proposition.

这将允许用户替换一行中的一个值。要在存在它的所有行中替换一个值将是一个更加困难的命题。

#4

A good design is not perfect for all situations. However there is a perfect design for a situation.

一个好的设计并不适合所有情况。然而,对于某种情况,有一个完美的设计。

Ask yourself again: 1) what the purpose of this design and, 2) how are you going to retrieve the data from the design.

再问自己:1)这个设计的目的是什么,2)你将如何从设计中检索数据。

According to your question, if a movie never changes its attributes, a flat one-row table is perfect:

根据你的问题,如果电影永远不会改变它的属性,那么平面的单行表是完美的:

table: movie
id | barcode      | format_id | runtime | disc | year_made | title
---+--------------+-----------+---------+------+-----------+-----------
1  | 025192018626 | 1         | 121     | 1    | 1995      | 12 Monkeys

And you might need a foreign table movie_format

你可能需要一个外表table_format

table: movie_format
id | format
---+-------
1  | DVD

Above design is very speedy for search.

以上设计非常快速搜索。

Now you want to save any changes, or alternative information, however not sure what they are. In this case, a meta table is more suitable. Meta table is usually perfect if you only need to display according to a primary key (a movie) and not used for a search:

现在您要保存任何更改或替代信息,但不确定它们是什么。在这种情况下,元表更合适。如果您只需要根据主键(电影)显示并且不用于搜索,则元表通常是完美的:

table: movie_meta
id | movie_id | user_id | created             | meta      | info
---+----------+---------+---------------------+-----------+---------
1  | 1        | 123     | 2016-01-28 11:22:33 | format_id | 2
2  | 1        | 456     | 2016-01-28 11:55:33 | disc      | 3
5  | 1        | 666     | 2016-07-14 12:58:55 | title     | 十二头傻猴子

You can make movie_meta.meta as enum so that you do not need to worry about a new lookup table

您可以将movie_meta.meta设置为枚举,这样您就不必担心新的查找表

#1