Design table issue

Question

I have some applications I sell, and they run by sessions, in simple terms, a user have a key and he can purchase multiple sessions of a specific application to run using his key.

So let's say product A, B and C, user have bought a session for A and B, so he can run a single instance of both, A and B until he stops paying for those session.

So, initially I was thinking of having a table like user_acquired_products with all the sessions(I guess sessions in here would be similar to subscriptions) they currently have.

This table would further be used to identify how many session of the owned products he can run at the same time.

Hypothetically speaking something like:

SELECT * FROM user_acquired_products WHERE dt_expire <= NOW() AND logged = 0

On to the problem, I want to make a package(let's call it Z), this package will give the user 1 key to all of our products, in short he would be able to run products A, B, C and any other paid product to be released.

But with my current format, I don't know how to fit this in, because if we add a new product then I would have to recursively create a new sessions of that product to all clients that own package Z.

By current format, I mean my current idea, which I described above where user_acquired_products would store all the current sessions owned by the users.

I am a bit lost how to even start it, I was going to make some design on workbench but am a bit lost because of the package schema, as in, when a new product is added, we would have to recursively watch for users that own package Z to include that new product to their account.

This is just some samples, I haven't put much thought on it yet(so no index or whatever) as I am a bit stuck on how to go about it:

CREATE TABLE `users` (
  `uid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` varchar(120) NOT NULL DEFAULT '',
  `password` varchar(120) NOT NULL DEFAULT '',
  `salt` varchar(10) NOT NULL DEFAULT ''
  .... a whole lot other fields ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `store_products` (
  `pid` int(10) UNSIGNED NOT NULL,
  `hash` varchar(64) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `description` text,
  `price` decimal(3,2) NOT NULL DEFAULT '0.00',
  `type` enum('FREE','PAID','BETA') NOT NULL DEFAULT 'FREE' COMMENT '0 = free, 1 = paid, 2 = beta',
  `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts_updated` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `store_user_acquired_products` (
  `aid` int(10) UNSIGNED NOT NULL,
  `uid` int(10) UNSIGNED NOT NULL,
  `pid` int(10) UNSIGNED NOT NULL,
  `in_use` tinyint(1) NOT NULL DEFAULT '0',
  `ts_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ts_expire` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

I think that a design where access is associated to packages (groups of products) - and not to products - would be appropriate for your case.

I named this association table licenses to replace your user_acquired_products:

users
    user_id      PK
    user_name 
    -- more columns

products
    product_id   PK
    product_name 
    -- more columns

packages
    package_id   PK
    package_name 
    -- more columns

package_details
    package_id   PK FK1 (REFERENCES packages)
    product_id   PK FK2 (REFERENCES products)
    max_sessions           -- max number of sessions allowed 
                           -- for this product in this package

licenses
    license_id   PK
    user_id      FK1 (REFERENCES users)
    package_id   FK2 (REFERENCES packages)
    -- more columns (sell_date, exprire_date, etc)