Planning simple CMS: How much relation

central-management-serverdatabase-designdatabase-theorydatabase-tuningoptimization

I'm currently in the process of designing the database for a rather minimalistic Content Management System I'm working on alone. It should allow scalability while remaining speed, especially on boxes with low-end hardware.

As I am doing database design this carefully for the first time I am struggling more than a bit and could use some help. I've made two models for the database, which both fail to satisfy me:

Model 1: Everything dynamic is an entity (i.E. menu items, blog posts, projects):

I want to achieve maximal scalability by using the same base for every content type, but one of my examples already fails: the menu. Not all menu item's might have a URL (dropdown-item's for example) and some might be separator, which would be impossible to achieve with this.
Model 2: Posted dynamic content are entities (i.E. blog posts, projects, comments):

Not as scalable as Model 1, but most likely faster. Still, contents won't need a preview (which is shown as description on the landing page) as they're only shown on their parent's pages.

The Project-class is just an example class for content-type-specific metadata, in this case the info for a button that could lead to a github repository, an image gallery and so on.
I've got the feeling I'm completely failing at this, I also fear that this will result in hundreds of queries when trying to get comments on something (checking for parent's on each one for correct nesting etc.).

I'll be glad for everyone getting me an advice on how to approach this better, a tip on how to view this from a different angle or anything else that could point me in the right direction.

Best Answer

Once you start stepping into areas like hierarchies, you probably want to know your RDBMS to some extent. If your RDBMS supports recursive queries natively your hierarchies will look very different then if each iteration is a round trip to the db.

This being said there are a few warnings that come to my mind looking at your schema. I believe these are likely to impact both performance and data integrity.

The separate parents table strikes me as somewhat dangerous. What are the rules for parents and children? Can you have blog -> menu_item -> blog -> comment -> menu_item? This first creates the possibility of problems that may be difficult to track down, but also consider that if the rules are based on smaller tables, actually enforcing the rules becomes possible If your menu items are broken off, a caching layer becomes a possibility. Same with other things that are less frequently changed, and the amount of effort to generate the menu on the part of the db will be reduced.
I actually think your second model, if things like menu items, etc. are broken off is likely to be both more scalable and faster. I would still suggest breaking apart comments and blog posts, and tracking the relationships there more closely. If you are worried about scalability and your site has 10000 blog posts and 1000000 comments, being able to filter the comments also by the attached blog post is likely to mean you can get to use better indexes at least on some db's.

I am reminded of the phrase "premature optimization is the root of all evil." Design a good db schema first and then worry about speed and scalability.

Related Solutions

SQLite3 simple database design question

After reading a lot around and receiving a hint about SQL data normalization I came with this schema. The rest will be handled by simple requests:

Table MRindex - All details about a entry will be listed here: key db | user_made_id | description | date | blob_picture | rss_feed | twitter_alias/keyword/hashtag |
Table MRtwitter - All twitter related data will be stored here: key db| index-at-table1| tw_option|tw_user | tw_date | tw_location | tw_name |tw_text
Table MRrss - All rss data will be stored here: key db | index-at-table1| rss_post_author | rss_post_date | rss_post_title | rss_post_text

Any comments & hints would be more than welcomed :-)

EDIT: To clarify a bit...

MRIndex (also referred as table1) holds basic data, filled by the user: Might be user tweets, hashtags or location based tweets. Once this entry is filled up

MRtwitter will hold the tweets and related info. The field index-at-table1 is the "link" to table MRindex.

MRrss it's the the same for blog posts. Same thing applies for 'index-at-table1'.

For MRindex:

description: short user description date: date of creation blob_picture: a picture if any (jpg, png, etc) rss_feed: An RSS_feed URL belonging to this user twitter_alias/keyword/hashtag: either @user, Greece (for location) or #hashtag

Mysql – Index optimization

I think that the best for your tag and posttag tables is:

ok for the tagid as a primary key
add an index on your tag field (this cover your second query)
add two index on posttag table like INDEX(postid,tagid) and INDEX(tagid,postid) (this cover your first and second query)

So:

CREATE TABLE `posts` (
  `postid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `post` varchar(255) NOT NULL,
  PRIMARY KEY (`postid`)
);

CREATE TABLE `posttags` (
  `postid` mediumint(9) NOT NULL,
  `tagid` mediumint(9) NOT NULL,
  PRIMARY KEY (`postid`,`tagid`),
  UNIQUE KEY `tagid_postid_UQ` (`tagid`,`postid`),
  FOREIGN KEY (postid)
    REFERENCES posts (postid),
  FOREIGN KEY (tagid)
    REFERENCES tags (tagid)
);

CREATE TABLE `tags` (
  `tagid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `tag` char(30) NOT NULL,
  PRIMARY KEY (`tagid`),
  UNIQUE KEY `tag_UQ` (`tag`)
);

And for your query:

mysql> explain select p.* from tags as t    inner join posttags as pt on t.tagid = pt.tagid   inner join posts as p on p.postid = pt.postid  where t.tag =  ?;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref    | PRIMARY,tag   | tag     | 32      | const          |    1 | Using where; Using index |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | tagid   | 3       | test.t.tagid   |    1 | Using index              |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       | test.pt.postid |    1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+


explain select p.* from posts as p    inner join posttags as pt on pt.postid = p.postid   inner join tags as t on t.tagid = pt.tagid  where p.postid = ?;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | const  | PRIMARY       | PRIMARY | 4       | const         |    1 |             |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | postid  | 4       | const         |    2 | Using index |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 3       | test.pt.tagid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

Best Answer

Related Solutions

SQLite3 simple database design question

Mysql – Index optimization

Related Question