Data Base Design solution advice

Question

I am pretty new in data base design and I would like have your opinion :-). I need design a simple Data Base for a CMS, CMS will manage Articles and Blog Posts for my website. My Questions:

A) Since fields in DB for Articles and Blog Posts are the same (ex: Title, Main content) expect "Type" (Posts or Full-Articles), I thought would be a good design solution to have a single Table "Pages" and associate a Look Up table for "Type".

• It is a good design solutions?
• Where to place a Clustered Index to improve performance?

B) Articles and Blog Posts could have property like "Top Article" or "Editor Choice". I thought to manage this property adding a Field to table "Pages" like "IsTop Article" and "IsEditorChoice".

• It is a good design solutions?
• Could make sense use a separate Table and associate a FK for these fields?

Answer 1

If you have to change either type (Posts or Articles), you will end up with lots of null fields.

In general, if an entity stands on its own, it deserves its own database table.

As for a clustered index - this would normally be put on the id field. Start looking at adding non clustered indexes on fields that are used in the select and where clauses, but don't go overboard as having too many indexes can hurt insert performance.

As for the "Top Article" and "Editor Choice" fields - it depends on how you mean to use them and how they relate to the posts and articles.

Answer 2

When designing a database, determine what are your data elements (Articles, Posts) and what are attributes of the data elements (type, top article, editor choice).

Attributes that will always be present, like "type" can have their own column in the data elements table since they will always be populated. If data elements can have more than one "type", then you need to separate the "type" out into a separate table (one to many relation).

Attributes like top article and editor choice are not associated with every data element, so they should be in a separate table. Think of them more as categories. By having them in a separate table, instead of a column, you can easily add "categories" without modifying the table structure.

I would suggest a basic structure like this:

content
content_categories (relation between content and categories)
categories
content_type (relation between content and type)
types