Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Architectural of database with millions of searchable records

Posted on 2011-03-22
4
Medium Priority
?
243 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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

971 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