MySQL: several parents identifying their children from one table

Question

I am designing a data model for tourism-site. I have a table for places (countries, resorts, hotels) and a table for tours. The tables are pretty different in fields so the cant be merged into one. Both of them have usual auto_increment as id. Places and tours have photos so there is a third table for photos. This table has 'parent' field in which I plan to store the parent (place or tour) id.

Which is the best way to design these tables? One table for all photos and two tables as 'parents' for the photos. For now I have added 'parent_type' column to photos table, so when my script displays a tour *it calls photos by its (parent) id and type (parent_type) 'tour' from the photos table*...

Upd: Is there a more graceful solution? With just 3 tables and no 'parent_type' column?

(cant post a diagram... here's the link http://share.xmind.net/yentsun/tourism-site-data-model/)

Answer 1

I had the same situation a while ago. I used a 'set' type for the parent_type. Never store names for your type, use integers because they can be read much faster. And also place indexes on your foreign keys.

Answer 2

Country, hotel and resort are sub-type of a place. The place table contains all fields common to places, while country, hotel and resort tables contain fields specific to each one. One tour contains many places, one place can be a part of many tours.

Here is example code for Place and Country -- it is T-SQL, but you'll get the idea.

CREATE TABLE Place
( 
    PlaceID        int  NOT NULL ,
    Type                varchar(2) 
);

ALTER TABLE Place
ADD CONSTRAINT PK_Place PRIMARY KEY  CLUSTERED (PlaceID ASC)
;
ALTER TABLE Place
ADD CONSTRAINT FK1_Place FOREIGN KEY (ParentID) REFERENCES Place(PlaceID)
;

CREATE TABLE Country
( 
    PlaceID        int  NOT NULL 
);

ALTER TABLE Country
ADD CONSTRAINT PK_Country PRIMARY KEY  CLUSTERED (PlaceID ASC)
;
ALTER TABLE Country
ADD  CONSTRAINT FK1_Country FOREIGN KEY (PlaceID) REFERENCES Place(PlaceID)
     ON DELETE CASCADE
     ON UPDATE CASCADE
;

UPDATE after comment
Sorry, four tables is my best for this one.

Answer 3

There are no Parents involved - you just have photos with two atttributes - Place and Tour.

So use a Photos table with two foreign keys, one for Tour, the other for Place. And then of course a Tours table and a Places table.

If you need to know which Tours went to which Places, deal with it directly with a Tour_Places table which justifies itself independently.

As for "Parentness", this solution still lets you identify, for a Tour (or Place), which Photos are associated.