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.

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