CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Robert Cav Campbell
Robert Cav Campbell

Reputation: 99

Database schema, 1 table or 2 tables

My application will allow users to have a contact list. This is my current schema:

CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`person_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `contact_request` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`person_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email_address` varchar(50) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_address` (`email_address`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB;

When a user tries to add another user as a contact, a record is created in the contact_request table. If the user receiving the request rejects the request, the contact_request record is deleted. If the user decides to accept the request, the data from the contact_request table is added to the contact table then deleted from the contact_request table.

I realized that I could do this in another way where I drop the contact_request table and add another field to the contact table e.g: status that signifies whether a contact was just requested or if it is an accepted request.

CREATE TABLE IF NOT EXISTS `contact` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `person_id` int(11) NOT NULL,
  `status` tinyint(1) not null,
  `create_time` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`person_id`)
) ENGINE=InnoDB;

The advantage I see is that I would have 1 less table. I currently do not see a problem occurring as a result of this change. Is it worth changing this? Are there any other advantages to either method that I might not be aware of. Which is recommended?

Upvotes: 1

Views: 284

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

One other advantage might be to have this status (either as INT or as CHAR), record requests (Q), accepted contacts (C), rejected requests (J), rejected and re-requested (R), blacklisted (B) and possibly other statuses so you could more easily apply more complicated logics, like "a user cannot request a contact again when it has been rejected twice", etc.

Upvotes: 2

E.Benoît
E.Benoît

Reputation: 806

It is worth changing this for more than one reason; as you stated, it will allow you to have one less table. More importantly however, it will allow you to avoid people from requesting contact with someone they've already added without having to query an extra table.

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

It would be cleaner in a sense to keep them as two tables. You could purge and keep the queue table small while not having to keep filtering out the not-real-contacts. It sounds like you will never really need to view contacts and requests within the same table, so there is no reason to mash them together just for the sake of it.

On the other hand, the only plus that I can see is that you, umm, have one less table in the db? And a very vague one of not being able to accidentally have a contact exist both in the contact table proper and the request table at the same time (timing bug or something else).

Upvotes: 0

Related Questions