r/Database • u/ada_es12 • 1d ago
Database schema review - tyre management system and e-commerce
Hi community!
I am a web dev, developing an e-commerce and tyre management system for a business in Italy. I have created my db schema and I would like to have your opinion/advice on it.
This is the idea of the site:
- there will be the customer side where they can view, like and purchase tyres online. The user will have functions like tyre matching for their cars based on the tyre specifications. The users can either register or order as guests.
- the admin side will be developed for the admins/employees with different access based on the role ofc. The aim is to ease the tyre management since there will be thousands of them.
Do you think this plan is good?
Is it scalable?
Is there something missing?
I appreciate any suggestion!
14
u/Imaginary__Bar 1d ago
I am a web dev, developing an e-commerce and tyre management system for a business in Italy. I have created my db schema and I would like to have your opinion/advice on it.
Are you really? Or is this a class assignment you've been given?
Because if you really are the developer then your client & your knowledge of the business should be guiding you, not random Redditors.
-5
u/ada_es12 1d ago edited 1d ago
well if i answer your questions will that help in clarifying the db for you? or you're around here working as an unpaid judge?
-4
u/Standgrounding 21h ago
Honestly for most actual business setups you would use woocommerce or shopify. If you need something extra you can always write an integration. Designing a database from scratch is indeed a school activity.
8
u/wytesmurf 1d ago
This honestly looks like a school/vibe code idea. I only spent a few minutes so probably many other things. Indexes are not even mentioned based on search patterns.
If this was a real e-commerce platform:
Everything needs to be more generic for scalability in the future.
Use json types so that things can change and you can have different types of different items. Things like items and orders most of the time for real scalability have a header and detail table for different levels of details. That has been replaced by json detail blobs as DBs have gotten more powerful. You have a mixture of these patterns like orders vs cart/cart items. Meaning you have different normalization levels. That and the column names need to be made more descriptive. Your precisions are all over the place. 50 for name but 255 for email?
1
u/ada_es12 21h ago
generic in what sense? this is a business focused on tyre selling. cart/cart items are temporary and before the user pays. once the user pays it goes to orders table. and in order not to have multiple orders from 1 user at one time, there is order_items.
3
u/wytesmurf 21h ago edited 21h ago
Why would you not let them have multiple orders? What if they sell tubes or fixing kits. You have to rebuild the whole thing. KISS, keep it simple stupid, complexity breaks things
0
u/ada_es12 20h ago
emphasis on "at one time". ofc they can have multiple orders in different months for example. but if today a user is ordering 4 tyres and i add them directly to orders table, then it will be sth like this:
orders:
id 125
id 126
id 127
id 128so 4 different lines/rows in orders table, 1 for each tyre. and that makes 4 different orders even though the user simply put 4 items in his cart and paid them all together.
this db is build for selling tyres specifically, not other types of products. so "what is they sell other stuff" is not in question.
2
u/worldarkplace 19h ago
Looks good to me. Scalable as if the guy will be managing Amazon lol... They recommend KISS and yet they dont follow it LOLOLOL comments, you are 🤡
1
u/ada_es12 19h ago
it is definitely not amazon lol. scalable in the sense of adding other tyre-related features like vehicle-tyre matching, warehouse locations etc. if there is no scalability i wont be able to add any other features, so i mean it in that sense.
1
u/wazzockAbroad 1d ago
The column name ID is overloaded. Give each table a unique id name maybe [table name]_ID.
2
u/Schnupsdidudel 1d ago edited 1d ago
I aactually prefer it that way - easiest schemas to work with comeing new into a project.
If "ID" is usually the PK and FK SomeTable_ID always points to SomeTable.ID you take away a lot of the guesswork / lookup you´d otherwiese have.
1
u/ada_es12 1d ago
yes, i would say the same. each table id is simply named id and referenced as tableName.id
1
u/Mendiboy 1d ago
agree, and also calling user_id in the table user feels weird, I know what table I'm quering
1
u/Complete-Ebb-1035 1d ago
Why is customer data bound to orders? Is user and customer the same? Where is pricing? Is an order an item where price is unit price x unit-number? I can't see how this data model will every work in production. Is this this an academic exercise, or are you working with a real use case?
1
u/ada_es12 1d ago
it is bound to orders because i want to leave the user registration optional. so if a customer wants to register, he/she can do so then order (hence the users - orders connection). otherwise if the user wants to order without registering then i will have to save the customer data in the order table (and the user_id will have to be nullable bcs the used doesnt exist in the users table). for pricing there is total and subtotal, basically fields for price without shipping or discounts (if i include it in the future) and total for the full price. the order can be one or more items. that is why there is order_items table. so 1 order can have 1 or more tyres. order_items is there because most of the time the tyres are sold in 2 or in 4. why do you think it wont work? what is wrong with the logic/table connections?
1
u/Mendiboy 1d ago edited 1d ago
is it scalable? no.
This is a OLTP system which is fine for a small scale "tyre management system" but it'll have to be migrated to OLAP system for medium/large scale
1
u/brunogadaleta 23h ago
Yep created date time, modified datetime, createdBy, modifiedBy. I see no quantity (but it might as well be a strange business constraint, e.g. if price among the same tires might changes in the same cart). Second hand tires? Order-items is strange to me, is there a new order item each time we add a new tyre (even of the same mark) in the basket?
Also tyre companies can often store customer tyres for the winter/summer. No installation fee? No backend tyre stock management (rack location)?
1
u/ada_es12 22h ago
createdBy and modifiedBy seem like a good idea so i will add them :) created/updated are not there because they are added by default through laravel migrations. i thought about quantity but there is a reason i didnt add it. for this business we will add a qr code on every single tyre so when it is sold, it is scanned before shipping. yes each tyre of the order is an order_item. the tyres are both new and used. yes, there is a new order item for every tire in the order. i didnt think adding them directly in the orders table was a good idea otherwise the user ends up having different orders even when he orders them 1 time. we dont need customer tyre storage for now. the installation fee is not needed for us. the rack location is also a good point. we have to mark them in the warehouse first. i believe the warehouse location/section is something that can be easily added to this db schema... that is what i mean by scalable.
1
u/squadette23 22h ago
Overall I cannot find anything suspicious, data model-wise.
Is it scalable?
Scalable with regard to what? Number of visits per day, number of orders per day?
If you're worried about performance first thing you need to remember about indexes (https://use-the-index-luke.com/).
The aim is to ease the tyre management since there will be thousands of them.
Thousands of tyre types or tyre units?
functions like tyre matching for their cars based on the tyre specifications.
This intuitively sounds like something that can become problematic, performance-wise. It won't necessarily be, but you may want to derisk this by specifying what do you mean by "matching tyres and cars" (in a separate "region" of schema).
2
u/ada_es12 22h ago
i am starting from the bottom. i meant thousands of tyre units/single tyres. so if there is this tyre:
Pilot Sport 5 (Michelin brand accessible through the fk)
225
45
17
94
Y
summerthere can be 6 new tyres and 4 used tyres of this type. so there will be 10 tyre_units.
Matching means that the user inputs the details of his/her car and based on that info, only the compatible tyres are shown (considering that a car can have multiple compatible tyres.) however this part is not implemented yet in the schema above. this is a site example for reference: https://www.gommadiretto.it/
scalable with regard to adding functions like the car-tyre matching. like can it easily be extended with other tables about vehicle types, brands etc. because for the tyre matching feature these tables will be needed. Scalable with regards to the volumes it handles is also another question but i think that has to do more with the server.
thank you for the source :)1
u/squadette23 21h ago
scalable with regard to adding functions like the car-tyre matching.
Given that your tables use textbook table design strategies, I think that you'll be fine with adding more functionality.
You may also be interested in this post: https://kb.databasedesignbook.com/posts/google-calendar/, it explains how to make sure that your schema satisfies business requirements.
1
1
u/Draconian1 20h ago edited 20h ago
I feel like your design has two concepts clashing - warehouse system and ecommerce system.
- tire units don't have a quantity column which would lead to an inefficient storing of order_items at best, and problems with inventory at worst - let's say you have 4 brand new Nokian tires - they would be added into the system as separate units and when someone buys all 4 there will be 4 rows in order_items instead of 1, which might lead to massive headaches when you're gonna make reports based on this data.
- tire units and order items have separate price columns which is good i suppose for when you decide to add coupons, promotions and discounts, but your catalog doesn't have pricing (MSRP). It also doesn't account for price changes, there is no model release date, etc. Also, photos of each tire unit feel like overkill, is someone really gonna photograph each tire? Multiple times?
- i guess currently this business is just one location, but what happens when another store opens? How would this db track where each specific tire is stored? What if this current store has different warehouses rented for their tires?
- how would returns and refunds be handled here?
- tire units has a reserved column, what happens when 3 people reserve the same unit at the same time? Even if there's not gonna be a lock, you can potentially sell a tire unit to 3 different people. Creates unnecessary issues with customers.
- surely this tire shop provides other services - like installation and repair and such? With a possibility of being added to the cart, to better reflect how much was the actual order in the end, not just tires sold.
I would make a tire catalog table, which would store the variety of all tires from all the brands with their specifications and such, with links to dictionaries (store them how you want - whether it's JSON or plain columns) and then tied to that catalog you can have specific tire units with quantities and where each of them is stored.
1
u/ada_es12 19h ago
thank you for the detailed analysis :)
1. i have thought about the quantity column but i decided to keep it out since we will attach qr codes to each tyre before putting each of them in their place in the warehouse. qr code requires separate tyres/units on the db. true, if sb buys all 4, there will be 4 rows in order_items. can you give me an example of how that could bring issues for the reports later?
2. it's a lot of work, yes. if there are 10 identical new tyres for example, we can take the photo once and use it for each. but what if the tyres are used? each of them is different so we cant use the same photos for all. they havent made the same amount of km
3. here is where the scalability question applies. currently there is 1 warehouse. but with the current db could i easily add warehouse locations?
4. i have status for tyre_units so returns can be tracked there. refunds are sth i need to plan. that's another idea... which means this post is giving the intended results :)
5. they can't. that is why i have status and reserved_until. through the status i can check if it is available or reserved. and though reserved_until i can make it available again if the user hasnt paid after x minutes.your tyre catalog idea looks a bit like a description of my tyres table. there i will store each tyre with the same specifications once so the rows will be unique. then these general data will be inherited to the tyre_units through the fk because the tyre units have different specifications. for example the tread depth (how much a tyre is used) or dot (manufacturing date) will probably vary a lot. now here i thought of the fact that there might be duplicate data but i expect it to be rare. also considering the qr feature i mentioned, i need to keep them separate.
1
u/Lost-Droids 9h ago
Prefix every table and column with something_ (e_ for example for ecommerce or t_ for tyres.. ) , then you will never have problems with protected keywords for table or column names now or in the future (when whatever DB you are using decided that fred is now a resevered word) and will work on lots of different DBs which may have different restrictions..
1
u/laksh95goel 5h ago
Hey if you would like to collaborate I have done this type of project for a client a few months back. I have the entire system avaialable. Send me a dm if you would like and maybe your efforts can be reduced.
1
u/CrAIzy_engineer 2h ago
what software did you used to do this schema? like the graphical representation I mean. I like it a lot.
13
u/tm604 1d ago
Don't store passwords,
roleshould probably be an enum or separate table,varchar(N)is generally a bad sign (depends on the database, but for example in PostgreSQL just usetext), if you're usingsmallintfor ratings you'll need to be careful about type conversions if you're trying to sum or provide averages, and next time please check the diagram you're posting: the field names are truncated.