create relationship between tables

I have tables for dogs, cats , horses containing rows of information about them , i wan开发者_如何转开发t to create a table photo where info about photos of each can be created and so want to establish one-to-many relation b/w name attribute of each table with table photo . I am using name attribute in each table and it is set to unique but not primary , i want a way to join them so that for each name if there are multiple entries in photo table they could be shown.

I strongly recommend you use an int primary key rather than name for linking your tables.

If you need to change the name of any animal, the links effectively break.

Simple example:

CREATE TABLE `animals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `species` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
);


CREATE TABLE `images` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `animal_id` int(10) NOT NULL,
  `image_url` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

You also might do well to create a third table for species and make that a link rather than a text field.

The idea is ease of management and future-proofing.

A query to get data for a specific animal and all its images would be like this:

SELECT a.name, a.species, i.image_url
FROM animals a
LEFT JOIN images i ON (i.animal_id = a.id)
WHERE a.id = 123;

name     species     image_url
-----    -----       -----
Fido     dog         images/fido1.jpg
Fido     dog         images/fido2.jpg

Other queries are possible, but this scheme allows you to have animals with the same name and they won't conflict.