0

I have three tables like this.

location(id,name)

tour(id,name)

hotel(id,name)

above three tables(location, tour and hotel) has many images.

So the traditional way to store the images would be

location_images(id,image_url,location_id)

tour_images(id,image_url,tour_id)

hotel_images(id,image_url,hotel_id)

Instead of keeping images in three different tables like this can I make one table for store all three types of images like this?

images(id,image_url,type,foreign_key)

How do I achieve this using MySQL?

How do I make my foreign_key connect with the type?

I am using MySQL 5.7.24-0ubuntu0.18.04.1 (Ubuntu)

Thasneen
  • 97
  • 3
  • 12
margherita pizza
  • 6,623
  • 23
  • 84
  • 152
  • You can keep `type` and `type_id` as composite foreign key in image table, – Pardeep Dhingra Dec 06 '18 at 06:41
  • I think we don't need to defined type and type_id as primary key but can fetch related records using `select * from images where image_type ='hotel' and image_id= 'hotel_id'` – Pardeep Dhingra Dec 06 '18 at 06:59
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Dec 06 '18 at 13:30

1 Answers1

0

Yes you can.

CREATE TABLE images (
id INT PRIMARY KEY AUTO_INCREMENT,
image_url VARCHAR(255) NOT NULL,
image_belongs_to_type ENUM('location', 'tour', 'hotel') NOT NULL,
post_id INT NOT NULL);

when you call the image just provide the post ID with ENUM value. (location, tour and hotel)

eg :

select * from images where image_belongs_to_type ='location' and post_id = 'location_id'
select * from images where image_belongs_to_type ='tour' and post_id = 'tour_id'
select * from images where image_belongs_to_type ='hotel' and post_id = 'hotel_id'

ENUM - MySql

Thasneen
  • 97
  • 3
  • 12