efficient schema design [poké]

Hello /prog/, I am a Pokétrainer-turned-businessman. I run a Pokébusiness where I license access to Pokémon via PC.

A big part of my business's success is that I license Pokémon individually, but also by package deal. For example, I have a Grass family package, a Fire family package, and so on. In fact, in my system, all my Pokémon are hierarchically organized this way (and in case you're wondering, with dual-type Pokémon, I only go by the first type). So if a customer licenses one Charizard, the system understands that the customer is actually singling out a Pokémon from the Fire family. Pokémon (category) -> Fire (subcategory) -> Charizard (product). It's a multi-key index!

So I need to track my Pokélicenses in my RDBMS, but I'm having trouble designing the schema correctly, as I'm unsure about unnecessary repetition of data. The thing is, my Fire family may increase in the future, as I go on to catch more Pokémon and add them to my company's collection. (I have people out hunting for Magmar at Pokémon Mansion as we speak!) But if I add a Magmar to the Fire family, that doesn't mean that everyone who has purchased the Fire family package deal beforehand suddenly gets a license to choose Magmar. No sir! That means when tracking Pokélicenses, jotting down just the Family is not sufficient when handling package deals (category -> subcategory). I need to write down what was in the Family when they bought it (category -> subcategory -> product)!


+--------------+
| licenses     |
+--------------+
| id           |
| client_id    |
| created      |
+--------------+

+----------------+
| license_items  |
+----------------+
| id             |
| license_id     |
| category_id    |
| subcategory_id |
| product_id     | < NOT NULL!
+----------------+

Like so. So a license might look like this:



id   client_id  created
---  ---------  -------------------
121  1031       2009-11-12 12:07:39

id  license_id  category_id  subcategory_id  product_id 
--  ----------  -----------  --------------  ----------
1   121         1 (Pokémon)  2 (Grass)       1 (Bellsprout)
2   121         1            2               2 (Bulbasaur)
3   121         1            2               3 (Ivysaur)
4   121         1            2               4 (Oddish)

Well here's the problem. As I said, the Family deals are the biggest reason I'm in business. So I imagine that there will be a lot of licenses where the same entries, in the same order, will appear over and over -- until I add to the Family, at which point it'll be mostly the same, except with one more line. The database will grow quickly with a lot of redundant info.

But is that actually a problem? I'm obsessed with cutting down redundancy, but I fear that I'm actually worrying too much. Also, if there is a better way, I'm not sure what it is. I considered another table, but it quickly became an abomination responsible for tracking subtle permutations between Families and various collections of Pokémon. What a nightmare!

So, any ideas? Am I on the right Poképath, or am I being denormalized?!?

_{Oh, and excuse me if you feel I've been verbose in explaining this issue. I'm a friendly businessman, you see. Cheers!}

>>7
I think you are slightly confused. >>1-san deals with customers, not players.
But yeah, I'd suggest creating a timestamp too.
OP could even go a bit further and instead store changes to the Pokémon inventory, as in:

+----------------+
| pokemon_entry  |
+----------------+
| (species data) |
| date           |
| added/deleted  |
+----------------+

This is more complex because it would require "playing back" all the changes to find the exact contents of the package the customer licensed, but it would make the system much more robust and ready to help >>1-san leverage his core skillsets and expand his Pokébusiness.