Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am creating a image search engine that would probably have millions of images tagged with keywords. Basically it would be something like Google Image search.

So I want to ask for any tips on designing the database of the application.

What kind of database system to use? How would the images table look like to store that huge quantity? How do I create the relation between the keywords and the image to be able to do a very quick search?

The programming will be done using a PHP Framework.

Thank you, Alex.

share|improve this question
    
Do you plan to store the images in the database or only the links (to external storage where the images will reside)? –  ypercube May 12 '12 at 19:57
add comment

2 Answers

you might consider to use an indexing framework like Apache Lucene as well. If not, I would recommend to model an inverted index like database. An inverted index uses keywords as key identifier for a set of images.

Inverted Index Table:

keyword image_ids
"sun" : [1,5,6]

Image Table

image_id  size    ...
   1     200x300  ...
share|improve this answer
add comment

You've got a lot of questions in here, so I broke 'em down:

How would the images table look like to store that huge quantity?

Millions of records really isn't that big for a database. The common definition for very large database (VLDB) is a terabyte of data or a billion rows. I'm not saying you can get away with any old database design, but as long as you follow basic rules for normalization, you should be fine. We can't tell you what fields to put on what tables without a lot more Q&A, but pick up a book on database design for your chosen platform. I use Microsoft SQL Server, and for that, I'd highly recommend Louis Davidson's Pro 2008 Relational Database Design. The title says SQL 2008, but it's really relevant for any version of SQL Server (and many other platforms as well.)

What kind of database system to use?

Use the one you're most familiar with. You're better off making smart decisions on a database platform you know well than making first-time-user decisions on a strange platform. All RDBMS's can scale to millions of records these days. If you're going to scale to the billions of records, you'll want to hire a database administrator and architect who's done this kind of thing before.

How do I create the relation between the keywords and the image to be able to do a very quick search?

It depends on whether you need the search to be real-time. Typically in image search database environments, the images aren't changing THAT fast. You could run the search off 5-15 minute old data (and perhaps much older) without affecting the application experience. In that case, you're better off doing the image search outside of the database in a full text platform like Lucene.

If you do need it to be real-time, then you have to be careful with indexing strategies. You want the right table indexes to support your queries, but none extra, because they'll slow down your application.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.