Relationship design for auto parts store database

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
)

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 ta

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.

StackExchange Code Review Q#5770, answer score: 4