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;