-1

I have a message table that contains the message itself. I am not sure whether I should separate these two, and what the benefits are, if there are any.

CREATE TABLE message (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    from_id INT UNSIGNED NOT NULL,
    to_id INT UNSIGNED NOT NULL,
    thread_id INT UNSIGNED NOT NULL,
    message TEXT NOT NULL,
    is_viewed BOOL NOT NULL
);

Should I normalize it further like this?

CREATE TABLE user_sent_message (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message_id INT UNSIGNED NOT NULL,
    from_id INT UNSIGNED NOT NULL,
    to_id INT UNSIGNED NOT NULL,
    thread_id INT UNSIGNED NOT NULL,
    is_viewed BOOL NOT NULL
);

CREATE TABLE message (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL
);

Does that not make the queries more complex? And what are the benefits of doing this if there are any benefits? I understand the table size of the first alternative will be much less and distributed among the two tables in second alternative. Will this affect performance when doing queries or memory usage?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Alex
  • 5,671
  • 9
  • 41
  • 81

2 Answers2

0

You are NOT normalizing in any way, when you have a 1 to 1 relationship. The only place this would make sense is if TWO or more different tables needed access to the message, and even then, they could simply use the primary ID of the table.

Stick with 1 table as it's easier, faster and better!

Jacques Amar
  • 1,803
  • 1
  • 10
  • 12
0

In the first case, you have just 1 table having 6 columns while in the second case, you have 1 table 6 columns and an additional table. I don't see anything you stand to gain from splitting the table as such. I see this MySQL: multiple tables or one table with many columns? It's probably better to have it one table since it reduces the number of overhead the database will need like in this case, you already have 2 sequences each for a table.

Nditah
  • 1,429
  • 19
  • 23