Relationship design for auto parts store database

Question

I'm creating a design database for an autoparts store (this is a project school). For the moment, I'm not sure if the relationship model especially in MODEL, MAKE, BRAND is correct (I really have no experience about this area).

I'm not an expert in this area, so I need some help.

create database AutoParts2

use AutoParts2
go

create table Make
(
MakeID int not null identity(1,1),
Name varchar(50) not null,
constraint PK_Make primary key (MakeID)
)

create table Model
(
ModelID int not null identity(1,1),
MakeID int not null,
Name varchar(50) not null,
constraint PK_Model primary key (ModelID),
constraint FK_Model_Make foreign key (MakeID)
references Make(MakeID)
)

create table [Year]
(
YearID tinyint not null identity(1,1),
Name char(4) not null,
constraint PK_Year primary key (YearID)
)

create table Model2Year
(
Model2YearID int not null identity(1,1),
ModelID int not null,
YearID tinyint not null,
constraint PK_Model2Year primary key (Model2YearID),
constraint FK_Model2Year_Model foreign key (ModelID)
references Model(ModelID),
constraint FK_Model2Year_Year foreign key (YearID)
references [Year](YearID)
)

create table Category
(
CategoryID tinyint not null identity(1,1),
Name varchar(30) not null,
constraint PK_Category primary key (CategoryID)
)

create table Subcategory
(
SubcategoryID tinyint not null identity(1,1),
CategoryID tinyint not null,
Name varchar(30) not null,
constraint PK_Subcategory primary key (SubcategoryID),
constraint FK_Subcategory_Category foreign key (SubcategoryID)
references Category(CategoryID)
)

create table Model2Year2Category
(
Model2Year2CategoryID int not null identity(1,1),
Model2YearID int not null,
CategoryID tinyint not null,
constraint PK_Model2Year2Category primary key (Model2Year2CategoryID),
constraint FK_Model2Year2Category_Model2Year foreign key (Model2YearID)
references Model2Year(Model2YearID),
constraint FK_Model2Year2Category_Category foreign key (CategoryID)
references Category(CategoryID)
)

create table Brand
(
BrandID int not null identity(1,1),
Name varchar(50) not null,
constraint PK_Brand primary key (BrandID)
)

create table Brand2Subcategory
(
Brand2SubcategoryID int not null identity(1,1),
BrandID int not null,
SubcategoryID int not null,
constraint PK_Brand2Subcategory primary key (Brand2SubcategoryID),
constraint FK_Brand2Subcategory_Brand foreign key (BrandID)
references Brand(BrandID),
constraint FK_Brand2Subcategory_Subcategory foreign key (SubcategoryID)
references Subcategory(SubcategoryID)
)

create table Part
(
PartID int not null identity(1,1),
Name varchar(50) not null,
BrandID int not null,
PartNumber varchar(50) not null,
Description varchar(200),
constraint PK_Part primary key (PartID),
constraint FK_Part_Brand foreign key (BrandID)
references Brand(BrandID)
)

create table Model2Year2Part
(
Model2Year2PartID int not null identity(1,1),
Model2YearID int not null,
PartID int not null,
constraint PK_Model2Year2Part primary key (Model2Year2PartID),
constraint FK_Model2Year2Part_Model2Year foreign key (Model2YearID)
references Model2Year(Model2YearID),
constraint FK_Model2Year2Part_Part foreign key (PartID)
references Part(PartID)
)

create table Brand2Subcategory2Part
(
Brand2Subcategory2PartID int not null identity(1,1),
Brand2SubcategoryID int not null,
PartID int not null,
constraint PK_Brand2Subcategory2Part primary key (Brand2Subcategory2PartID),
constraint FK_Brand2Subcategory2Part_Brand2Subcategory foreign key (Brand2SubcategoryID)
references Brand2Subcategory(Brand2SubcategoryID),
constraint FK_Brand2Subcategory2Part_Part foreign key (PartID)
references Part(PartID)
)

Answer 1

You have all the tables you need and you have the many-to-many relationships correct, I think.

I'd suggest that you add a Parent field to Category and then create a hierarchy of categories instead of having a Category table with a single Subcategory table.

create table Category(
    CategoryId int not null identity(1,1),
    ParentCategoryId int null,
    CategoryName nvarchar(100) not null,
    primary key (CategoryId))

As a point of style, I'd name the many-to-many tables with underscores instead of numbers, i.e. Model_Year_Part.

Note that I used nvarchar instead of varchar.

If you ask a question on StackExchange for a school project, don't forget the citation if you use the answers! It's okay to ask but never to plagiarise.

Answer 2

I'd consider the following things:

• Are you sure that you need the Year table? You could store the year in the Model2Year table directly.

• I would omit the Model2Year.Model2YearID attribute. Instead of this I'd use a compound primary key (ModelID, Year) or (ModelID, YearID).

• Is it possible that, for example, a specific model is in categoryX in 1999 and it's in categoryY in 2000? If not, connect the Model and the Category table with a model_to_category helper table ((ModelId, CategoryId) is the primary key) instead of the current Model2Year2Category table.

It's not obvious what you store in these tables. Provide some example for every table and I'll update this answer. For example, does Category store car categories or part categories?