Patent Application
20120311474
Relational databases are often used to implement data storage mechanisms for business applications where the data maps to real-world items, such as financial records, medical records, personal information, manufacturing and logistical data, and the like. For novice users, relational databases may be hard to visualize and understand. Traditional forms of exploring and querying relational databases focus on views of individual tables. As the relational database grows increasingly normalized, with the number of tables increasing and the number of human-readable columns in each table decreasing, it may become harder and harder to understand the database by looking at any table in isolation.
Traditional database visualization tools may allow users to create diagrams, such as an entity-relationship (“ER”) diagram, that show all the tables in the relational database, along with how those tables are related. Tables are drawn as boxes containing schema, and the relationships between tables are depicted as lines connecting the boxes. Markers on the lines or their endpoints may indicate the kind of relationship that the connected tables share. However, these diagrams may grow too complicated to understand for even a moderately sized relational database.
It is with respect to these considerations and others that the disclosure made herein is presented.
Technologies are described herein for generating and manipulating visual maps of relational databases. Utilizing the technologies described herein, a novel metaphor for database visualization may be implemented that visually depicts the tables in a relational database as a database map. The database map may be generated such that the tables in the database that are related to each other appear as close to each other as possible in the database map. In this way, tightly connected or related groups of tables may be displayed together in regions. This may offer an improvement over traditional ER diagrams, which generally force the user to hand-position the tables on the surface, or have a default positioning based on alphabetical order or some other simple metric.
In addition, while the tables are initially visualized as tiles containing the name of the table and/or other textual label, a user may be provided facilities to explore, search, and zoom the database map to explore the contents of the tables in the database. In this way, the first view provided to the user provides a high level overview of the objects and the way they are related, while allowing the user to reveal the underlying data as they desire. This may provide another improvement over traditional ER diagrams that often show so much detail that it is hard to get a big-picture view.
According to embodiments, a list of data tables is retrieved from a database and ordered according to the number of relationships for each data table. A database map is generated containing tiles corresponding to each data table in the list, wherein tiles corresponding to related data tables are adjacent along at least one edge or corner of the tiles, where possible. The display attributes of each tile may be adjusted to reflect properties of the corresponding data table. The database map is then displayed to a user in a user interface, and controls are provided that allow the user to navigate and zoom the database map in the user interface in order to reveal data contained in the data tables corresponding to the displayed tiles.
It should be appreciated that the above-described subject matter may be implemented as a computer-controlled apparatus, a computer process, a computing system, or as an article of manufacture such as a computer-readable medium. These and various other features will be apparent from a reading of the following Detailed Description and a review of the associated drawings.
This Summary is provided to introduce a selection of concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended that this Summary be used to limit the scope of the claimed subject matter. Furthermore, the claimed subject matter is not limited to implementations that solve any or all disadvantages noted in any part of this disclosure.
The following detailed description is directed to technologies for generating and manipulating visual maps of relational databases. While the subject matter described herein is presented in the general context of program modules that execute in conjunction with the execution of an operating system and application programs on a computer system, those skilled in the art will recognize that other implementations may be performed in combination with other types of program modules. Generally, program modules include routines, programs, components, data structures, and other types of structures that perform particular tasks or implement particular abstract data types. Moreover, those skilled in the art will appreciate that the subject matter described herein may be practiced with other computer system configurations, including hand-held devices, multiprocessor systems, microprocessor-based or programmable consumer electronics, minicomputers, mainframe computers, and the like.
In the following detailed description, references are made to the accompanying drawings that form a part hereof and that show, by way of illustration, specific embodiments or examples. In the accompanying drawings, like numerals represent like elements through the several figures.
According to embodiments, a database visualization module 110 executes on the computer system 102 that generates visual database maps from relational databases provides services to user 104 to navigate the database maps. The database visualization module 110 may execute locally on a user computing device of the computer system 102, or may execute on a server computer, such as a Web server, accessed by a client application executing on a user computing device. The database visualization module 110 may be implemented as hardware, software, or a combination of the two. The database visualization module 110 may be an add-in or component of a relational database management system (“RDBMS”), such as the MICROSOFT® ACCESS® database software from Microsoft Corp. of Redmond, Wash., and may include a number of application program modules and other components on the computer system 102.
The database visualization module 110 accesses a database 112 in order to generate the database maps. The database 112 may be a relational database or other data storage mechanism known in the art and contains a number of data tables 114 for storing data. The database 112 may also contain metadata 116 defining the structure or “schema” of the database, such as relationships between the data tables 114. According to one embodiment, the database visualization module 110 utilizes the metadata 116 to generate database maps representing the data tables 114 in the database 112 and displays the database maps to the user 104 on the display device 106, as will be described in more detail herein.
Each tile 206 may be labeled with an identifier of the corresponding data table 114, such as the table name as shown in
According to further embodiments, the database visualization module 110 may provide facilities that allow the user 104 to manipulate the database map 204. For example, the database visualization module 110 may provide a pointer control 208 allowing the user 104 to select a particular tile 206A corresponding to a data table 114 in the database 112, by manipulating the point control with a mouse, for example. The database visualization module 110 may also provide a set of navigation controls 210 that allows the user 104 to pan and zoom the database map 204 in the window 202.
According to one embodiment, if the database map 204 is zoomed to a zoom level such that a particular tile, such as tile 206A, exceeds a threshold size, the label on the tile may be replaced with a data grid 212 or other display showing the records in the corresponding data table 114 from the database 112, as shown in
The database visualization module 110 may further provide a set of search controls 214 that allows the user 104 to search the database 112 for particular data. For example, the search controls 214 may allow a search string or keyword, such as “HEALTH,” to be entered. As the user 104 types characters in the search controls 214, the database visualization module 110 may search an index of words in the data tables 114 or the database 112. The database visualization module 110 may then highlight those tiles 206G-206H in the database map 204 corresponding to data tables 114 containing the search string or keyword, by changing the display attributes of the tiles, and or fading the those tiles 206 corresponding to data tables 114 that don't contain the searched-for data from view, as shown in
In one embodiment, when only one tile 206 remains highlighted in the database map 204, the user 104 may press a key on a keyboard to cause the database visualization module 110 to both center and zoom the database map 204 on the tile while showing the data grid 212 in the tile containing those records from the corresponding data table 114 including the searched-for data. It will be appreciated that other methods of searching the data in the database 112 known in the art may be implemented by the database visualization module 110 using the search controls 214.
Referring now to
The routine 300 begins at operation 302, where the database visualization module 110 retrieves a list of data tables 114 from the database 112 and orders the list by the number of relationships each table has, from highest to lowest. The database visualization module 110 may determine the number of relationships for each data table 114 from the metadata 116 in the database 112 defining the schema of the data tables. For example,
From operation 302, the routine 300 proceeds to operation 304, where the database visualization module 110 initially creates a square tile 206 in the database map 204 for the first data table 114 in the ordered list of tables. For example, the initial current layout 500 of the database map 204 may comprise the square tile 206A corresponding to table 3114C, as shown at Step 1 in
According to one embodiment, each layout in the list of possible new layouts 504 comprises an arrangement identical to the current layout 500 with one existing tile 206 split in two to create a new location for a new tile corresponding to the next data table 114 to be added. For example, as shown at Step 2 in
From operation 306, the routine 300 proceeds to operation 308, where the database visualization module 110 computes an “energy” for each layout in the list of possible new layouts 504 quantifying the ability of the layout to visually reflect the affinity of the data tables 114 in the database map 204. According to one embodiment, the energy of each layout represents the total number of pixels or other units along an edge of each tile 206 that touches another tile corresponding to a data table 114 to which the data table corresponding to the first tile is related in the database 112. Next, at operation 310, the database visualization module 110 selects the layout from the list of possible new layouts 504 having the highest computed energy as the new current layout 500 for the database map 204. For example, from the two possible new layouts shown at Step 2 in
The routine 300 proceeds from operation 310 to operation 312, where the database visualization module 110 determines if more data tables 114 exist in the ordered list of tables to be added to the database map 204. If more data tables 114 exist, the routine 300 returns to operation 306, where the database visualization module 110 generates a new list of possible new layouts 504 for the database map 204 containing a tile for the next data table, such as data table 1114A, in the ordered list of tables, as shown at Step 4 in
If at operation 312, the database visualization module 110 determines that no more data tables 114 exist in the ordered list of tables to be added to the database map 204, then the routine 300 ends. It will be appreciated that, by applying the space-dividing layout routine 300 described above to the database 112 depicted in the ER diagram 400 in
The routine 600 begins at operation 602, where the database visualization module 110 retrieves a list of data tables 114 from the database and orders the list by the number of relationships each table has, from highest to lowest. As will be seen below, this ordering of the data tables 114 may result in the generation of a database map 204 with those data tables 114 with the most relationships at or near the center of the map. As described above in regard to operation 302, the database visualization module 110 may determine the number of relationships for each data table 114 from the metadata 116 in the database 112 defining the schema of the data tables. Using the example database 112 depicted in the ER diagram 400 in
From operation 602, the routine 600 proceeds to operation 604, where the database visualization module 110 computes a relative size for each tile 206A-206E corresponding to each data table 114A-114E in the ordered list of tables. According to one embodiment, the database visualization module 110 computes the width of each tile 206 from the relative number of relationships for the corresponding data table 114, and the height of the tile from the number of records in the corresponding data table in relation to the number of records in other data tables in the list. In addition, the database visualization module 110 may ensure that no tile 206 is wider than it is high by increasing the height of the tile to be at least the width of the tile, as necessary. It will be appreciated that this will result in the tiles 206 in the database map 204 generated according to the edge-adhesion layout being either squares or upright rectangles. For example, from the data tables 114 in the ordered list of tables shown above in Table 1, the database visualization module 110 may generate corresponding tiles 206A-206E with the relative heights and widths shown at Step 1 in
In further embodiments, some other combination of number of relationships, number of records, number of unique keys, or other properties of the data tables 114 in the database 112 may be utilized by the database visualization module 110 in computing the relative width and/or height of each tile 206 for the database map 204. Next, at operation 606, the database visualization module 110 places the tile 206 corresponding to the first data table 114 in the ordered list of data tables in the center of the database map 204. For example, the initial current layout 500 of the database map 500 may comprises the tile 206A corresponding to data table 3114C, as shown at Step 2 in
The routine 600 proceeds from operation 606 to operation 608, where the database visualization module 110 generates a list of possible new layouts for the database map 204 containing the tile 206 corresponding to the next data table 114 in the ordered list of tables. In order to generate the list of possible new layouts, the database visualization module 110 may create a list of slots 702A-702H (referred to herein generally as slot 702) around the tiles in the current layout 500. A slot 702 may be created at each open edge and corner of the existing tiles 206 in the layout. For example, from the initial tile 206A placed in the database map 204, the database visualization module 110 may generate a list of eight slots 702A-702H, as shown at Step 2 in
It will be appreciated that the numbering of the available slots 702 around the existing tiles 206 in the current layouts 500 shown in
From operation 608, the routine 600 proceeds to operation 610, where the database visualization module 110 computes an energy for each layout in the list of possible new layouts. According to one embodiment, the energy of each layout represents the sum of the inverse of the distances between the center of the newly fitted tile 206 and those tiles corresponding to a data table 114 to which the data table corresponding to the new tile is related in the database 112. Next, at operation 612, the database visualization module 110 selects the layout from the list of possible new layouts having the highest computed energy as the new current layout 500 for the database map 204. For example, from the eight possible layouts corresponding to tile 206B in each of slots 702A-702H shown at Step 2 in
The routine 600 proceeds from operation 612 to operation 614, where the database visualization module 110 determines if more tiles 206 corresponding to data tables 114 in the ordered list of tables exist to be added to the database map 204. If more tiles 206 exist, the routine 600 returns to operation 606, where the database visualization module 110 creates a new list of slots around the existing tiles in the current layout 500, as shown at Step 3 in
If at operation 614, the database visualization module 110 determines that no more tiles 206 corresponding to data tables 114 in the ordered list of tables exist to be added to the database map 204, then the routine 600 ends. It will be appreciated that, by applying the edge-adhesion layout routine 600 described above to the database 112 depicted in the ER diagram 400 in
While the present disclosure describes the generation of the database map 204 using square or rectangular tiles 206, it will be appreciated that other polygons may be utilized by the database visualization module 110 for generating the tiles corresponding to each data table 114 depicted in the database map, including, but not limited to, triangles, hexagons, and octagons. In a further embodiment, the database visualization module 110 may place an amount of space between adjacent tiles 206 in the database map 204 such that the relationship, if any, between the data tables 114 corresponding to the adjacent tiles can be shown in the space, using conventional ER diagramming notation, for example.
The computer architecture shown in
The computer architecture further includes a system memory 808, including a random access memory (“RAM”) 814 and a read-only memory 816 (“ROM”), and a system bus 804 that couples the memory to the CPUs 802. A basic input/output system containing the basic routines that help to transfer information between elements within the computer 800, such as during startup, is stored in the ROM 816. The computer 800 also includes a mass storage device 810 for storing an operating system 122, application programs, and other program modules, which are described in greater detail herein.
The mass storage device 810 is connected to the CPUs 802 through a mass storage controller (not shown) connected to the bus 804. The mass storage device 810 provides non-volatile storage for the computer 800. The computer 800 may store information on the mass storage device 810 by transforming the physical state of the device to reflect the information being stored. The specific transformation of physical state may depend on various factors, in different implementations of this description. Examples of such factors may include, but are not limited to, the technology used to implement the mass storage device, whether the mass storage device is characterized as primary or secondary storage, and the like.
For example, the computer 800 may store information to the mass storage device 810 by issuing instructions to the mass storage controller to alter the magnetic characteristics of a particular location within a magnetic disk drive, the reflective or refractive characteristics of a particular location in an optical storage device, or the electrical characteristics of a particular capacitor, transistor, or other discrete component in a solid-state storage device. Other transformations of physical media are possible without departing from the scope and spirit of the present description. The computer 800 may further read information from the mass storage device 810 by detecting the physical states or characteristics of one or more particular locations within the mass storage device.
As mentioned briefly above, a number of program modules and data files may be stored in the mass storage device 810 and RAM 814 of the computer 800, including an operating system 818 suitable for controlling the operation of a computer. The mass storage device 810 and RAM 814 may also store one or more program modules. In particular, the mass storage device 810 and the RAM 814 may store the database visualization module 110, which was described in detail above in regard to
In addition to the mass storage device 810 described above, the computer 800 may have access to other computer-readable media to store and retrieve information, such as program modules, data structures, or other data. It should be appreciated by those skilled in the art that computer-readable media may be any available media that can be accessed by the computer 800, including computer-readable storage media and communications media. Communications media includes transitory signals. Computer-readable storage media includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for the storage of information, such as computer-readable instructions, data structures, program modules, or other data. For example, computer-readable storage media includes, but is not limited to, RAM, ROM, EPROM, EEPROM, flash memory or other solid state memory technology, CD-ROM, digital versatile disks (DVD), HD-DVD, BLU-RAY, or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store the desired information and that can be accessed by the computer 800.
The computer-readable storage medium may be encoded with computer-executable instructions that, when loaded into the computer 800, may transform the computer system from a general-purpose computing system into a special-purpose computer capable of implementing the embodiments described herein. The computer-executable instructions may be encoded on the computer-readable storage medium by altering the electrical, optical, magnetic, or other physical characteristics of particular locations within the media. These computer-executable instructions transform the computer 800 by specifying how the CPUs 802 transition between states, as described above. According to one embodiment, the computer 800 may have access to computer-readable storage media storing computer-executable instructions that, when executed by the computer, perform the routines 300 and 600 for generating database maps from relational databases, described above in regard to
According to various embodiments, the computer 800 may operate in a networked environment using logical connections to remote computing devices and computer systems through one or more networks 820, such as a LAN, a WAN, the Internet, or a network of any topology known in the art. The computer 800 may connect to the network 820 through a network interface unit 806 connected to the bus 804. It should be appreciated that the network interface unit 806 may also be utilized to connect to other types of networks and remote computer systems.
The computer 800 may also include an input/output controller 812 for receiving and processing input from a number of input devices 108, including a keyboard, a mouse, a touchpad, a touch screen, an electronic stylus, or other type of input device. Similarly, the input/output controller 812 may provide output to a display device 106, such as a computer monitor, a flat-panel display, a digital projector, a printer, a plotter, or other type of output device. It will be appreciated that the computer 800 may not include all of the components shown in
Based on the foregoing, it should be appreciated that technologies for generating and manipulating visual maps of relational databases are provided herein. Although the subject matter presented herein has been described in language specific to computer structural features, methodological acts, and computer-readable storage media, it is to be understood that the invention defined in the appended claims is not necessarily limited to the specific features, acts, or media described herein. Rather, the specific features, acts, and mediums are disclosed as example forms of implementing the claims.
The subject matter described above is provided by way of illustration only and should not be construed as limiting. Various modifications and changes may be made to the subject matter described herein without following the example embodiments and applications illustrated and described, and without departing from the true spirit and scope of the present invention, which is set forth in the following claims.
