CODEWITHSUNDEEP
phpmysqldatabase
Hexodus
Hexodus

Reputation: 12965

Proper solution for storing only few values - column or table?

I've got a huge table 'books' storing book information where I plan to have a row for media type, let's call it 'media_ID'. I know that I'm dealing here with only three possible values: book, ebook and audiobook.

So, making a separate table out of this three values seems to me like wasting especially when I have to include it in every query which feels also unnecessary to me. So what's a clean solution for such cases? Maybe using a PHP array in a config file? What are you using?

books
-------
ID
media_ID
title
...

medias
-------
ID
type

MySQL data:

CREATE `books` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `media_ID` tinyint(4) NOT NULL DEFAULT '1',
  `title` tinytext NOT NULL,
  )

INSERT INTO `books` (`ID`, `media_ID`, `title`) VALUES
(1, 1, 'Some print book title'),
(2, 1, 'Other print book title'),
(3, 2, 'First ebook title'),
(4, 2, 'Second ebook title'),
(5, 3, 'Single audio book title');

CREATE TABLE `medias` (
  `ID` tinyint(4) NOT NULL AUTO_INCREMENT,
  `type` tinytext NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

INSERT INTO `medias` (`ID`, `Medium`) VALUES
(1, 'book'),
(2, 'ebook'),
(3, 'audiobook');

Or just php array:

$medias = array("book", "ebook", "audiobook");

Upvotes: 2

Views: 94

Answers (3)

Hexodus
Hexodus

Reputation: 12965

It's also possible to use lookup tables to handle enumaration. Just found the solution here: How to handle enumerations without enum fields in a database.

CREATE `books` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `media` tinyint(4) NOT NULL DEFAULT '1',
  `title` tinytext NOT NULL,
  FOREIGN KEY (media) REFERENCES medias(Medium)
  )

Upvotes: 0

Harlan Wilton
Harlan Wilton

Reputation: 504

Personally I'd use a enum for this. 

    `mediaType` ENUM('book', 'ebook', 'audiobook') not null

Upvotes: 2

Dimitris Papageorgiou
Dimitris Papageorgiou

Reputation: 447

Using a separate table for just 3 values is NOT out of the ordinary. Furthermore this will give you the option of adding more attributes in the future-if the need arises...something that will be an issue with ENUM column type. In my app...I have such a table that stores the payments packages the user selected...and these are only three. So go for a table if you are not sure about your future needs/requirements.

Upvotes: 4

Related Questions