What is better in performance for 1-to-1 relationship: a new table or a VARCHAR column with a lot of empty entries

Question

I have to make the decision whether to add a new column (VARCHAR) to an existing table or to create a new table.

If a added a new column there would be empty entries, so I wonder how detrimental this is when the datatype is VARCHAR.

So concretely I have a "messages" table in which I store messages from userA to userB. Every user should have the chance to respond to a given message exactly 1 time so I could add a "response" column with VARCHAR to the existing messages table or I could create a new table "responses" with "index_message" and "response" as columns.

What is better?

EDIT:

CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender` int(10) unsigned NOT NULL,
  `receiver` int(10) unsigned NOT NULL,
  `message` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ref` varchar(180) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `type` tinyint(4) NOT NULL,
  `multiple` tinyint(4) NOT NULL DEFAULT '0',
  `timestamp` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `responses` (
  `msg_id` int(10) unsigned NOT NULL,
  `response` varchar(140) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Is that another great example of Stack Overflow's friendly and welcoming tone that it's so renowned for, @e4c5? (but, yes, OP, you should definitely share your table structure.) — Pekka
In this specific case I would go for the column: it will be faster when you retrieve the data, since you won't have to make a join. It is possible that you save a negligible amount of disk space using a separate table, but that is not an issue in my opinion. — xzoert
@Pekka웃 I added the missing information, thank you for not being rude. — Julius S.
Yeah, performance is unlikely to be an issue until you have millions or billions of records. A normalized approach might make future changes easier - but would also add a lot of complication. I'd go with the column, too — Pekka

rhavendc rhavendc · Accepted Answer · 2017-01-26T09:45:10

It all depends on your need or process flow.

If you're allowing users to respond only once to a message, then go for 1 table only.

CREATE TABLE `messages` (
  columns go here.....
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

If you're allowing users to respond more than once to a message, because follow-ups may occur, then go for that multiple table relationship.

CREATE TABLE `messages` (
  columns go here...
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `responses` (
  msg_id, response, sender....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

But you can also allow your users to respond more than once to a message with just 1 table. You'll just put the msg_id column inside your messages table, so every message and responses are saved in the same table. With that setup, users can reply to messages and responses (just like how emails work, but I'm not saying that they use the same table structures).

CREATE TABLE `messages` (
  id, title, to, from, message/response, date, msg_id....
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What is better in performance for 1-to-1 relationship: a new table or a VARCHAR column with a lot of empty entries

2 Answers