Complicated dependent dropdown situation

In a table of 7 categories I have 2 categories with the exact same subcategories (about 150 each) I would like to include a dependent drop down in my cms. The way I have it working now would mean that I have 2 times the same 150 subcategories in my database. I was wondering if there is a way to make just 1 group of 150 subcategories dependent of 2 categories.

Subcategories table:

 CREATE TABLE `subcategories` (
 `subcategory_id` int(4) NOT NULL auto_increment,
 `category_id` int(3) NOT NULL,
 `subcategory` varchar(64) character set utf8 collate utf8_unicode_ci NOT NULL,
 PRIMARY KEY  (`subcategory_id`),
 KEY `subcategory` (`subcategory`)

The current setup is with CF and a bit of Javascript:

<cfif isDefined('Form.category_id')>
    <cfset page.select_category = category_id />

Subcategories query :

<cfquery name="getSubcategories" datasource="#application.dsn#">
    SELECT subcategory_id, subcategory
    FROM subcategories
    WHERE category_id IN ( <cfqueryparam cfsqltype="cf_sql_integer" value="#page.select_category#" /> )
   ORDER BY subcategory

Output in form:

<cfif isDefined('page.select_category')>

<select name="subcategory_id" class="selectfield">
<option value="" selected>select</option>
<cfloop query="getSubcategories">
<option value="#subcategory_id#">#subcategory#</option>


<select name="subcategory_id" class="selectfield">
<option value="" selected>select</option>


I do hope that anyone have any suggestions on this. I would be very, very happy.

Sounds logical. I’m goin to play with this and let you know, Thanks

I would probably create something like the following

You can still do all the stuff you’re wanting to do, this is just WAY more efficient that what I’m understanding you’re doing now.

sub-genres, sub-formats all taken care of via the parent_id columns.

I see what you mean. Genre can be related to 2 categories. (Viny Records and CD’s) The way I have it now is because of the dependent drop-downs (this is the only way i know). I know I could separate the tables completely and create a third relational table but then I don’t know how to create my drop-downs. It is working though. But If you have a better solution, you’re never to old to learn something new :slight_smile:

Thank you for the reply cfStarlight.

The genre table is actualy the same as the subcategory table (Except for the name :slight_smile: )

To give an example There is a category Vinyl Record. In Vinyl Records you can have genes like: acid rock, heavy metal, hard rock, country rock, acid jazz, traditional jazz, modern jazz, experimental jazz etc (the list is endless). Than there is the subcategory table. A vinyl album can come in different formats: Single, 7", 12" and LP

The same goes up for the CD’s which this shop is selling as well :slight_smile: That’s why my earlier question.

How does genre relate to the other 2? ie Could you give us an example showing a few categories, subcategories and genres … so we can get an accurate picture. Also, what does the genre table look like?

But what is not right in my approach? What could I do better?

I don’t know if it’s definitely wrong or not… But the tables you described don’t seem to represent the relationships as I understand them.

You said the genre table is like subcategory. Meaning it has a category_id column. So a genre has to 1 (and only one) category. That doesn’t jive with how you described it:

In Vinyl Records you can have genes like: acid rock, heavy metal, hard rock, country rock, acid jazz, traditional jazz, modern jazz, experimental jazz etc

That description suggests a genre can be related to MANY categories. If that’s true, then the current table structure doesn’t make sense. Because it doesn’t allow a genre to be related to MANY categories.

So it doesn’t seem to accurately represent the relationships.

Yeah, I can’t say I understand the issue either.

Going strictly off the names, I would assume category and subcategory have a one-to-many relationship. What you’re asking now … makes it sound like they have a many-to-many relationship. If that were really the case, you’d have a totally different table structure. ie Three tables instead of two. Though as downtroden suggested, I usually create a single table for parent-child relationships. I’m not sure why you need two tables here.

who’s more important here, you or the sql server?

I would say “sql server”… but I think he’s using MySQL :wink:

(Sigh… I guess we’re less important than the database. Sad, but true)

Don’t know that, that would be a good idea. What if they ever deviated from one another, you’d be screwed.

Also, maybe it’s just me, but I think your table could be easier to read, for nav structures you should only need id, title and parent_id… anything else is case by case. Only reason i’m saying that is because I don’t really understand your setup off the bat.

BUT… to answer your question, don’t do what your asking to do. Bad idea and the only gain would be your convenience, not the databases… and really… who’s more important here, you or the sql server? :slight_smile:

But what is not right in my approach? :eek: What could I do better?

By the way I found a solution for my initial question about the drop downs: I’m still testing the application right now but before launching the site the owner will start with an empty database, so he will be the one adding the genres to the database. He have to add the list with genres only ones, but a double insert will take place so a certain genre will be inserted for both categories (Vinyl Records and CD’s). Thank you and downtroden again for pointing that about the database to me.

The genre table is actualy the same as the subcategory table

I’m not a db expert, but that doesn’t sound quite right to me. It sounds like category has a many to many relationship with genre.

I would think you could find “Vinyl Records” in many genre’s: “rock”, “traditional jazz”, etc… AND items in a genre could be in many different formats (or categories) “Vinyl Records”, “CD’s”, etc…

@ downtroden & cfStarlight :slight_smile: I was thinking that this would come up (:

@ cfStarlight: About the single table for parent-child relationships! It’s not only this relationship. There is 1 more child involved (genre). How would you table look like and the involved query

@ cfStarlight: You’re right, it’s mySql indeed :slight_smile:

Thing is it’s not for me and as always I try to think about the client :eek:

I have to think about another solution than I quess