Database design for a sell/rent eCommerce
database, e-commerce, mysql
Solution
Think of your problem from the real-life perspective:
- You will be offering “something”, being `products` in your schema;
- You will be working with “somebody” or `customers` in your schema.
These 2 tables are most important, as without them the whole thing has no meaning.
Next, in real life, you have to have papers for each deal you'll make in order to:
- Report to authorities and pay taxes (don't you like to pay taxes?);
- Keep track of products that are “on hands” of your customers.
These are `orders` of 2 types: purchase and rent.
Note, though, that it is unlikelly to rent a set of products with different return dates in one order. Typically, one will rent a set of related items for some special case, like wedding celebration or bathroom repair. In case you need some products for 2 days while others for 2 weeks, it is better to create 2 orders, as different delivery conditions or discounts may apply.
Therefore I think that initial variant #1 matches your goal better with the following updates:
- `order_type` column should be added to the `orders` table;
- `rent_details` should relate to `orders` table;
- `rent_details` should have only `order_id` in place of `rent_detail_id` + `order_items_id`;
- it very necessary to have a separate `price` column in the `order_items` table along with `discount`;
Problem
I am working on my school project to create an eCommerce website that sell and rent product. My main problem is that I can't find the best design for the "Renting" part. For now I am thinking about two designs: The first database design suggestion: The second database design suggestion: As you can see there are the same tables in both designs but with different relations, but I can't tell which one is better. I will be happy if you can tell me which design is better or if there is a better way to design my database altogether.