Infinite database

Anon

Hello,

Wondering if anyone can point me in the right direction for a resource to help.

I'm building a site right now which will allow all kind of user inputs in a certain topical area.

But that topical area can be broken down into maybe 25 subtopics, and the subtopics can be further broken down, and the subsubtopics, and maybe even the subsbusubtopics.

What kind of db design would gracefully handle not only this kind of topic-subtopic-subsubtopic-subsubsubtopic requirement ... and also be simple to add new main and subtopics to?

Thanks!
-john

Anon

For hierarchical structures like this, if you're just storing simple strings, it's sometimes best to just use a tree of blank directories. I recently built a small community web site directory and used this method for the hierarchical categories, and it worked very well. It makes it easy to navigate with chdir(...) and readdir(...) and especially easy to modify on the fly.

Keegan

Anon

Hello
I don't think that a flat and pretty riigid structure like the directories is what he needs. I think a dynamical driven structure is what you need to use. Have a look on the net at phphoo, ported from perl's yaHOO's clone.
The structure of the database should be designed according to its purpose.

Regards,
Calin

Anon

You're a little vague about what you're trying to achieve, but here's my guess.

You can achieve what you need with three tables.

The first table lists the existing discussions, and their "names". Discussion number is a primary key. Smething like:

DiscNo DiscName
000001 Sport
000002 Politics
000003 Arts
000004 Soccer
000005 Baseball
000006 UK Premiership
000007 US Presidential Election
000008 Manchester United
000009 Leeds United
000010 Italian Serie A
000011 Juventus
000012 AC Milan
... etc

The second table lists how the above discussions are grouped.

DiscNo SubDisc
000001 000004
000001 000005
000002 000007
000004 000006
000004 000010
000006 000008
000006 000009
000010 000011
000010 000012

Finally, you have a table listing the individual posts by discussion number.

DiscNo MessageNo OtherMessageFields
000001 000000001
000001 000000002
000001 000000003
000001 000000004
000002 000000001
000002 000000002
etc

I've deliberately avoided going into detail on how to thread the individual messages, as this is covered in umpteen existing bulletin boards.

With this structure, you could have a top-level page which lists general headings (Sport, Politics...)

Clicking on Sport would open discussion number 000001. This would look up the posts for this discussion and, maybe, display the headers on the page. But as well as that, it could look up the groups table and list all the groups for discussion number 000001. In this case it would provide links to discussions 000004 and 000005 (Soccer and Baseball).

Clicking on Soccer would open discussion number 000004. This would look up the posts for this discussion and, maybe, display the headers on the page. But as well as that, it could look up the groups table and list all the groups for discussion number 000001. In this case it would provide links to discussions 000006 and 000010 (UK Premiership and Italian Serie A).

Clicking on UK Premiership would open discussion number 000006. This would look up the posts for this discussion and, maybe, display the headers on the page. But as well as that, it could look up the groups table and list all the groups for discussion number 000001. In this case it would provide links to discussions 000008 and 000009 (Manchester United and Leeds United).

So, as you can see the structure allows for infinite levels of nesting. For instance, you could create a new discussion for each of the players on the Manchester United panel, and include them under the team discussion. For even more detail you could have a range of discussions for each player... and so on.

All you need to do is to fit each new discussion under an existing one, or else at the top level. This structure is also very flexible. Imagine if David Beckham moved to Juventus. All you would have to do is edit the groups table to move him from the Manchester United group to the Juventus group.

Sorted.

And I can even write the whole thing for you for a modest fee!

Cheers,

Jack

Anon

Jack,

I work in freelance . Just for my knowledge, how much would you charge
for this dynamical db structure?

Calin

Anon

http://phpbuilder.com/forum/read.php3?num=5&id=5000&thread=4970

I just posted the correct table structure for this on that post.

