CODEWITHSUNDEEP
mysqlsqldatabasesecuritydatabase-design
mariannnn
mariannnn

Reputation: 287

Very simple database design question

Let's say I have a messaging service between users, with public and private messages. I'd like the "public" ones to be visible to everybody, and the "private" ones to be REALLY private. Which of both designs would be the best?

a)

Having a single database called "messages" with the columns

Or b)

Having two databases, one called "messages_public" and other called "messages_private" with the same following columns

I know that the second approach is redundant, but is safer in the sense that in the case an error occured, the private messages wouldn't be accidentally displayed for everybody (which would be a disaster), am I right? In the first case, on the other hand, it really could. A simple error in the SQL query could fail to filter the private messages, and it would display every one.

Upvotes: 0

Views: 121

Answers (6)

Antoine Trouve
Antoine Trouve

Reputation: 1273

I think the second one would be hell to use and the applications above it would be more prone to bugs that with the first one. BTW, the safety should be managed at the application level, i.e. the configuration of the server and the user application itself. Good luck !

Upvotes: 0

Marvo
Marvo

Reputation: 18143

If they've gotta be "REALLY private" as opposed to "kinda private" or "private-ish" then use the single table approach, encrypt the private messages, and decrypt them on the way out. That way, if you fluff, then the worst that happens is you display gibberish.

But ultimately, I'd just say Don't make any mistakes.

Upvotes: 1

Mike
Mike

Reputation: 964

You might want to consider one database with one table, but use a database view for your public messages, something like: 

CREATE VIEW public_messages AS SELECT * FROM messages WHERE private = false;

then select your public messages from the public_messages view. This could help prevent clumsy programming errors where the private = false is omitted from the select statements in code.

Upvotes: 3

Nathan
Nathan

Reputation: 12314

Better just to keep everything in one table. In any case Splitting the tables probably doesn't have the advantages you might think it does anyway.

  • If an attacker works out a way to dump the entire public table, then they'll probably be able to get messages_private to dump out as well.

  • messages_private contains not just my own private messages, but everyone's, so if this hypothetical error occurs while I am looking at my own private messages, it would end up dumping everyones anyway.

Upvotes: 1

lbrandao
lbrandao

Reputation: 4373

Both can fail, it will depend on your error! That's why you test first. The second approach can fail as easily as the first one if you get confused by the table names, since the structure is the same.

Upvotes: 0

Justin Ethier
Justin Ethier

Reputation: 134207

I think either design would be fine, although I prefer the first because it eliminates redundancies.

In your case, security is going to come down to your application code, which is going to have to guarantee that private messages are only delivered to the appropriate users. If there is a defect in the application code, either database schema could expose private data to the wrong users.

Upvotes: 3

Related Questions