I am developing a small application. I could not create the database as an idea.
My data will be as follows, should I create separate tables for them or should I use them in a single table using The Adjacency List Model.
Material (Material type (such as steel, carbon fiber, metal)
Brand
Series
Product (such as length, color, price)
Each brand will be able to produce products in multiple materials.
Each brand will be able to produce more than one series.
Each product will have a single series. Each series belongs to a single product.
The brand can have more than one product.
When I delete a brand, when the brand is deleted, it will delete the series and products belonging to that brand.
When I delete a product, it will delete that product, plus the series to which the product belongs, and if there are no different series and products belonging to the brand, it will also delete the brand.
When I delete the series, it will delete the product belonging to that series and if there is no different data in the brand to which that series belongs, it will also delete the brand.
It will make related additions in the same way in addition operations.
Accordingly, should I design a related database or a single table?
Do I need to use function/trigger? Thank you in advance for your answers, I wish you a good day.
2
Answers
If you have different "things" in your database, like Material", "Brand", "Product", and "Series", you should most likely use a relational database. Likely with different tables for each "thing".
Deletion can often be handled by "On Delete Cascade".
But we cannot provide the exact database design you should use. To design a database you will need at least some knowledge about how databases work, and of basic concepts like indices, foreign keys etc.
If the amount of data you will store is small I would also consider simpler options, like a in memory database like SQLite, or just a plain .json file.
The best way to design this is to have separate (relational)tables (for Material, Brand, Series, Product) related by primary & foreign keys.
For DELETIONS use ON CASCADE DELETE, it will drop related records.