与两个实体共享地址表

Question

我的数据库中有两个实体都需要地址。客户可以有多个地址。餐厅每个ID可以有一个地址。我应该如何组织关系？我目前有一个地址表，它引用了它所属的用户，但它也可以属于一家餐厅。

地址：

CREATE TABLE IF NOT EXISTS grabatakeaway.address (
  `address_id` int(8) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `address` text NOT NULL,
  `city` varchar(128) NOT NULL,
  `state_province` varchar(128),
  `zip_post` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  FOREIGN KEY (username) REFERENCES grabatakeaway.user(username)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

用户：

CREATE TABLE IF NOT EXISTS grabatakeaway.user (
  `username` varchar(32) NOT NULL PRIMARY KEY,
  `password` varchar(128) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

餐厅：

CREATE TABLE IF NOT EXISTS grabatakeaway.restaurant (
  `restaurant_id` int(8) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `address_id` int(8) UNSIGNED NOT NULL,
  FOREIGN KEY (address_id) REFERENCES grabatakeaway.address(address_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

您需要一个'through'表。

CREATE TABLE IF NOT EXISTS (user_address
       address_id int,
       user_name varchar(32), 
      FOREIGN KEY (username) REFERENCES grabatakeaway.user(username),
      FOREIGN KEY (address_id) REFERENCES grabatakeaway.address(address_id),
      PRIMARY KEY (user_id,user_name))

地址表中不需要用户名。

CREATE TABLE IF NOT EXISTS grabatakeaway.address (
  `address_id` int(8) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `address` text NOT NULL,
  `city` varchar(128) NOT NULL,
  `state_province` varchar(128),
  `zip_post` varchar(32) NOT NULL,
  FOREIGN KEY (username) REFERENCES grabatakeaway.user(username)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

我还可以建议您为users表设置一个自动递增的id字段吗？这将导致它的索引变小，直通表的索引也变小。