What's the best approach to designing a database that keeps track of orders and wish lists?

Question

The best way to describe this scenario is to use an example. Consider Netflix: do they store their orders (DVD's they mail out) in a separate table from their member lists (NOT members table, but a joiner table of members and movies--a list of movies each member has created), or are orders distinguished by using additional information in the same row of the same table?

For those not familiar with Netflix, imagine a service that lets you create a wish list of movies. This wish list is subsequently sent to you incrementally, say two movies at a time.

I would like to implement a similar idea using a MySQL database, but I am unsure whether to create two tables (one for orders and one for lists) and dynamically move items from the lists table to the orders table (this process should be semi-automatic based on the member returning an item, where before a new one is sent out, a table with some controls will be checked to see if the user is still eligible/has not gone over his monthly limit)...

Thoughts and pros and cons would be fantastic!

EDIT: my current architecture is: member, items, members_items, what I am asking is if to store orders in the same table as members_items or create a separate table.

Answer 1

I feel like they would store their movies as follows (simplified of course):

tables:

• Titles
• Members
• Order
• Order_Has_Titles

This way an order which has a foreign key to the Members would then have a pivot table as many orders could have many titles apart of them.

When you have a many to many realtionship in the database you then need to create a pivot table:

Order_Has_Titles:
    ID (auto-inc)
    Order_FkId (int 11)
    Title_FkId (int 11)

This way you're able to put multiple movies apart of each order.

Of course this is simplified, and you would have many other components which would be apart of it, however at a basic level, you can see it here.

Answer 2

A problem with storing orders in the members table is that there's a variable number (0, 1, or several) of orders per member. The way to do this using a relational database is to have two separate tables.

Answer 3

Moving things from one database table to another to change its status is simply bad practice. In a RDBMS, you relate rows from one table to other rows in other tables using primary and foreign key constraints.

As for your example, I see about four tables just to get started. Comparing this to Netflix, the grand-daddy of movie renting, is a far-cry from reality. Just keep that in mind.

1. A User table to house your members.
2. A Movie table that knows about all of the available movies.
3. A Wishlist or Queue table that has a one-to-many relationship between a User and Movies.
4. An Order or Rental table that maps users to the movies that are currently at home.

Statuses of the movies in the Movie table could be in yet another table where you relate a User to a Movie to a MovieStatus or something, which brings your table count to 6. To really lay this out and design it properly you may end up with even more, but hopefully this sort of gives you an idea of where to begin.

EDIT: Saw your update on exactly what you're looking for. I thought you were designing from scratch. The simple answer to your question is: have two tables. Wishlists (or member_items as you have them) and Orders (member_orders?) are fundamentally different so keeping them separated is my suggestion.