数据建模：使用一系列 BOOL 还是 1 个 ENUM？
假设我有一个名为“media”的表来表示音频、图像和视频。此外，假设我需要区分媒体类型。哪个更好：
TABLE: media
- media_id
- is_audio (BOOL)
- is_image (BOOL)
- is_video (BOOL)
或者
TABLE: media
- media_id
- type (ENUM: "audio", "image", "video")
如果有 1000 种媒体类型怎么办？我是否将模型分成 2 个表：
TABLE: media
- media_id
- media_type_id
TABLE: media_type
- media_type_id
- name*
- 注意：假设确实不需要知道媒体类型的“名称”（例如，您需要知道它是图像，但不在乎它是 JPG）。
Say I have a table called "media" to represent audios, images, and videos. Furthermore, say I need to distinguish between the media types. Which is better:
TABLE: media
- media_id
- is_audio (BOOL)
- is_image (BOOL)
- is_video (BOOL)
OR
TABLE: media
- media_id
- type (ENUM: "audio", "image", "video")
What if there were 1000 media types? Do I split the model into 2 tables:
TABLE: media
- media_id
- media_type_id
TABLE: media_type
- media_type_id
- name*
- NOTE: say there really is no need to know the "name" of the media type (e.g. you need to know it's an image, but don't care that it's a JPG).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
第三个选项——使用参考表——可能是最广泛支持的，也是我经常使用的。
使用第三个选项，如果
mediaTypeId列是MediaType表的外键，则可以保证数据完整性（除非您使用的 SQL 风格不支持强制外键，例如带有 MyISAM 引擎的 SQLite 或 MySQL）。您的第一个解决方案无法实现这一点 - 您可能会在该表中将
is_audio和is_image设置为1的记录。此外，当您添加更多媒体类型时，您的表格会变得更宽、更丑、更笨重。您的第二个选项（使用枚举）似乎特定于 MySQL（如果您不打算使用除 MySQL 之外的任何东西，那么这是可以的）。从它的文档页面来看，它实际上是一个字符串类型（不必要但比整数稍微占用更多空间），并且添加新选项似乎非常痛苦（请参阅 这个问题）。此外，如果您希望其他表引用媒体类型，它们都需要自己的枚举。
我会选择第三个选项，它更便携，并且它也允许其他表引用
MediaType。Your third option – using a reference table – is probably the most widely supported, and it's what I regularly use.
With the third option, if the
mediaTypeIdcolumn is foreign keyed to theMediaTypetable, you have guaranteed data integrity (unless you're using a flavor of SQL that doesn't enforce foreign keys, such as SQLite or MySQL with the MyISAM engine).You don't get that with your first solution — you could potentially have a record in that table that has both
is_audioandis_imageset to1. Additionally, as you add more media types, your table becomes wider, uglier, and clunkier.Your second option (using enums) seems to be specific to MySQL (which is okay if you don't ever plan to use anything but MySQL). From its documentation page, it's actually a string type (which needlessly yet slightly takes up more space than an integer), and it seems very painful to add new options (see this question). Additionally, if you want other tables to reference media types, they all need their own enums.
I'd go with your third option, which is more portable, and it allows other tables to reference
MediaTypeas well.