Which MySQL schema would is optimal for this type of system

Assuming a system similar to Netflix where members create a wish list of movies and, based on their type of plan, one, two, or more of those movies in their list turn into orders, which one of the following schemas makes more sense?

1. A controls table storing the following columns:

   controls(memberid, currentMoviesAtHome, moviesAtHomeLimit, currentMonthlyMovies, monthlyMoviesLimit)

The user does not actually decide when the order is created as that depends on their account contr开发者_StackOverflow社区ols. A daily function will go through the customers and their controls and choose ones where currentMoviesAtHome < moviesAtHomeLimit AND currentMonthlyMovies < monthlyMoviesLimit ...

2. A separate accounts table linked to a plans plans table:

   accounts(memberid, planid, currentMoviesAtHome, currentMonthlyMovies)

   plans(planid, moviesAtHomeLimit, monthlyMoviesLimit)

---

The second option, having the ACCOUNTS and PLANS tables, is normalized so it would be my recommendation.

Additionally, these tables:

• MOVIES
• WISHLIST
  • movie_id (primary key, foreign key to MOVIES.movie_id)
  • account_id (primary key, foreign key to ACCOUNTS.account_id)
  • is_onsite

The is_onsite would be a boolean to determine if the movie has been sent to the client. If it has, value should be set to 1. Use this to sum to know if the account is at or under their plan limit. When videos are returned, only delete the rows that have is_onsite set to 1.

---

A daily function will go through the customers and their controls and choose

This doesn't answer your question but I thought I'd mention that your design is suboptimal. Rather than polling, as you describe above, you're much better off deciding what to do on-demand; that is, there will obviously be a time in your application's use where the limit values will be updated. What you should do is fire some kind of event at that time and consume the event that will decide whether or not to send out another movie.

Polling on a daily basis will not scale.

Firing and handling an event will not only be faster but it will be easier to maintain in the long run. Good luck.