design a database for a shopping-cart application? [closed]

Question

I have never designed a database/data-model/schema from scratch, especially for a web-application. In some recent job interviews, i was asked to 'design' a database for a shopping cart application. Now i am working on a mobile shopping application (retail, uses phonegap) with a backend that needs to store and process product and order info. The scale of this problem is so huge, i don't know where to start. I was hoping for some advise on -

1. How should I approach such a problem (shopping cart application DB) ? where should i start ?
2. Are there any common mistakes/pitfalls that i should avoid ?
3. What optimization/efficiency paradigms should i keep in mind when designing such a DB ?
4. How should i go about identifying entities in the problem space (products, orders, etc)? how should i derive the relationships between them ?
5. When a interviewer asks such a question, what exactly is he looking for ? is there something i should/should not say ?

I should also clarify that -

1. Yes, I am a noob, and my motives are to learn database design AND prepare for upcoming job interviews. I have read DBMS books where they describe individual concepts in detail, but i have no clue how to put those things together and start designing a database.
2. I have seen other threads on database design. The authors already tend to posses some knowledge on how to break the problem down. i would like to understand the methodology behind doing that.
3. Links to outside resources, comments, suggestions and anything that will put me on the right track is much appreciated. I hope this thread serves as a learning experience for myself and others.

Dupree

Thank you for your response and info on normalization. As a newbie, i have to clarify this from my viewpoint - its very difficult for a newbie to comprehend the complexities of db design in a domain as complicated as shopping-cart or banking. this has less to do with understanding the basics of DB design, and more to do with understanding the domain and how to break it up so as to derive a good solution. the answers above have done a good job of providing concise solutions to answer both problems - design fundamentals AND domain knowledge. common sense can only take you so far. – Dupree

Answer 1

There can be five tables in database:

CATEGORY this table stores information about products categories of your store and categories hierarchy.
parent field of this table stores ID of the parent category.

PRODUCT all products of your store are stored in this table. This table has a foreign key categoryID which identifies ID of the category to which a product belongs.

ORDER this table stores information about all orders made by visitors of your store.

ORDERED_SHOPPING_CART table is tightly connected with PRODUCT and ORDER tables; stores information on customers' orders content.

SPECIAL_OFFER table contains a list of products, which are shown on home page as special offers

Katlynkatmai

i am not advocating it but found that it may help you to better visualize your problem and includes/covers maximum requirements of a shopping cart application. even i have not architectured any shopping database yet so this is also a question of interest to me too – Katlynkatmai

this is a good point for me to start working on my design skills. +1 internet to you. – Dupree

that's great i am glad that it helped you. all the best – Katlynkatmai

What's the design of the shopping cart itself? Should I also have Cart and CartDetail tables? How do I store data for unregistered users? – Dingle

@Shimmy shopping_cart and anonymous users are not managed in this example as i said even i searched this on internet to take an idea of it and to help OP please feel free to improve if you have something more to add – Katlynkatmai

I really don't know :( I was hoping you can share your experience – Dingle

Answer 2

A brief answer is the way that i would tackle this problem. Firstly, there are loads of open source or free, web based shopping carts. This means that you can get one, set up the database and then have a good look around what they did.

Ask yourself questions such as, why have they done that? Why is it good? What downside could there be? How would i do it differently? why?

I would try to procure a database design tool that allows you to visualize the database. (like database designer in visual studio or i have one from MicroOlap that does pgsql databases)

Then you need to think about what you need in the database. What is the customer going to do? Buy products! Therefore you need a products table. Without going down the whole route you can see the point. Imagine what is needed, then basically make a table for it.

If you have more than one option for a field in a table, make another table with a relation in it. So if you have a product table and you have a status field. you could have more than one status. (eg out of stock, limited number, big item, expensive) instead of hard coding these fields, make a table and allow the user to add items to the table. then in the product table add a field status_id and link it to the status table

Many - many relationships are useful things to know. (i fell short to this myself.) say you have a component and product tables. The products can be made up of lots of components and the components could be allocated to many products. Create a mediator table. Something like prodcomp( and in this you would have fields like id, prod_id, comp_id, qtyneeded).

Learn to index correctly.

Don't create the database until you have a solid idea of how it will work. this saves time in recreating it later.

There may be more to this, however, i hope i have given you a good start.

Lieutenant