Is there a way to visually organize database tables (and saved queries and views)?

February 2, 2012 3:33 PM

I have a wonderful (Oracle) SQL database and I use Navicat Lite to crunch around in it. My problem is that I'm staring at lists of 50+ tables, 60+ saved queries, 10+ views, etc. Is there anything I can do to somehow organise things in a visually pleasing and/or functional way?

What do database administrators do? Do they use naming conventions to cluster tables together? Is there some sort of way I could group tables into folders (even if it's meaningless to the database)? Color-coding? Dividers? Please help me; this is getting unwieldy to navigate through.

posted by iamkimiam to computers & internet (4 answers total)

I do a bit of Oracle database administration but have never used Navicat.

When i have the opportunity I do use naming conventions to make it easier to identify groups of tables ,stored procs, views etc.

I'm not sure if this is your thing but I do a fair bit straight off the command line running queries similar to this ..

SQL> select table_name from user_tables where table_name like 'FOO_BAR%';

... which I find useful for selecting groups of objects.
posted by southof40 at 5:30 PM on February 2

You want to create an ERD of your database. When I was first learning about DBs I found it very helpful. Here is a list of various ERD SW packages, I can't vouch for any of them except ER Studio. ER Studio is super expensive but amazingly powerful.
posted by Confess, Fletch at 8:32 PM on February 2

Our shop uses MS Sql Server so my experience may not transfer to Oracle exactly, but the MS SQL management studio has a section where you can bind hotkeys to stored procedures of your choosing. When I figured this out I probably improved my productivity 80% in a day, as you can feed the results of whatever you have highlighted into these procedures.

for example, to extremely rapidly peek at the top few rows of a table, something I easily do 100+ times a day -

create procedure sp_quickcount
(
@tablename varchar(300)
)
as
declare @sql varchar(1000)
set @sql = 'select top 100 * from ' + @tablename
exec (@sql)

I also frequently use one like
create procedure sp_quickfind
(
@snippet varchar(300)
)
declare @sql varchar(1000)
set @sql = select * from sys.views where name like '%' + @snippet + '%'
set @sql = select * from sys.tables where name like '%' + @snippet + '%'
set @sql = select * from sys.procedures where name like '%' + @snippet + '%'

so i can just highlight the name or part of a name and this will display any table, view, or procedure with that in the name. Much faster than navigating a tree structure.

I read a study somewhere that the average person is significantly faster at finding something in a large email mailbox if they use gmail-style search and threading than if they meticulously organize their inbox into folders - and the first method takes no maintenance time as well, whereas the second requires a person to devote a non-negligible amount of time to maintaining the meta-sorting. This is the same idea, just completely ignore sorting it and use a good search method.
posted by spatula at 11:14 PM on February 2

Naming conventions are the way I always deal with this. For reference the database I work with most of the time (Sybase, not Oracle but the principles are the same) has 768 tables and over 600 stored procedures.

ERD will not help in my situation, I doubt anyone could read a diagram of that size even if a tool would generate it!

But it should help you. Here we use CA Erwin, but that starts at nearly $5000 per seat. I understand Quest Software have a trial version of Toad Data Modeller for download, and there are several free offerings.
posted by hardcode at 2:55 AM on February 7