Hey, this one has stumped me for over a week now. I keep thinking I've got it, but I don't. What do you think?
I have accounts, each account has signed up for a package of products, kind of like a value meal at McDonalds. The only difference is that the number of items in a packages varies, here's an example:
<pre>
account o1 o2 o3 o4 o5 o6 o7 o8 price
1 x x x x x x x x 50
2 x x x x x x x x 50
3 x x x o o o o o 35
4 x x x o o o o o 35
5 x o o x x x o o 40
</pre>
*o1 through o8 are boolean fields
The number of options is potentially much greater, which leads you to believe that the number of possible combinations is very high, but actually, there probably won't be too many different combinations.
My First thought was to break this into two tables such as:
<pre>
+---------+
|Accounts |
+---------+
|accountID|
|package |<-+
+---------+ |
|
+---------+ |
|packages | |
+---------+ |
|package |--+
|o1 |
|o2 |
|o3 |
|o4 |
|o5 |
|o6 |
|o7 |
|o8 |
|price |
+---------+
</pre>
This is where things started getting tricky. After looking at this, I decided what I'd like to do is some how document what each option (o?) is. Therefore
o1-> "Cherry Pie", "600 Cal.", $1.19, "You'll enjoy this cherry pie"
Doing this would be difficult though, because I don't know how to create the structure for this table. I don't see a way to join data from a row to a column.
<pre>
+---------+ +--------+
|packages | |options |
+---------+ +--------+
|package | |optionID|
|o1 | |name |
|o2 | |price |
|o3 | |calories|
|o4 | |descrip |
|o5 | +--------+
|o6 |
|o7 |
|o8 |
|price |
+---------+
</pre>
Ideally, I would be able to get two types of information out of the whole thing.
1. AccountID and all chosen options.
Mr. Smith ordered package 1 (BigMac, French Fry, Coke, Cherry Pie, McFlurry) price is $12.00
2. Package description with human readable least of options.
Order a Pacakge 2 for only $13.47, it has Chicken McNuggets, French Fries, Dr. Pepper, Hot Fudge Sunday and BarBQ sauce.
Maybe I have the structure all right, but I just don't know how to join the information. It's made difficult by the fact that every time I try to think about the solution, my concentration starts to drift (maybe it's all this talk about the food).
This is of course simplified data, but the analogy should work. Another comparison might be the cell phone company, every month they have a new package, 200 min, free weekends, state wide long distance, $22.95.
How do you describe this "a la carte" style data?
Thanks for your help.
Matt
I have accounts, each account has signed up for a package of products, kind of like a value meal at McDonalds. The only difference is that the number of items in a packages varies, here's an example:
<pre>
account o1 o2 o3 o4 o5 o6 o7 o8 price
1 x x x x x x x x 50
2 x x x x x x x x 50
3 x x x o o o o o 35
4 x x x o o o o o 35
5 x o o x x x o o 40
</pre>
*o1 through o8 are boolean fields
The number of options is potentially much greater, which leads you to believe that the number of possible combinations is very high, but actually, there probably won't be too many different combinations.
My First thought was to break this into two tables such as:
<pre>
+---------+
|Accounts |
+---------+
|accountID|
|package |<-+
+---------+ |
|
+---------+ |
|packages | |
+---------+ |
|package |--+
|o1 |
|o2 |
|o3 |
|o4 |
|o5 |
|o6 |
|o7 |
|o8 |
|price |
+---------+
</pre>
This is where things started getting tricky. After looking at this, I decided what I'd like to do is some how document what each option (o?) is. Therefore
o1-> "Cherry Pie", "600 Cal.", $1.19, "You'll enjoy this cherry pie"
Doing this would be difficult though, because I don't know how to create the structure for this table. I don't see a way to join data from a row to a column.
<pre>
+---------+ +--------+
|packages | |options |
+---------+ +--------+
|package | |optionID|
|o1 | |name |
|o2 | |price |
|o3 | |calories|
|o4 | |descrip |
|o5 | +--------+
|o6 |
|o7 |
|o8 |
|price |
+---------+
</pre>
Ideally, I would be able to get two types of information out of the whole thing.
1. AccountID and all chosen options.
Mr. Smith ordered package 1 (BigMac, French Fry, Coke, Cherry Pie, McFlurry) price is $12.00
2. Package description with human readable least of options.
Order a Pacakge 2 for only $13.47, it has Chicken McNuggets, French Fries, Dr. Pepper, Hot Fudge Sunday and BarBQ sauce.
Maybe I have the structure all right, but I just don't know how to join the information. It's made difficult by the fact that every time I try to think about the solution, my concentration starts to drift (maybe it's all this talk about the food).
This is of course simplified data, but the analogy should work. Another comparison might be the cell phone company, every month they have a new package, 200 min, free weekends, state wide long distance, $22.95.
How do you describe this "a la carte" style data?
Thanks for your help.
Matt