3

I am building a site that is a database of spare parts for cars. The information in the database is essentially:

1) Manufacturer
2) Model
3) Parts Main Category
4) Parts Sub Category
5) Image Diagram
6) Label of Part in Diagram
7) Part Number
8) Part Name

I was going to build it in a multi-table structure, something along these lines:

MARQUES
=======
marque_id, marque_name

MODELS
======
model_id, marque_id, model_name

CATEGORIES
==========
cat_id, model_id, cat_name

SUBCATEGORIES
=============
subcat_id, subcat_name, subcat_diagram, cat_id, subcat_parts

PARTS
=======
part_id, part_name, part_description

The reason for doing it this way would be because there are lots of overlap between fields (e.g. there are only a handful of manufacturers, and many parts share the same image diagram etc), so it seems to me it would be more efficient this way (te remove redundant data).

However, I am now wondering whether indexing everything properly and the development overhead required to acheive a multi-table structure ahead is worth it. Running searches will also be a lot easier if it is all in one table.

Could I just stick it all into one massive table? And if the advice is to build it into multi-tables, is there software available that will help me "split it up" and do the necesary indexing?

The site will feature a multi-page browsing interface. You select your marque, then model, then category, then subcategory, then choose your part.

There will be about 700,000 records at first, and probably will grow over time.

Thanks for any advice.

Satyam Koyani
  • 4,236
  • 2
  • 22
  • 48
R C
  • 441
  • 4
  • 10
  • 2
    *"Running searches will also be a lot easier if it is all in one table."*: Easier to write, maybe. Not faster. *"Could I just stick it all into one massive table?"* **No, No and no!** – ypercubeᵀᴹ Nov 26 '11 at 15:10
  • possible duplicate of [Database Structure Advice Needed](http://stackoverflow.com/questions/1622528/database-structure-advice-needed) – ypercubeᵀᴹ Sep 08 '13 at 09:55

2 Answers2

0

This page here as a very detailed answer. Basically your problem is the same as anyone designing a product catalog with a search feature - One item may fall under many categories.

I recommend checking this out first, and if it doesn't answer your question then let us know how it is different from what you want to achieve and we will do what we can.

Database Structure Advice Needed

Community
  • 1
  • 1
maestro416
  • 904
  • 2
  • 17
  • 31
0

For searching my suggestion is to use the Sphinx Search or other similar tool.

Rifat
  • 7,628
  • 4
  • 32
  • 46