Which MySQL schema would is optimal for this type of system

views:

answers:

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?

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 controls. A daily function will go through the customers and their controls and choose ones where currentMoviesAtHome < moviesAtHomeLimit AND currentMonthlyMovies < monthlyMoviesLimit ...

A separate accounts table linked to a plans plans table:

accounts(memberid, planid, currentMoviesAtHome, currentMonthlyMovies)

plans(planid, moviesAtHomeLimit, monthlyMoviesLimit)

+2 A:

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.

OMG Ponies 2010-09-13 04:06:25

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.

RibaldEddie 2010-09-13 04:26:22

The alternative is to run logic when a movie/item is returned. Polling doesn't sound so bad when it can be done once a day for this situation, because it's unlikely for an item to be returned and mailed to the next customer inside of a day. It's plausible, but as a service provider you want a little wiggle room.

OMG Ponies 2010-09-13 04:30:30

@ Ribald, using that approach means I have to check every single time a user changes his wish list/adds removes an item... doesn't that waste resources if the user has his maximum orders out already? What's the use in checking if we should create an order when the person has maxed out their limit, or already has two orders open?

Mel 2010-09-13 05:06:49

You don't need to re-check when a user adds or removes an item to their wishlist, since modifying that information doesn't change the value of the number of movies they have checked out. OMG Ponies is correct, you only need check when you are going to modify the values in question; i.e., when a movie is returned.

RibaldEddie 2010-09-13 05:32:29

Additionally, you may think that avoiding polling is not worth the effort or that it limits you in some way but I'm skeptical it confers any benefit at all.

RibaldEddie 2010-09-13 05:41:58

Thanks for the input! I agree with that approach, but I am a little confused on the scenario when someone signs up and has nothing in their list... by definition, in order to deduct a number from the controls table, we need to introspect the wishlist table.. in otherwords, how do we tell when someone first joins whether to turn something on their wish list into an order without always introspecting that table...

Mel 2010-09-13 23:27:49

Yes, when they sign up or have few items in their wishlist then you will need logic to handle that. Likely the solution is that more than one action will cause the "choose a movie to queue for mailing" event. If you'll forgive my pontification, if you want to improve your development skills, you should start thinking in terms of asynchronous event-driven programs. It's not just a question of scalability, it's also at the core of maintainable software. It will also improve your skills and give you a solid foundation to become a better programmer.

RibaldEddie 2010-09-14 02:41:20

ansaurus

tags:

views:

answers:

Which MySQL schema would is optimal for this type of system

related questions