1

Compare Database #1 and Database #2 below

DATABASE #1

POSTS---------------------------------
|___Id________Category_Id______Body__|
|   1         "For Sale"      "...." |
|   2         "Requests"      "...." |
|   3         "Misc."         "...." |
|   4         "For Sale"      "...." |
|   5         "Misc."         "...." |
--------------------------------------

DATABASE #2

POSTS---------------------------------
|___Id________Category_Id______Body__|
|   1             1           "...." |
|   2             2           "...." |
|   3             3           "...." |
|   4             1           "...." |
|   5             3           "...." |
--------------------------------------


CATEGORIES----------------
|___Id__________Title____|
|   1        "For Sale"  |
|   2        "Requests"  |
|   3        "Misc."     |
--------------------------

Are there any situations where Database #1 is preferred over Database #2?

It think that Database #2 is the better choice, especially in terms of scalability and maintenance, but should you ever opt for the simpler design of Database #1?


Before you continue reading -

▼ Don't miss this part! ▼

The rest of the post is not at all necessary to answer my question, and unless you just wanted to read my opinion, you should probably skip to the end.

I've only included the rest of this post so that if anyone wanted to, they could see a situation where I thought Database #1 might be better than Database #2.

If anyone wants to take the time, I would love to get feedback on why Database #1 is not even right for the following situation and how/where my thought process goes wrong.

▲ Don't miss this part! ▲


When/why I think that Database #1 might be acceptable and/or preferred over Database #2 -

  • If you use Database #1, you can search for and sort Posts by Category just as easily as you can with Database #2.

  • The only task I see being more difficult with Database #1 is fetching each unique Category title.

But, if you/the designer knows there are only a few ( <5 ) Categories, and they aren't expected to change, then you could hard-code each Category's title in the View.

  • The Model can provide easy access for each Category of posts, while staying uncluttered and performing only the duties of a Model.

Implementation details:
You could have a View page that simply has three links (since this database only has 3 Categories):

        ----------------------------------------------------------------
        |                          View Posts!                         |
        |                                                              |
        |         __________       __________       _______            |
        |         |For Sale|       |Requests|       |Misc.|            |
        |         ‾‾‾‾‾‾‾‾‾‾       ‾‾‾‾‾‾‾‾‾‾       ‾‾‾‾‾‾‾            |
        ----------------------------------------------------------------

Each link could go to a different page which would each be served by a different controller method, or each link could go to the same page and send a parameter specifying the desired category in the HTTP request.

In the controller, depending on which link was clicked by the user, a different call is made to the Model requesting Posts with whatever Category was requested by the user.

The Model could implement these calls in one of two very clean ways:

# One way
# post.rb

def by_category(category)
    where("category = ?", category)
end


# OR
# post.rb

def for_sale
    where("category = ?", "For Sale")
end

def requests
    where("category = ?", "Request")
end

def misc
    where("category = ?", "Misc.")
end

For those reasons I think Database #1 might work better in this situation.


In conclusion

Are there any situations where Database #1 is preferred over Database #2?

It is most important that you answer this question as generally as possible.

Touching on my opinion or the specific situation I described is only a plus.
Thanks!

1

DATABASE #1

There's not a master table. It means that there's no repository of categories. Category is a mere description field.

Doesn't matter if we get repeated values (it seems). May be because these values are informed by the client or the customer and it's out of our control. We only want to store it.

We have not been asked for its maintenance. So there's no need of CRUD. Looks like any word is a valid Category. It seems a tag.

DATABASE #2

Category matters. It's part of the data model and it has its own purpose, besides to catalogize POST.

CATEGORIES is master table. Probably is going to be maintenance of these categories. A sort of CRUD is required to manage the table.

This is important because now we have the control over Category and we decide what a category is and how is named.

As we pointed, Categroy is now a entity so, it's going to have It's own class. The rest of the data model no longer does reference to categories with tags. It's required composition.

We need to review our SQL scripts and mappings. Now categroy is retrieved by joins and there's referencing integrity to validate.

Are there any situations where Database #1 is preferred over Database #2?

Yes. The situation is requirements depending. If there's no need of Category's maintenance, then Database #1.

Otherwise, Database #2 looks more flexible for further developments. It also allows to reuse info because it has been catalogized as a master table. So no more tags scatered all around the data model and no more spagetti-code comparing strings.

DATABASE #2 scales better than DATABASE #1.

Looking at the example. How do you expect to maintain the view with only 3 links if in 1 month it becomes a view with 200 different categories?

With DATABASE #1

SELECT DISTINCT CATEGORY FROM POST;

You should assure that DISTINCT is CASE SENSITIVE and there's no repeated values by typo. Also to take in account puntuation and orthographic marks.

Over time, POST will end up containing many more entries than CATEGORIES would. Or do you expect 1 category per Post?

This design may end up penalizing the performance at printing the view.

With DATABASE #2

SELECT TITLE FROM CATEGORIES;

This table will have less entries than POST. The performance at printing the view will be a great deal better.

5

I strongly advice against DATABASE #1, for the following reasons :

  • There is no integrity constraint. Invalid values may be created.
  • Changing a description, will require changing all rows with that description. And also all programs that use that value (i.e. you may have an if statement)
  • You can not support multilingua applications

I do not see any advantage of DATABASE #1, other than it may take a few minutes less to create. Your queries can be simple in #2 as well if you create a view that joins the two tables.

  • I assumed that Database #2 would have a foreign key constraint. – kevin cline Apr 12 '16 at 8:05
  • @kevincline fixed the mistake. Typing error, I replaced #1 with #2 – DesignerAnalyst Apr 12 '16 at 10:58
2

It really depends on what the application calls for. If the application requires a list of categories that might be referenced one or more times (a one to many relationship) then I'd use DB #2. Otherwise, #1 should suffice - and would keep the queries simpler.

Its about normalizing the data. You shouldn't just assume that you need to 'normalize' data based on the text being stored. What matters is the context of that bit of data. Are the words "For Sale" really a separate thing on its own, or is it really an attribute of the Posts table?

1

In a data warehouse setting Database #1 could be the data warehouse of Database #2 (where tables are "flattened out" for performance)

1

But, if you/the designer knows there are only a few ( <5 ) Categories, and they aren't expected to change, then you could hard-code each Category's title in the View.

But if you use the second approach you could also just hardcode the category's fk into the view. Also an important detail in your description is the word "expecting", you aren't "expecting" them to change but if they nonetheless change you have to change the view. With the database#2 approach you don't have to change anything in the view

def for_sale where("category = ?", "For Sale") end

def requests where("category = ?", "Request") end

def misc where("category = ?", "Misc.") end

here you could also write, which does not care if the data model changes:

def for_sale where("category = ?", "1") end

def requests where("category = ?", "2") end

def misc where("category = ?", "3") end

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.