I am working on an app that requires some basic data associations. I've picked SQLite as my database choice for simplicity and ability to use it in mobile version of my app in the future. It comes with certain limitations so it is possible that I am doing this completely wrong.
A simple scenario is as following:
I need to have cars assigned with categories such that a car can be part of multiple categories. Categories are pre-defined.
My approach:
- Three tables:
'Cars','Categories','CategoriesAssigned' - Each
'Car'can belong to multiple'Category'. 'CategoriesAssigned'is used for mapping'Categories'to the'Cars'
On SQLite end I create three tables:
CREATE TABLE Cars(
Id INT PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
);
CREATE TABLE Categories(
Id INT PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
);
CREATE TABLE CategoriesAssigned(
Id INT PRIMARY KEY NOT NULL,
CategoryId INTEGER NOT NULL,
CarId INTEGER NOT NULL,
FOREIGN CategoryId(Id) REFERENCES Categories(Id),
FOREIGN CarId(Id) REFERENCES Cars(Id),
);
As such, I can have many 'Category' for each 'Car'. What I DO NOT like is that there will be a lot of duplicate data, like 'Category' will repeat for many cars.
I am still very new to databases and wanted to get some advice and feedback on how to properly handle scenarios like this.
Update:
There is a another way, which I personally hate:
CREATE TABLE Cars(
Id INT PRIMARY KEY NOT NULL,
Categories TEXT,
Name TEXT NOT NULL,
);
CREATE TABLE Categories(
Id INT PRIMARY KEY NOT NULL,
Name TEXT NOT NULL,
);
And then add 'Categories' as coma separated: i.e. "Trucks,Luxury,Diesel" and lastly parse the string. But that somehow feels even more wrong.
