mysql - 基本数据库设计 : struggling to map dictionary thinking to relational thinking. 如何对这些数据建模？

Question

我已经编程相当长的时间了，但是整个数据库的事情已经完全被我忽视了。我现在正在尝试了解其中的一些内容，但我正在为如何建立关系而苦苦挣扎。

目的:

我在一家摄像店工作，因此我们有大量需要用元数据标记的文件。在文件系统上，各个文件按顺序编号，并存储在一个由项目 ID 号和包含所有元数据的 csv 文件组成的文件夹中。

例如

Projects/ 
   |
   | - Project_ID_4539485/
              | - metatdata.csv
              | - Rec_01.mp4
              | - Rec_02.mp4
              | - ...
              | - Rec_N.mp4
   | - Project_ID_4539485/
            etc...

我们将元数据保存在 csv 文件中，如下所示:

| filename | Title | Album | Artist | etc.. |

我有一个 Python 脚本，它运行并根据该元数据 csv 自动标记所有单独的文件。该项目试图扩展该脚本，使其不再需要人类接触。

建模:

我想跟踪三件主要事情:

1. 每个文件夹中的元数据
2. 记录哪些文件已被标记以及何时标记
3. 脚本当前正在监视的所有文件夹的列表。

如果我将其存储为 JSON，对我来说以这种嵌套字典的方式思考相对容易，但当我尝试将其转换为关系内容时，我会崩溃。

作为一堆字典和列表，我将其设置如下:

watch_folders = {
    'Project_ID_4539485' : {
        'metadata' : {'Title' : 'bla', 'artist' : 'foo', etc..},
        'indexed' : [[filename, checksum, last_modified], 
                     [filename, checksum, last_modified],
                     etc..]
        },
    'Project_ID_4539682' : {
        'metadata' : {'Title' : 'bla', 'artist' : 'foo', etc..},
        'indexed' : [[filename, checksum, last_modified], 
                     [filename, checksum, last_modified],
                     etc..]
        },
    # and so on... 
}

尝试翻译我最终得到了这样的结果:

create table metadata (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename INTEGER, 
    title TEXT, 
    artist TEXT,
    album TEXT, 
    year TEXT,
    genre TEXT, 
    publisher TEXT, 
    url TEXT
);

create table indexed (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    filename TEXT, 
    checksum TEXT,
    date_modified TEXT
);

create table project_folder (
    project_id INTEGER PRIMARY KEY, 
    metadata_key INTEGER, 
    indexed_key INTEGER
);

但我不确定如何将所有表格相互“附加”。

例如，从元数据开始。我需要一把将元数据表与项目表联系起来的键。我不太确定如何做到这一点，因为元数据中的键都是唯一的并且与外部文件相关——更不用说这些键在每个项目文件夹中都是相同的，因为各个文件都是按顺序编号的。

因此，解决方案似乎只是将 project_id 字段添加到元数据表中。

create table metadata (

    project_id PRIMARY KEY,
    filename INTEGER, 
    title TEXT, 
    artist TEXT,
    album TEXT, 
    year TEXT,
    genre TEXT, 
    publisher TEXT, 
    url TEXT
);

但这似乎违反了我对标准化的理解，因为现在每一行都会有一条重复的信息。我在尝试附加索引表时遇到了同样的问题..

添加project_id列实际上是解决问题的方法，还是有更好的方法？

Answer 1

你的模式对我来说毫无意义。

一个 project_folder 将关联多个 indexed，因此您必须添加 indexes.project_id，而不是 project_folder.indexed_key >.

此外，我会将 project_folder 与 metadata 合并，因为它们是一对一映射的。

如果您更喜欢单独的表，我确实更喜欢 metadata.project_id 而不是 project_folder.metadata_key。

关于project_folder.filename，我不明白为什么使用它!

所以我的建议是:

create table project_metadata (
    project_id INTEGER PRIMARY KEY, 
    filename INTEGER, -- ???
    title TEXT, 
    artist TEXT,
    album TEXT, 
    year TEXT,
    genre TEXT, 
    publisher TEXT, 
    url TEXT
);

create table indexed (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER REFERENCES folder_metadata.project_id,
    filename TEXT, 
    checksum TEXT,
    date_modified TEXT
);