Database Development

[rulian]

What would be a good septup for an article database that will simply have main catagories and their subcatagories: for example:

--cars
----manufacturers
----history
----vendors

--books
---horror
---drama
---comedy

---movies
---horror
---drama
---actors/actresses

you see where I have a problem, they subcatagories are inconsistent. I thought of making 3 different tables, 1 for catagories, 2 for subs, and 3 for articles on mysql, so articles would look like:
[ ID ] [cat_id] [cat_sub] [article]

but the problem lies when I have to assign the subcats to the cats, I would have to specify each one manually which defeats the purpose of having them on different tables.
so what should I do?

[jjoske]

You could make a genera tree with each sub genera linking back to its parent. Structure would be somehting like this:

id
parent_id
genera_name

[rulian]

I'm not quite following you, so let's say I have 3 main genera's:
Cars, Books, Movies:

if they all had the same subs, it would be easy to add a new main genera, but if they are inconsitent, i need to assign sub genera's. for example. the following is my tree:

-plays
--horror
--comedy
--drama
--mystery
--broadway

-books
--horror
--comedy
--drama
--mystery

-movies
--oscar winners
--boxoffice flops
--horror
--comedy
--drama
--mystery

Now I need to add a main genera called cartoons that will have comedy, mystery, and warner brothers: as you can see this makes it difficult for queries and modifications to the database

[[deleted]]

Use a thread-based tree, like in forums.
That can be expanded as much as you like.

name | id | thread | parent
plays | 1 | 1 | 0
horror | 2 | 1 | 1
comedy | 3 | 1 | 1
drama | 4 | 1 | 1
books | 5 | 2 | 0
movies | 6 | 3 | 0
comedy | 7 | 3 | 6
mystery | 8 | 3 | 6
horror | 9 | 2 | 5

To get all the 'main' categories, just select all records without a parent (parent=0)

To get the 'plays' tree, select all records that have the same threadid that the 'plays' main record has.
From there you can put subcategories under subcategories simply by changing the parent to whatever category you want to create a sub under.

for ex; the last 'horror' in the list belongs to thread 2 (books) and is a subcategory under record 5, which is the main category for books.
If I make a new record
comics | 10 | 2 | 9
It would be a category 'comics' in the books thread, as a subcategory of 'horror'.

[Anon]

One way of categorizing things is as you are proposing: a heirarchy. This works well enough for many purposes, and it would probably serve well here too. But you're quite right about child nodes in one subtree not being related to child nodes in another subtree. <i>This is inherent in the nature of a heirarchy.</i>

For a different method of classification, consider an attribute matrix. Here, items simple have <i>n</i> of <i>m</i> attributes. For example, your catalog might have:

&nbsp;&nbsp;medium: book, play, movie, ...

&nbsp;&nbsp;genre: horror, comedy, mystery, narrative, ...

&nbsp;&nbsp;language: english, french, german, tagalog, ...

Now, to locate a horror film under this method, you don't look for the subtype 'horror' under the main category 'film'. Instead, you look for items which have 'medium == movie' AND 'genre == horror'.