Geek asks and answers

How to normalize this auto parts database?

I am new to PHP / Databases ... But I pick it up pretty quickly. I would like to ask you guys quite simply. I want to normalize my database and am not sure how to do it. I understand the concept, but I see several ways to do this. Drawing. I would ask people with some experience.

Here is my database (so far 2 tables): Brands Products

***Brands Breakdown:***
1   id          int(6)
**Note:** Above, I will probably use 4-Letter codes for each brand instead of primary/int/auto.
2   name            text
3   logo            varchar(20)
4   phone           varchar(20)
5   website     varchar(30)
6   contact_name    text
7   contact_number  varchar(20)
8   contact_email   varchar(30)
9   warehouse       varchar(20)
10  pricing         varchar(15)
11  bio             varchar(300)

***Products Breakdown***
id (INT(6) / Auto_Increment)
brand (This is where I'll insert the four letter code for brand)
category (e.g. Brakes)
subCategory (e.g. Brake Rotors)
details (e.g. Drilled and Slotteed 'Razr')
sku (Part #)
minYear
maxyear
make (e.g. Subaru)
model (e.g. Impreza)
subModel (e.g. WRX STi)
description (Paragraph on part describing it)

specs (I imagine this can be expanded on. need cells somewhere for sizes / colors / engine codes / etc.)

msrp
jobber
price
cost
weight (of part)
warehouse (Could be moved to brand table)
image (URL of image for the part)

So, my main question is: do I have each brand has its own table, similar to my current product table? or is there a category table? "subcategories"? How do you guys normalize this data?

I would like to have a solid database while I study this stuff, so I will find out the right way. Any advice would be appreciated.

UPDATE: , , , , , , - - cardinality". , !

+3
4

. , . , .

, -. , -, . , -.

.

. "" , .

:

mfrid (your four letter code, primary key)
mfrname            text
mrflogo            varchar(20)
mfrwebsite     varchar(30)
mfrphone           varchar(20)
warehouse       varchar(20)

:

mfrid (four letter code)  (part of primary key)
contactid (autoincrement) (part of primary key)
contact_name    text
contact_number  varchar(20)
contact_email   varchar(30)
bio             varchar(300)

"" ? ""? ?

. . ( , ). :

SKU:

sku (your stock-keeping unit number, primary key).
mfrid (maker of the PART, not the vehicle in which it fits, foreign key to mfr table).
mfrsku (the brand stock keeping unit, not necessarily unique in your system)
category (e.g. Brakes)
subCategory (e.g. Brake Rotors)
details (e.g. Drilled and Slotteed 'Razr')
description (Paragraph on part describing it)
saleprice (?)
cost (?)

:

ApplicationID (auto incrementing primary key)
make (e.g. Subaru)
model (e.g. Impreza)
subModel (e.g. WRX STi)
firstYear.
lastYear.

( SKU , .. SKU Application " " ). , Subarus . .

ApplicationSKU:

ApplicationID
SKU

. , .

  • , Delco Subaru
  • , .
  • , .
  • 1999-2006 Subaru Forester 1998-2007 Subaru Impreza

, . , ( , , ).

, "--" .

.

, .

+1

products.brand products.brand_id brands.id.

categories id, name parent_id ( NULL), categories.id (NULL ). , . products products.category_id ( subCategory).

+2

, , , . - , , .

+1

One product can fit on more than one car - for example, you may have a wiper that is suitable for the 2010 Toyota Camry, 2009 Scion tC and 2011 Acura TL. Thus, you will need to separate the year / model / model from the product table and create a separate table for vehicles (id, year, make, model) and a crosstab (id, product_id, vehicle_id) that combines them.

0
source

More articles:


All Articles