CODEWITHSUNDEEP
mysqldatabasedatabase-designrdbms
Ibrahim Azhar Armar
Ibrahim Azhar Armar

Reputation: 25745

single or split tables to hold this records in database?

i have been struggling with design decision on how to store this information in tables.

I have table called property which holds the record for real estate properties.

CREATE TABLE `property` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `addDate` datetime NOT NULL,
  `serial` varchar(30) NOT NULL,
  `title` varchar(100) NOT NULL,
  `description` text,
  `user_id` int(11) NOT NULL,
  `area_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE  KEY `serial` (`serial`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`),
  FOREIGN KEY (`area_id`) REFERENCES `area`(`id`),
  FOREIGN KEY (`category_id`) REFERENCES `category`(`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I have to display 5 featured property and 5 hotDeal property offered by the site owner in the companies index page. i am not able to decide wether to hold the record in one single table or two different table.

for example.

CREATE TABLE `property_featured` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `addDate` datetime NOT NULL,
  `description` text DEFAULT NULL,
  `position` tinyint(1) DEFAULT '0',
  `property_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `position` (`position`,`property_id`),
  FOREIGN KEY (`property_id`) REFERENCES `property`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `property_hotdeal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `addDate` datetime NOT NULL,
  `description` text DEFAULT NULL,
  `position` tinyint(1) DEFAULT '0',
  `property_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `position` (`position`,`property_id`),
  FOREIGN KEY (`property_id`) REFERENCES `property`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

which approach should i go with, single or split?

Upvotes: 0

Views: 86

Answers (1)

Travesty3
Travesty3

Reputation: 14469

All the information is exactly the same, so you should use a single table with an additional column, call it whatever you want, and make it a TINYINT (0 or 1) if there are only two possible values, or perhaps VARCHAR if there could be more values in the future.

Upvotes: 2

Related Questions