Foreign key for multiple tables and columns?

问题:

I have been learning about Foreign keys and I wanted to know if the way I used it is correct in the example below:

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)
);

Thanks

Now, how can I get maximum information from just the Foreign Key if I use, let's say, PHP?

回答1:

You don't have to include item name in both tables. This is called a denormalized solution. You should have it only in the items table and only refer to the id, then if you need the name also you can join it based on the primary key(id). Otherwise it is totally OK in my opinion.

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)
);

Sometimes when performance is critical you have to use denormalized tables. It can be much faster.

Normalization is important to avoid different anomalies. If you have tables in a high level normal form then your tables won't be redundant and wont have these anomalies. For example if you have something stored in multiple locations you have to look after to keep all the redundant data up to date. This gives you a chance to do this incorrectly and end up having different anomalies.

In your situation having a foreign key helps you to keep data integrity but without a foreign key for the name you would be able to have items with names in the purchases that are not present in the items table.

This is a kind of anomaly.

There are many kinds of this, best to avoid them as long as you can.

Read more here about anomalies

In some cases you must denoramalize. So store some data redundantly because of performance issues. This way you can save some join operations that could consume much time.

Details of normalization are covered by topics of different normal forms: NF0, NF1, NF2, NF3 and BCNF

Normal forms in detail

For further details about the mathematical foundations of loseless decomposition to higher normal forms see "Functional dependencies". This is going to help you understand why you can keep the ids "redundant". Virtually they are necessary redundancy, since you need them in order to be able to later rebuild the original dataset. This is going to be the definition for the different normal forms. What level of this redundancy is allowed?

Functional Dependencies

回答2:

you've got an i_id as a primary key, you don't need to set up a name as a foreign key. and btw foreign key has to reference to unique attribute.

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)    
);