Jump to content

Database Design - Packages, Options, Users


cmgmyr
 Share

Recommended Posts

Hey Everyone,

I'm having a tough time putting everything together in an efficient way, so I figured I would throw it out there to get some opinions. I have some web software where users can signup for a package, and each package has different options available. Users will also have the ability to add on an option that's not in their current package (instead of upgrading to the next package with that option), in order to save them some money. I would also need a simple way to query these options to see what they have available to them to open up or deny options to them.

 

One of my thoughts is to have all of the packages in a table and have each option set to "yes" or "no" depending on if that option is allowed. Obviously this isn't that efficient as far as adding new features in the future.

 

The other thought would be to create multiple tables:

packages - package_id, name, price

options - option_id, name, price

packages_option_map - map_id, package_id, option_id

users_map - user_map_id, user_id, map_id

 

Is this the best way to handle this? Am I missing something? Any other advise/insight would also be helpful.

 

Thanks in advance,

-Chris

Link to comment
Share on other sites

Assumptions:

 

1. The total price of a package is the sum of all it's options.

2. The price of each option may change at any given time and therefor adjusting the package price.

3. The total price a user pays is the sum of all it's chosen options.

 

users (user_id, user_name, user_password)
packages (package_id, package_name)
packages_options (option_id, option_name, option_price, option_package_id)
users_packages_options (upo_user_id, upo_option_id)

 

When the user selects a package instead of individual options:

 

INSERT INTO users_packages_options (upo_user_id, upo_option_id)
  SELECT $uid, option_id FROM packages_options
  WHERE option_package_id = $pid

 

When the user switches from entire package to individual options (components):

 

DELETE FROM users_packages_options WHERE upo_user_id = $uid AND upo_option_id NOT IN ({implode(',', $chosen_options)})

 

Delete a package:

 

DELETE FROM users_packages_options WHERE upo_user_id = $uid AND upo_option_id IN (SELECT option_id FROM packages_options WHERE option_package_id = $pid)

Link to comment
Share on other sites

Thanks for the feedback. Your assumptions for 2 & 3 were correct, but not #1. The package price will be set as the "base price" then additional options can be added. What about this for the structure:

 

packages (package_id, name, price)

options (option_id, name, price, base_package_id)

 

The total at checkout will be the package price plus whatever all of the options add up to that are not in that base_package_id. Would that work better?

Link to comment
Share on other sites

I would add some additional data to the tables

 

packages (package_id, name, price, sort)

options (option_id, name, price, base_package_id)

 

First I would see which package they selected, on the next page list options that are above that "sort" value and attached to the "base_package_id". So at that point that package sequence is based on the "sort" since I could delete packages or add some, so after a while the ID's wouldn't have any importance (as far as sorting goes).

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.