Name: Anonymous 2010-02-08 13:03
I have a table that is responsible for holding data relating to categories and subcategories -- let's say, as an example, songs by band based by genre. I'm using a multi-key table to hold that information:
songs
+-------------+----------------+------------------------+
| category_id | subcategory_id | song |
+-------------+----------------+------------------------+
| 1 | 1 | All My Lovin' |
| 1 | 2 | Sympathy for the Devil |
+-------------+----------------+------------------------+
...where category_id = 1 might be 60's music, and subcategory = 1 is The Beatles.
So, let's say I have base prices that I want to attach to these categories -- a standard base price that applies to every song in a given category. I would have a table that relates to categories like so:
category_prices
+-------------+-----------+
| category_id | baseprice |
+-------------+-----------+
| 1 | 0.99 |
+-------------+-----------+
But let's say I want to have base prices on the subcategory. Let's say I want those base prices to override base prices set by category. How would you design this table?
Right now I appear to have two options.
The first (and most obvious) is to create two tables. The second table would have two keys:
The second is to put it all in the same table. For base prices that are only applying to
Either way works fine, so at this point I'm simply curious. What would you do?
songs
+-------------+----------------+------------------------+
| category_id | subcategory_id | song |
+-------------+----------------+------------------------+
| 1 | 1 | All My Lovin' |
| 1 | 2 | Sympathy for the Devil |
+-------------+----------------+------------------------+
...where category_id = 1 might be 60's music, and subcategory = 1 is The Beatles.
So, let's say I have base prices that I want to attach to these categories -- a standard base price that applies to every song in a given category. I would have a table that relates to categories like so:
category_prices
+-------------+-----------+
| category_id | baseprice |
+-------------+-----------+
| 1 | 0.99 |
+-------------+-----------+
But let's say I want to have base prices on the subcategory. Let's say I want those base prices to override base prices set by category. How would you design this table?
Right now I appear to have two options.
The first (and most obvious) is to create two tables. The second table would have two keys:
category_id and subcategory_id. Overriding would happen when you set up the JOIN.The second is to put it all in the same table. For base prices that are only applying to
category, I would make the subcategory_id = 0. Overriding would happen with a precise SELECT.Either way works fine, so at this point I'm simply curious. What would you do?