CODEWITHSUNDEEP
database-designrelational-database
stefmikhail
stefmikhail

Reputation: 7125

Image Database - One Table or Two?

I'm putting together a database to manage image files located on a file server. As I'm new to the database world, I'm torn between using one table, or two. Here's the breakdown:

I'm needing columns for: imgId, imgTitle, imgClass, imgFamily, and imgURL.

I'm wondering if it would be better to put the imgURL in another table with imgID linking the two, or if they can all be together in one table, and function fine.

What do you think?

Upvotes: 0

Views: 130

Answers (3)

Kenny
Kenny

Reputation: 1090

All of the items that you have stated appear to have a one to one relationship; in other words, you're probably not going to have two different URLs for one title or image id.

However, the other thing to consider is whether any of the fields are likely to be empty fairly often; would a lot of them have no class, for example, or family? If that was the case, you could consider putting that data in a separate table and linking to it using the id:

CREATE TABLE `image` (
  `imgId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `imgTitle` varchar(255) DEFAULT NULL,
  `imgURL` varchar(255) DEFAULT NULL,
  `imgDate` datetime DEFAULT NULL,
  PRIMARY KEY (`imgId`),
  KEY `date` (`imgDate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `imageAddendum` (
  `imgId` int(11) NOT NULL DEFAULT '0',
  `imgClass` varchar(255) DEFAULT NULL,
  `imgFamily` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`imgId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

You would then get your data using a LEFT JOIN, which would therefore get you the data in image even if there were no row in imageAddendum:

SELECT i.imgId, i.imgTitle, i.imgURL, i.imgDate, ia.imgClass, ia.imgFamily
 FROM image i LEFT JOIN imageAddendum ia using ( imgId );

Upvotes: 1

dkretz
dkretz

Reputation: 37655

The primary reason you might want to split the tables would be if there is the possibility that there might not be a one-to-one match in some cases between imgId and imgURL - like two or more Ids for the same URL; or the same ID referring to more than one URL. (An empty URL would be fine, however. Otherwise, you're fine with one table.

Upvotes: 1

marc_s
marc_s

Reputation: 754478

If you're storing just the Image URL - a string of fairly finite length (a few hundred characters - at most) - I don't see any big benefit in breaking that column into a separate table.

If you would be storing the image per se (the bytes making up the image) - then yes, in many systems, it would make sense to store this blob (binary large object) into a separate table.

Upvotes: 1

Related Questions