Solved

Architectural of database with millions of searchable records

Posted on 2011-03-22
4
232 Views
Last Modified: 2012-05-11
I am building a comparison site with around 500,000 product with maybe 30 data fields for each product.

I have little experience with something this scale (over a few thousand records), does anyone have any suggestions on how to ensure this is done efficient and how product pictures and files need to be organized for it being most efficient. I can code the logic but worried about efficiency and retrieving records quickly.

Would it be better to break the data into a bunch of tables? Does having a smaller table make it faster to search?

Does an auto suggest code work off directly from the db or some cached file produced elsewhere?

 
0
Comment
Question by:blink10
4 Comments
 
LVL 4

Expert Comment

by:qasim_md
ID: 35187629
Yes it is better to breakdown your table into 2 tables and maintain a Referential integrity to your tables.
0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 500 total points
ID: 35188324
"Would it be better to break the data into a bunch of tables? Does having a smaller table make it faster to search?"

Yes. There is a process for this called NORMALISATION and if you follow its 3 main rules you will not go far wrong. There are examples of how to do this at Wikipedia http://en.wikipedia.org/wiki/Database_normalization#Example and you can also post database table definitions here at EE. The articles at Wikipedia can get a bit technical but read them and try and get the gist of what they are saying. In particular read these 3 and look at the examples.

http://en.wikipedia.org/wiki/First_normal_form
http://en.wikipedia.org/wiki/Second_normal_form
http://en.wikipedia.org/wiki/Third_normal_form

For 99% of database work, you can ignore all the other normal forms.


"Does an auto suggest code work off directly from the db or some cached file produced elsewhere?"

Auto suggest uses database data to work, but it usually requires some level of programming in PHP/MySQL and javascript.
0
 
LVL 5

Expert Comment

by:tsmgeek
ID: 35194790
or use latest mysql table partitioning if its too much hassle to split the table up
depends on the application really
dont go overboard as then its counter productive, say you have two pages onyour site, a summary and then a detailed page, think that thats how you could split your data
0
 
LVL 5

Expert Comment

by:innotionent
ID: 35198101
I always find that you need to look closely at your data structure, determine what data is related to each other and then group that data together in tables.

Then create a mapping table to tie all that data together.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now