数据库设计建议
我有一个可用的 SQLite 数据库，其中保存有关视频文件的信息。目前的设计如下图所示。不过，老板决定做出一些改变。
FileProperties 表当前使用文件名作为主键。但是，PK 现在必须是 fileName 和（文件）location 的复合键，无论如何，这更有意义。
如果这样做，在其他表中引用该复合键作为外键的最佳方法是什么？我正在考虑创建一个单独的表来保存自动递增主键、文件名和位置。然后 PK 可以用作所有其他表的外键引用。
或者，将 fileName 和 location 设为当前 FileProperties 表中的复合键，并添加一个可用作参考的新字段和此字段必须是自动递增且在表中唯一。
我在设计数据库方面没有太多实践经验，因此非常欢迎针对我的问题或我当前的设计提出任何建议。
I have a working SQLite database that holds information about video files. The current design is as pictured below. However, the boss has decided to make some changes.
The FileProperties table currently uses the file name as the primary key. However, the PK now must be a compound key of both fileName and (file) location, which makes more sense anyway.
If this is done, what would be the best way to reference this compound key as a foreign key in the other tables? I was thinking of either creating a separate table that holds an auto-incrementing primary key, fileName and location. Then the PK can be used as a foreign key reference with all the other tables.
Or, make fileName and location a composite key in the current FileProperties table and add a new field that can be used as a reference and this field must be auto-incrementing and unique in the table.
I haven't had much practical experience with designing databases so any advice with my problem or my current design would be very welcome.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
绝对使用自增主键。为了确保数据完整性，请在（文件名，位置）列中创建唯一索引。
以下 wiki 文章简要讨论了自然密钥的优缺点。自然密钥是直接从数据中获取的密钥。在您的情况下，这将是（文件名，位置）的复合键。简而言之，自然密钥减少了数据所需的物理空间，但代价是在所有关系中传播对密钥的更改。
我（几乎）总是在表上有一个自动递增的 id，即使有一个自然键可供使用。
Absolutely use an auto-incrementing primary key. To ensure data integrity, create a unique index across the (filename,location) columns.
The following wiki article talks briefly about the pros and cons of a natural key. A natural key is a key taken directly from the data. In your case, that would be the composite key of (filename,location). In short, a natural key reduces physical space required by the data, at the cost of propagating changes to the key across all relations.
I (nearly) always have an auto-incrementing id on a table, even if there is a natural key available to be used.
添加自增FileId主键。
添加位置+文件名的唯一约束。
避免使用复合主键。
Add auto-incremented FileId primary key.
Add unique constraint for Location + FileName.
Avoid using compound primary keys.