Внешний ключ для нескольких таблиц и столбцов?

Question

Я изучал внешние ключи и хотел знать, правильно ли я их использовал в приведенном ниже примере:

CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
  FOREIGN KEY (i_id) REFERENCES items(i_id),
  FOREIGN KEY (name) REFERENCES items(name),
  FOREIGN KEY (id) REFERENCES user(id)
);

Спасибо

Теперь, как я могу получить максимум информации только от внешнего ключа, если я использую, скажем, PHP?

Answer 1

Вам не нужно включать название предмета в обе таблицы. Это называется денормализованным решением. Вы должны иметь его только в таблице предметов и ссылаться только на идентификатор, тогда, если вам нужно имя, вы также можете присоединиться к нему на основе первичного ключа (id). В противном случае это совершенно нормально, на мой взгляд.

CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
  FOREIGN KEY (i_id) REFERENCES items(i_id),
  FOREIGN KEY (id) REFERENCES user(id)
);

Иногда, когда производительность критична, вы должны использовать денормализованные таблицы. Это может быть намного быстрее.

Нормализация важна, чтобы избежать различных аномалий. Если у вас есть таблицы в нормальной форме высокого уровня, то ваши таблицы не будут избыточными и не будут иметь этих аномалий. Например, если у вас есть что-то, хранящееся в нескольких местах, вам нужно следить за тем, чтобы все избыточные данные были актуальными. Это дает вам возможность сделать это неправильно и в конечном итоге иметь различные аномалии.

В вашей ситуации наличие внешнего ключа поможет вам сохранить целостность данных, но без внешнего ключа для имени вы сможете иметь товары с именами в покупках, которых нет в таблице товаров.

Это своего рода аномалия.

Есть много видов этого, лучше всего избегать их, пока вы можете.

Узнайте больше здесь об аномалиях

В некоторых случаях вы должны денорализовать. Поэтому сохраняйте некоторые данные из-за проблем с производительностью. Таким образом, вы можете сохранить некоторые операции соединения, которые могут занимать много времени.

Детали нормализации охватываются темами различных нормальных форм: NF0, NF1, NF2, NF3 и BCNF.

Нормальные формы в деталях

Для получения дополнительной информации о математических основах разложения без потерь в высшие нормальные формы см. "Функциональные зависимости". Это поможет вам понять, почему вы можете сохранять идентификаторы "избыточными". Фактически они являются необходимой избыточностью, поскольку они нужны вам для того, чтобы впоследствии можно было восстановить исходный набор данных. Это будет определение для разных нормальных форм. Какой уровень этой избыточности разрешен?

Функциональные зависимости

Answer 2

У тебя есть i_id в качестве первичного ключа вам не нужно устанавливать имя в качестве внешнего ключа. и между прочим, внешний ключ должен ссылаться на уникальный атрибут.

CREATE TABLE `user`(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  `name` TINYTEXT UNIQUE NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)    
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  `name` TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
  FOREIGN KEY (i_id) REFERENCES items(i_id),
  FOREIGN KEY (id) REFERENCES `user`(id)    
);