You want a table of categories with a primary key and a foreign key to the primary key so you can reference the parent and keep drilling up until you get to the top. It's also very easy to drill down. I suggest a recursive function that will take a primary key and array by reference and you could build your whole tree very quickly, but with a bunch of queries. While you usually won't need the whole tree, just do a search of foreign key = null or 0 for top level categories and do a query for each of those to find out if they have children then provide the interface to drill down through that category.

Anon

Thank you very much everyone for your help ... there's some great info for me to chew on here.

Much appreciated!
-john

Anon

If I understand what you're saying correctly, then using the structure you've outlined, the following would be true:

every single topic, no matter what level (sub, subsub, etc.) would be in the topics or DiscNo table.
the second table lists relationships.So topic4 or DiscNo4 is a parent to 10 which in turn is a parent to 11 and 12:
(4) Soccer
- - (10) Italian Serie A
- - - - - (11) Juventus
- - - - - (12) AC Milan

Now, the only question is, what kind of query would quickly pull the parent topics out for display?

For instance, if my toplevel categories were Soccer, Basketball, and Ice Hockey, and they each had subtopics as shown above, what kind of query would pull out Soccer, Basketball, and Ice Hockey for display on my main page?

Do I need another column in the DiscoNo and SubDisc table listing parents?

For instance, if parent for topic4 (Soccer) is 0 or null, then a query can be constructed to find that, and I know immediately that it soccer a parent.

If parent =4, or parent=10, then I know that the value is not a parent but a subtopic.

Does this make sense?

I'm trying to find a way to pull info out of this db in structured ways that minimize the amount of knowledge the application has about what's actually there.

Thanks again.

Anon

Yep,

you could have a dummy top-level "discussion" with a DiscNo of zero.

Then, you could make all your top-level discussions be subdiscussions of the dummy zero discussion.

Then a select * from groups where DiscNo = 0 will give you your list of parent items.

To find a topic's parents, you only need to a reverse lookup on the groups table. Make "SubDisc" an alternate key.

Now, for any given discussion number, you can find its parent or its children. You can use this to thread everything, draw collapsable/expandable trees... whatever you need to do.

Hope this give you a starting point.

Jack

Anon

Hi Calin,

Before I can quote on a job, I need the full details. Then I can estimate the effort and costs involved.

Cheers,

Jack

Anon

I have a related question on this same topic. I am also trying to develop a
category tree like the one described in the previous messages. I was planning on implementing it using one table with the following structure:

"Category Table"
category_id (primary key)
parent_id (foriegn key)
other fields, etc

The top level categories would have parent_ids of zero and child categories would have the parent_id=category_id of their parent. I think it would be fairly easy to drill down through the categories to create a tree in this fashion. I was then going to have a table of items that would be associated with the bottom level categories (ie leaves) of the tree. These items would be in a seperate table and would have a foreign key for the category they belong to.

I wanted to be able to search all items in a specific main category. The problem I was running into was I would have to drill down to all of the leaves of the tree using a recursive algorithm each time I did a search and then join this with the items table. This seemed like it would be a lot of time and extra work. Is there another format I can use that would solve this problem?

Thanks,

Chaz

toma42

Their are CS algorithems for exactly what you want...do searches....learn more 🙂

Anon

Tom,

Thank you for your response.

Actually, I don't think I was clear about what I wanted. I actually know how to write the recursive search routine to go through and pick out all of the items. However, for content that is created on the fly, if each time a search is done this type of recursive algorithm is used it would add a great deal of overhead and slow things down a lot. What I was looking for is a way to restructure how I had set up the categories in order to avoid the recursive search.

Thanks,

Chaz

uioreanu

Hello,

You need to normalize the databases, but first you should decide where you need the fastest reponse timinigs: on inserting or on reports. For inserting speeding a recursive structure like you described would be usefull, but still need normalisation, and for output-ing maybe another structure,level-oriented would be better.

imho the point of discussion lists is answering, not refering to web. We don't surf blindly, but interact with each other. that should be the essence of web.

Regards,
Uioreanu Calin