Solved

Architectural of database with millions of searchable records

Posted on 2011-03-22
4
236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

734 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