Suppose I've got a blog publish entity.

  • It's many characteristics
  • It's comments mounted on it.
  • It's many states (erased/locked/invisible, etc).
  • It's many "tags". (key phrases, school_id, user_id)

Clearly, comments ought to be its very own table, having a many-to-one relationship to Blog table.

But how about "states" or "tags"? Can you put that in another table? Or can you stick that in lots of posts?

How about characteristics...when they get too large? Because when this site develops, your blog publish may have increasingly more characteristics attached (title, author, blah, blah....). What goes on when the attribute list goes up to 100?

to begin with, states ought to be a tightly structured factor, which means you should create separate posts on their behalf. Consider the thing you need at the start, but it is simple to add a couple of more posts later.

Tags like key phrases should not be saved in posts, since the amount keeps growing quickly with time. That wouldn't make sense at all. So for your, develop a table with id and keyword inside it along with a link table with publish_id and keyword_id. You might omit the keyword_id and directly link publish_id and keyword. Make certain that both posts combined define the main key, so that you can not finish track of a keyword saved several time for you to a particular publish.

For characteristics it may be exactly the same. It's not a poor practice to produce a characteristic table with attribute_id, attribute_title and perhaps more details along with a link table attribute_id and publish_id and content. You may also easily enhance so that it is multilingual by utilizing attribute_ids.

Comments are identical, saved inside a separate table having a connect to a person along with a publish: comment_id, user_id, publish_id, content and perhaps parent_id, which may be a comment_id if you would like comments to become commentable again.

There you have it for any brief overview.

Here is a sample:

Again.. It is simply an example.. You will find other approaches which you can use.

Let's begin:

-- basic-basic blog
CREATE TABLE blog_entry (
    blog_entry_id INT NOT NULL AUTO_INCREMENT,
    blog_entry_title VARCHAR(255) NOT NULL,
    blog_entry_text VARCHAR(4000) NOT NULL,
    create_date DATETIME,
    state_id INT
);

-- create a look-up table for your blog entry's state
CREATE TABLE be_state (
     state_id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (state_id)
);

-- create a look-up table for your blog entry's tag/s
CREATE TABLE be_tag (
     tag_id INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (tag_id)
);

-- a table to store multiple tags to one entry
CREATE TABLE blog_entry_tags (
    blog_entry_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (blog_entry_id, tag_id)
);

-- a table to store definitions of attributes
CREATE TABLE be_attribute (
    attribute_id INT NOT NULL AUTO_INCREMENT,
    name CHAR(30)
);

-- now have a table to which you can assign multiple attributes to one blog
-- of course, this is if I understand you correctly
-- where you want to have additional attributes
-- aside from the basic properties of a blog entry 
-- and will allow you, if you choose to do it
-- to not necessarily have all attributes for each entry
CREATE TABLE blog_entry_attributes (
    blog_entry_id INT NOT NULL,
    attribute_id INT NOT NULL,
    PRIMARY KEY (blog_entry_id, attribute_id) 
    -- PK enforces one blog entry may have only one attribute of its type
    -- meaning, no multiple attributes of 'location' attribute,
    -- for example, for one blog. Unless of course you wrote half the entry
    -- in one location and finished it in the next.. then you should
    -- NOT enforce this primary key
);
  1. blog_entry - your primary table, in which the goods go

  2. be_state - define them here, and place their state_id values in blog_entry.state_id

  3. be_tag - have multiple tags like we all do here

  4. blog_entry_tags - since possibly have numerous tags for just one blog entry, store them here and place blog_entry.blog_entry_id and also the corresponding be_tag.tag_id together. one tag of their type per blog entry. meaning you cannot tag entry#1 (for instance) the tag php two times or even more.

  5. be_attribute - store attribute definitions here like location, author, etc

  6. blog_entry_attributes - much like blog_entry_tags where one can assign a number of than a single be_attribute to some blog entry.

Again, case one approach.