MySQL Bugs: #61120: Ability to create real sub-folders within DB folders

Bug #61120	Ability to create real sub-folders within DB folders
Submitted:	11 May 2011 0:31	Modified:	28 Jun 2011 16:31
Reporter:	Rich Gosselin	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S4 (Feature request)
Version:	5.2.33b - all	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	grouping, sub-folder within db folder

Description:
Allow the user of Workbench to create sub-folders within the database folder. When user has many hundreds of tables, a single folder for all tables in db becomes difficult to group. The same principle applies for queries & procedures/functions. In an elaborate system the only way to currently accomplish this is to create different db's for diferent tasks or clients or ??.

This is a condition of use where very similar or identical tables are used for a variety of clients or entities, and these must be set up quickly in very large data warehouses. This would also allow some scripting to allow use of reporting & functionality across many data sets, being able to group them or treat them as independant.

How to repeat:
N/A

User reqesting feature is running Windows 7 Enterprise Workstation with latest versions of everything! 5.6.2-m5-winx64. 

Suggested fix:
Allow user to create sub-folders within database folders. My usage has been mostly Windows (XP, 2003, & 7) but this feature would / should apply to UNIX type OS as well.

NOT like Navicat 'groups' that allow you to segregate tables & queries & procs, but don't really segregate them...so when you copy a set to a new folders (table defs for instance) you get an error for already existing fNames or that fname with - copy appended to it's fName. 

Rather have real folders with their unique paths (and) deal with that path in set up scripts.

Thank you for the feature request.

I have a few questions so I can understand the request.

What kind of DB is it where different clients have tables in the same schema?
Assuming you have 3 tables A, B and C that were separated into 3 groups, like:
FooTables
    - A
BarTables
   - B
BazTables
   - C

How would they become created in the database? Normally they would be named just like they are, ie, A, B and C; but from your request it sounds like you want something different. Could you give an example?

Consider this arrangement:
db1
fee:
tabl1, tabl2, tabl3, tabl4, ~~~~ tabln
query/proc/fun1, qpf2, qpf3, ---- qpfn
foo:
tabla, tablb, tablc, tabld, ~~~~ tabln
query/proc/funa, qpfb, qpfc, ---- qpfn
fum:
tabl1, tabl2, tabl3, tabl4, ~~~~ tabln
query/proc/fun1, qpf2, qpf3, ---- qpfn
db2
fil:
tabl1, tabl2, tabl3, tabl4, ~~~~ tabln
query/proc/fun1, qpf2, qpf3, ---- qpfn
fat:
tabl1, tabl2, tabl3, tabl4, ~~~~ tabln
query/proc/fun1, qpf2, qpf3, ---- qpfn
foy:
tabl1, tabl2, tabl3, tabl4, ~~~~ tabln
query/proc/fun1, qpf2, qpf3, ---- qpfn

Although these are all nearly identical, the code that manipulates them may and usually does differ slightly.

Someone might suggest that because they are so similar they be all in one db with indicators. However, fee may have two data tables with one being 25,000,000 records, and the other @ 40,000,000. Foo in my example has 65,000,000 rows, with fum at about 90,000,000.

In addition to these main tables I have been building my indexes manually with very specific sort instruction to minimize run times. It’s much faster to build specific ordered tables than deal with MySQL’s sort & insert process at these volumes. There is one major operation where I construct a series of indexes and build almost 850,000,000 very small records to end up with a small tight reference index of 3 fields and 65,000,000 rows. I seem my swap space grow & shrink by 60 gig on each stage of this operation...simple select/insert statements that build intermediate tables very quickly and then use their contents to build the next.

Because of the size and run times of the various process statements, fee, foo, and fum must be kept separate. Also, the fee, foo, & fum organizations would be very upset if we somehow mixed things up between them.

Also consider that these tables come into my shop and need to be processed very quickly over the space of a month, and then may disappear until 6 months or a year later.

Yes, I realize this is an absolutely silly use of MySQL, but it's what we do, and we don't have a business model that would allow use of expensive data managers such as Oracle...wish we could use things like that.

Also, my production staff consists of me, myself, & I, so fancy things get left in the dust, including elaborate db structures to mitigate some of these issues. This small start-up operates on threads from the proverbial shoe string.

Perhaps what bothers me the most, is that every tool I use, whether it be Workbench, Navicat, EMS, or dbForge Studio; they all have a different manner of organizing stuff. And it seems that non of it transfers to the other unless I do something manually. I would like to have just one method that all managers can work with...but that's beyond the scope of this forum. That's something that would be a series of hooks in MySQL itself.