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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
The current setup is with CF and a bit of Javascript:
<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
</cfquery>
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
The genre table is actualy the same as the subcategory table (Except for the name )
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 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.
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.
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?
But what is not right in my approach? 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 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
Thing is it’s not for me and as always I try to think about the client
I have to think about another solution than I quess
What could I do better?