CODEWITHSUNDEEP
mysql
Starkers
Starkers

Reputation: 10551

Should databases be simple and repetitive?

Basically, I'm creating a rails app with users and posts. I want to be able to soft delete them. To do this, all I need to do is create a boolean column deleted on the users and then use a conditional to change what information is displayed to a non admin user:

(rails)

def administrated_content
    if !self.deleted && !current_user.is_admin?
        self.content
    else
        "This post has been removed"
    end
end

Now my question is, is it best to keep databases simple and repetitive? Because a few days ago I would have said it would be better to create a third table, a state table and set up a has_one belongs_to relationship between the user and a state, and a post and a state. Why? Because state is an attribute shared by both users and posts.

However, then I realised that this would result in more queries being executed.

so is it best to keep it simple and repeat yourself with attributes?

Upvotes: 0

Views: 28

Answers (2)

Andrei
Andrei

Reputation: 3106

It depends on the use-case you want to optimise on. If it is speed you want to achieve than a little bit of denormalization should be ok (again, depends on the scenario).

What your presented here makes sense in my opinion to be both in the user and in the post table because they will not lead to duplicated data. And also each one makes sense for both users and posts.

Think of the state as userState and postState. This way each makes sense in its own context. Maybe in the future the user gets another state other than deleted (Ex: 'in process of deletion') and that would not be true for posts.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562631

Yes, in general we keep each attribute in the table which it applies to, instead of needlessly adding a state table. It's okay for another table to have a similar state attribute.

That's far better than , which break the fundamental definition of a relation. And as you found, require you to write more complex queries.

Upvotes: 1

Related Questions