Database Design - Packages, Options, Users

[cmgmyr]

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

[ignace]

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)

[cmgmyr]

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?

[ignace]

Yeah, that may work. I have one question though:

 

Is it possible for a user to not select a package but instead select options from different packages to make a whole? Is this something you/your client would want in the future?

[cmgmyr]

No, all users would need to pick an initial package. Then they will be able to add-on any options that are in the higher level packages.

[ignace]

How would you know if the selected option is from a "higher level"? I don't see any modeling for it, and you can't trust the ID.

[cmgmyr]

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).