使用日期字段作为表之间的引用是否明智?

I have a two tables events and bookings, which are linked by event_id. Example:

我有两个表事件和预订，它们通过event_id链接。例子:

CREATE TABLE `bookings` (

    `booking_id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_id`  int(10) UNSIGNED NULL DEFAULT NULL,
    `fullname`  varchar(80) NOT NULL,
    `phone`  varchar(20) NULL DEFAULT NULL,
    `note`  text NULL,
    `date_created`  datetime NOT NULL,
    `date_updated`  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`booking_id`),
    FOREIGN KEY (`event_id`) REFERENCES `events` (`event_id`) ON DELETE SET NULL ON UPDATE CASCADE,

    INDEX `event_id` USING BTREE (`event_id`),
    INDEX `source_id` USING BTREE (`source_id`) 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT;

    #events
CREATE TABLE `events` (
    `event_id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
    `date`  date NOT NULL ,
    `title`  varchar(255) NULL DEFAULT NULL ,
    `description`  text NULL,

    PRIMARY KEY (`event_id`, `date`),
    UNIQUE INDEX `date` USING BTREE (`date`) ,
    INDEX `event_id` USING BTREE (`event_id`) 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT;

I'm wondering, can I use date field as reference between tables, as it's consume 1 byte less comparing to integer and it is easier to read and operate with single table rather than combined. But I'm not sure how does it affect indexing and SQL query performance.

我想知道，我是否可以使用日期字段作为表之间的引用，因为它比整数少消耗1字节，而且使用单个表比使用组合表更容易读取和操作。但我不确定它如何影响索引和SQL查询性能。

2 个解决方案

#1

It is not a good idea to use a pure data field as a link between tables. A date field appears to be a general purpose field intuitively, so another engineer would eventually update the field for sensible application purposes. If it were used as a table link, updates would break the linkage.

使用纯数据字段作为表之间的链接不是一个好主意。从直觉上看，日期字段似乎是通用字段，因此另一名工程师最终将更新该字段，以实现合理的应用目的。如果它被用作表链接，那么更新将中断链接。

Why not create a specific field for table linkage purposes? Sure, it might cost 4 or 8 bytes per record, but have you looked at the cost of storage? It is almost free. On the other hand, a non-intuitive use of a field might cost quite a bit in future errors, misunderstandings, confusion, and downtime.

为什么不为表链接创建一个特定的字段呢?当然，每条记录可能需要4到8个字节，但是你查看过存储的成本吗?它几乎是免费的。另一方面，对字段的非直观使用可能会在将来的错误、误解、混乱和停机中造成相当大的损失。

#2

I wouldn't do it. What happens when the date moves for an event? What if you have two events on the same day?

我不会这样做。当事件的日期移动时会发生什么?如果你在同一天有两个活动呢?

You already have a generated natural key you know won't change (event_id), so it's much safer to use that.

您已经生成了一个您知道不会更改的自然密钥(event_id)，因此使用它要安全得多。

#1

#2