在数据库中创建新主键的最佳选择是什么？

imagine you have a N-N relationship with three tables created. By example, image you have a table for CUSTOMER(id_customer, name), the car table CAR(id_car, model, num_doors, ...) (id_customer and id_car are the primary keys for their respective tables). Now you create the table PURCHASE to relate CUSTOMER with CARS and create the FOREIGN KEYS... The question is what is the best choice?

想象你有一个N-N关系,创建了三个表。例如,图像有CUSTOMER表(id_customer,name),汽车表CAR(id_car,model,num_doors,...)(id_customer和id_car是各自表的主键)。现在你创建表PURCHASE以将CUSTOMER与CARS联系起来并创建FOREIGN KEYS ......问题是什么是最佳选择?

1.PURCHASE(id_customer, id_car, date_purchase) -> Primary keys are (id_customer, id_car). Foreign keys are (id_customer->CUSTOMER.id_customer and id_car->CAR.id_car).
2.PURCHASE(id, id_customer, id_car, date_purchase) -> Primary keys are (id), Foreign keys are (id_customer->CUSTOMER.id_customer and id_car->CAR.id_car).

My question is what is the best option, 1 or 2, taking into account efficiency and optimization in database.

我的问题是什么是最佳选择,1或2,考虑到数据库中的效率和优化。

1 个解决方案

#1

For a Many-to-Many mapping, do it this way.

对于多对多映射,请这样做。

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X
    INDEX      (y_id, x_id)             -- When starting with Y
) ENGINE=InnoDB;

Notes:

Lack of an AUTO_INCREMENT id for this table -- The PK given is the 'natural' PK; there is no good reason for a surrogate.

此表缺少AUTO_INCREMENT ID - 给出的PK是'自然'PK;代理人没有充分的理由。

"MEDIUMINT" -- This is a reminder that all INTs should be made as small as is safe (smaller ⇒ faster). Of course the declaration here must match the definition in the table being linked to.

“MEDIUMINT” - 这提醒所有INT应该做得尽可能小(更小⇒更快)。当然,此处的声明必须与链接到的表中的定义相匹配。

"UNSIGNED" -- Nearly all INTs may as well be declared non-negative

“未签名” - 几乎所有的国际单位都可能被宣布为非负面的

"NOT NULL" -- Well, that's true, isn't it?

“NOT NULL” - 嗯,这是真的,不是吗?

"InnoDB" -- More effecient than MyISAM because of the way the PRIMARY KEY is clustered with the data in InnoDB.

“InnoDB” - 比MyISAM更有效,因为PRIMARY KEY与InnoDB中的数据聚集在一起。

"INDEX(y_id, x_id)" -- The PRIMARY KEY makes it efficient to go one direction; the makes the other direction efficient. No need to say UNIQUE; that would be extra effort on INSERTs.

“INDEX(y_id,x_id)” - PRIMARY KEY使向一个方向有效;使另一方向有效。不用说UNIQUE;这将是对INSERT的额外努力。

In the secondary index, saying justINDEX(y_id) would work because it would implicit include x_id. But I would rather make it more obvious that I am hoping for a 'covering' index.

在二级索引中,说justINDEX(y_id)会起作用,因为它会隐式包含x_id。但我宁愿让我更明显地希望有一个“覆盖”指数。

See Cookbook on building indexes

请参阅有关构建索引的Cookbook

#1