CODEWITHSUNDEEP
mysqldatabasedatabase-design
XoR
XoR

Reputation: 132

Enhancing the Database Design

My CMS Application which lets users to post Classifieds, Articles, Events, Directories, Properties etc has its database designed as follows :

1st Approach:

Each Section (i.e 'classifieds','events' etc) has three tables dedicated to store data relevant to it:

Classified:

  1. classified-post
  2. classified-category
  3. classified-post-category

Event:

  1. events_post.
  2. events_category.
  3. events_post-category.

The same applies for Articles, Properties, Directories etc. each Section has three tables dedicated to its posts, categories.
The problem with this approach is:

2nd Approach:

This approach will have single posts table which has section column associated to each posts as foreign key. i.e

post: id, section, title etc ....VALUES ( 1, 'classifieds','abc') (2,'events','asd') While the second approach is little bit cumbersome when doing sql queries, it eases up the process when performing relational table queries. ex: table images, ratings, comments belongs to posts table.
image [ id, post_id (FK) ]
While this approach seems clean and easy, this will end up in having oodles of columns in posts table, that it will have columns related to events, classifieds, directories etc which will lead to performance issues while querying for rows and columns.
The same applies for categories. It could be either one of the two approach, either save section column as second foreign key or have separate tables for each sections ( 1st approach ).

So now my question is, which approach is considered to be better than the other? does any of the two approaches have benefit over the other in performance wise? or what is the best approach to tackle while dealing with these paradigms?

Upvotes: 3

Views: 230

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

I will favor second approach with some considerations.

A standard database design guidance is that the designer should first create a fully normalized dsign then selective denormalization can be performed for performance reasons.

Normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.

Hint: Programmers building their first database are often primarily concerned with performance. There’s no question that performance is important. A bad design can easily result in database operations that take ten to a hundred times as much time as they should.

A very likely example could be seen here

A draft model following the mentioned approach could be:

draft cms model

Upvotes: 2

James A Mohler
James A Mohler

Reputation: 11120

Approach 1 has the problem of too many tables

Approach 2 has too many columns

Consider storing your data on a single table like Approach 2, but dividing storing all the optional foreign key data in XML.

The XML field will only have data that it needs for a particular section. If a new section is added, then you just add that kind of data to the XML

Your table may look like

UserID  int FK
ImageID int FK
ArtifactCategory int FK
PostID int FK
ClassifiedID int FK
...
Other shared
...
Secondary xml

Now you have neither too many columns nor too many tables

Upvotes: 0

Related Questions