CODEWITHSUNDEEP
mysqlinheritance
Nguyễn Văn Huy
Nguyễn Văn Huy

Reputation: 21

MYSQL: how to create inherit table design

I would like to design a supertype named 'post' and two subtypes 'blog_post' and 'image_post'.

How can I implement this model in MySQL?
How can I insert data into this model to establish complete post of each subtype?

Upvotes: 1

Views: 263

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Unfortunately there is no inheritence with tables. There are two common ways to go around this problem:

1) Have just one table and leave columns null:

  • table post (id_post, title, time_posted, thumbnail, body, image_path, category)

You would have a constraint to ensure that either thumbnail and body or image_path and category are filled. (MySQL lacks check constraints, so you would write a before insert/update trigger for this.)

2) Have a parent and child tables that you join:

  • table post (id_post, title, time_posted, id_blog_post, id_image_post)
  • table blog_post (id_blog_post, thumbnail, body)
  • table image_post (id_image_post, image_path, category)

There are other ways to address the problem, such as having blog_post and image_post only, both featuring the columns title and time_posted. Or take solution 2 and add id_post to blog_post and image_post to double link the records. Or add id_post to blog_post and image_post and remove id_blog_post and id_image_post from post.

What model you choose depends on the way you want to work with the tables and personal preference.

Upvotes: 1

shA.t
shA.t

Reputation: 16968

I think, inheritance in database design is just telling about properties that refers to columns and is like this:

Shape { Long Id, String Name }

Circle :: Shape { Long Id, Integer Radius, Long Shape.Id, String Shape.Name }
Square :: Shape { Long Id, Integer Width, Long Shape.Id, String Shape.Name }

Goes to:

[Shape] table:
Id bigint^
Name varchar(20)

[Circle] table:                       [Square] table
Id bigint^                            Id bigint^
ShapeId bigint*                       ShapeId bigint*
Radius int                            Width int

Upvotes: 1

Related Questions