Nested tables

[Azu]

I know how to make tables in a database and how to make rows in a table.

What I am trying to find out how to do is how to make a table inside of another table like I can make one inside of a database?

 

In a flat file database I would just have some folders, and some files in them, and sometimes some folders in them, and those folders would have folders etc

 

Folders = tables

Files = rows

 

Please tell me how I can do this in MySQL.

 

I know there are ways I could get similar functionality but I would rather use nested tables because I think the performance would be much better.

 

Instead of giving each table an ID and an ID of the table it is in, and running queries to navigate up this artificial tree to find out what the higher up tables in, I'd rather just nest the table inside another table, I think the performance would be better this way, because I could just look for all tables that are nested inside 1 of the tables, instead of searching through all the tables in the database or having all the rows in 1 table and searching through them all.

 

I am using the MyIsam storage engine in MySQL right now but it's okay if I would have to use a different storage engine/database program as long it can do this and do it fast.

 

Please tell me

[btherl]

If performance is your primary concern, I think the best approach would be to describe what you want to do, and ask a mysql guru to suggest an efficient approach.

 

What you've done is suggest an approach and ask how to implement it.. but there may be much better approaches.

 

I am not a mysql guru, which is why I am not suggesting anything   But I will say that creating large numbers of tables is usually bad database design, unless you have a good reason to do it (eg partitioning).

[Azu]

I want to make a bread-crumb based navigation and storage system for my forum and for most of my website, which is all generated dynamically with PHP from a MySQL database, instead of files stored in folders.

 

I want to make it run as fast as possible while still displaying in real-time.

 

I thought that if the tables could be in other tables, like folders in other folders, then it would be a lot faster to be able to just search through one of these "sub-tables" just like it is faster to search in 1 little folder instead of the whole program files folder.

 

If there is a better way to do this then that would be great!

[bubblegum.anarchy]

What is the difference of where a search location is as long is the location is known.

 

The visual way you are looking at a folder storage system does not transpose well to how the information is actually stored as finite data... a folder location is stored as seperate bit of information so the actual location becomes a little irrelevant.

 

Don't stop dreaming though, your imagination will serve you well.

[Azu]

So it isn't any faster for it to just search through 1 table with 10 rows then to search through 1 table with 100 rows or 10 tables with 10 rows each?

 

:s

[bubblegum.anarchy]

I am not a mysql guru like the ones btherl refers to but looking for a bit of information that is at the end of a table of 1000 records is certainly going to take longer than looking through a table with 10 records, but this rational can't be applied to referencing tables... there is no point in storing uniquely define record in seperate location unless you can have an additional field to destinguish the records and what normalization is all about.

[Azu]

What I mean is like.. you know how in explorer, if you have 1 folder, and there is 100 folders in it, and each of them has 100 files?

It's much faster having it like this and just searching in one of those folders (E.G. "Music") for something, since it only has 100 results to look through, instead of looking through all of the files in all of the folders (or having them all in 1 folder).

 

It works like this in MySQL also.

 

Having a bunch of little tables and just searching through one of them, is much faster then searching through them all or then having 1 big table and searching through it.

 

I just want a way to make tables inside of other tables, just like you can make folders inside of other folders.

 

This way if say there is 1 thread on the forum there is 10 replies to it and 2 other threads in it that have 10 replies each, and each of those has 2 more threads with 50 replies each, each thread could have it's own table, so that only that one table would need to be searched through to find the replies, instead of looking through them all.

 

I'm pretty sure there would be a big performance increase as the nesting grew deeper and deeper.

 

I might be totally wrong about this and if I am please explain what is wrong about this.

 

And if not then please tell me how to make nested tables

[anatak]

Azu,

 

it depends on how you look through your tables.

 

can you give me a concrete example of what you want to do ?

 

I think what you want to do is something like this.

 

on your website you have 3 categories

1 books 2 movies 3 music

in books you have two subcategories

1.1 cookbooks

1.2 programming books

in movies you have two subcategories

2.1 american movies

2.2 french movies

in music you have three categories

3.1 jazz

3.2 house

3.3 hip hop

 

what you can do is link those tables together with references

for example in the subcategory programming books table you make a link to the table that is the parent of that subcategory (here books)

 

that way you have an id field in the parent table (books)

and you have the same id in the subcategory table (cookbooks)

 

now you can easy join the tables

 

This will become a slow query in the end of course but you can solve that with ADODB (look for the tutorial here http://www.phpfreaks.com/tutorials/110/0.php )

 

I think that if you create a table for every thread you is going to be created your database becomes slower as it will have to run through more indexes.

but I can be horribly wrong of course.

 

anatak