mercredi 22 juillet 2015

Mysql database design one table many key value rows performance?

Most of the content types and categories of a project I'm working on has many tables related to each content, pages, blog_articles, blog_tags, recipes, recipes_categories, ads, banners, covers, customers etc.

What all this tables have in common and repeated in each table is title, slug, description, only few of them have other fields, like recipes has ingredients|methods|level_of_difficulty and so on.

What I was thinking is to get rid of many tables and narrow it down to only two tables, kind of like wordpress for posts and postmeta.

For example

content_table
-------------
id
parent_id
type
title
slug
body

content_meta
-------------
id
content_id
type
key
value

In the content_meta I was thinking to do something like this

content_meta
----------------------------------------------------------
| id | content_id | type   | key         | value
----------------------------------------------------------
| 1  | 1          | banner | image       | img.jpg
| 2  | 1          | banner | link_to     | google.com
| 3  | 2          | recipe | ingredient  | milk
| 4  | 2          | recipe | method      | stir it 
| 5  | 2          | recipe | difficulty  | medium

I'm very well aware that for only few types of content the content meta table is going to grow quick

What do you think of this type of design from performance point of view and should I drop it?

Aucun commentaire:

Enregistrer un commentaire