SQL Table Design Opinion

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: 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?

all in one
Beacuse you might add other coluns (e.g. genre) more easily, and can set entire catogories according to it.
And you could create another pricing model easier.

I would go with one table. And you could use null instead of 0, to make it clearer.

SQL

Now u have 2 problems!!

>>4
At least three, if you count the pronunciation.

>>3
That's what I thought, too. I really would have preferred to use null, but I had problems using null as a key, I guess because null is not UNIQUE by definition. But it's possible I'm just setting up the table wrong. I feel really uncomfortable using 0, it seems to me like a problem just waiting to happen.

(What I mean: category_id and subcategory_id are both keys. That way you can get things like


categories
+-------------+--------------+
| category_id | name         |
+-------------+--------------+
| 1           | 60's rock    |
| 2           | 80's hair    |
+-------------+--------------+
subcategories
+-------------+----------------+---------------+
| category_id | subcategory_id | name          |
+-------------+----------------+---------------+
| 1           | 1              | The Beatles   |
| 1           | 2              | The Stones    |
| 1           | 3              | Cream         |
| 2           | 1              | Poison        |
| 2           | 2              | Whitesnake    |
+-------------+----------------+---------------+

...which seemed conceptually accurate to my needs.)

>>6
I don't really use SQL, but I don't see the problem. I believe you can still have a foreign key constraint on a field that can be null.

>>7
Yes, but you can't have a NULL value in a primary key column.

Although this solution involves three tables and a view, it is by far the more maintenable IMHO:

Table song:
song_id |    title    | category | subcategory
---------+-------------+----------+-------------
       1 | Good Night  |        1 |           1
      61 | Hax my Anus |        7 |            

Table category:
category_id | description | price
-------------+-------------+-------
           1 | 60's        |  0.99
           7 | 2000's      |  0.29

Table subcategory:
subcategory_id | description | price
----------------+-------------+-------
              1 | Beatles     |  1.25

View song_prices:
SELECT title, category.description AS category, 'N/A' AS subcategory, category.price FROM song, category WHERE song.subcategory IS NULL UNION
SELECT title, category.description AS category, subcategory.description AS subcategory, subcategory.price FROM song, category, subcategory WHERE song.subcategory IS NOT NULL

    title    | category | subcategory | price
-------------+----------+-------------+-------
 Good Night  | 60's     | Beatles     |  1.25
 Hax my Anus | 2000's   | N/A         |  0.29

SQL: When Excel isn't ENTERPRISE enough.

12 Name: Anonymous 2010-12-06 9:57

^{Back to /b/, ``GNAA Faggot''}