Difference between revisions of "New Database Schema"
Jump to navigation
Jump to search
(Created page with 'We are proposing an update to the schema for storing a vistrail in a MySQL database. The motivation is: * The current schema requires multiple SQL commands to access and update.…') |
|||
| Line 1: | Line 1: | ||
We are proposing an update to the schema for storing a vistrail in a MySQL database. The motivation is: | We are proposing an update to the schema for storing a vistrail in a MySQL database. The motivation is: | ||
* The current schema requires multiple SQL commands to access and update. One SELECT query is required for each table we want to extract information from. This gets really slow when the server is far away from the client. | * The current schema requires multiple SQL commands to access and update a vistrail. One SELECT query is required for each table we want to extract information from. This gets really slow when the server is far away from the client. | ||
== Proposed Improvements == | == Proposed Improvements == | ||
# Store all information in a single table. | # Store all information in a single table. | ||
#: | #: Pro: One single command can be used to access and update a vistrail. | ||
#: Pro: We can still use a vanilla MySQL server. | |||
#: | #: Con: A lot of redundancy in this table since different objects need different kinds of fields such as dates and integers. | ||
#: | #: Con: One big table might lead to slow performance. | ||
#: Con: Difficult to implement. | |||
#: | |||
#: | |||
# Add an intermediate server application that can receive and perform multiple SQL commands in batches. | # Add an intermediate server application that can receive and perform multiple SQL commands in batches. | ||
#: | #: Pro: No changes to the schema necessary. | ||
#: Con: We have to install a new application on the server side that handles communication between VisTrails and the MySQL server. | |||
#: | |||
# Use a more object-focused database such as an XML database. | # Use a more object-focused database such as an XML database. | ||
#: | #: Pro: A single query can retrieve a complete vistrail. | ||
#: Con: We would need to install a new database such as [http://exist.sourceforge.net/ eXist] | |||
#: | #: Con: We would need to use a different query language such as XQuery | ||
#: | |||
# Other ideas? | # Other ideas? | ||
Revision as of 15:31, 18 February 2011
We are proposing an update to the schema for storing a vistrail in a MySQL database. The motivation is:
- The current schema requires multiple SQL commands to access and update a vistrail. One SELECT query is required for each table we want to extract information from. This gets really slow when the server is far away from the client.
Proposed Improvements
- Store all information in a single table.
- Pro: One single command can be used to access and update a vistrail.
- Pro: We can still use a vanilla MySQL server.
- Con: A lot of redundancy in this table since different objects need different kinds of fields such as dates and integers.
- Con: One big table might lead to slow performance.
- Con: Difficult to implement.
- Add an intermediate server application that can receive and perform multiple SQL commands in batches.
- Pro: No changes to the schema necessary.
- Con: We have to install a new application on the server side that handles communication between VisTrails and the MySQL server.
- Use a more object-focused database such as an XML database.
- Pro: A single query can retrieve a complete vistrail.
- Con: We would need to install a new database such as eXist
- Con: We would need to use a different query language such as XQuery
- Other ideas?