Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 10

Displaying posts with tag: Database design (reset)

Using MySQL Workbench
+1 Vote Up -0Vote Down

I’ve been setting up a simplified lab environment to let my students learn use in class. This added content will show them how to do reverse engineering with MySQL Workbench.

It’s a complete Fedora image with MySQL and Oracle Database 11g for the course. The uncompressed image is 14GB and the compressed image is 5.3GB. I chose Fedora because it’s the smallest open source image that supports both environments, and Fedora is the closest to Red Hat and Oracle Unbreakable Linux. I’m inclined to make the instance available generally but haven’t figured out the best way to do that.

Here are the new instructions I’m adding and if you have any input leave it as a comment.

You connect as the student user, which puts you in the

  [Read more...]
Capture MySQL Foreign Keys
+0 Vote Up -0Vote Down

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
  [Read more...]
Add User Defined Types
+0 Vote Up -0Vote Down

Somebody asked me if there was a cheaper alternative to using the Embarcadero Data Architect (a data modeling tool). I said sure, you can use the MySQL Workbench. My friend laughed and said, it’s to model Oracle databases and they use different data types. I broke the news to him that he can create his own user defined types and use MySQL Workbench to model problems for the Oracle Database 11g.

For example, you can launch the MySQL Workbench, and click on the Model menu option, and in the menu window click on the User Defined Types choice, as shown in the following:

Choosing the User Defined Type option, launches the following

  [Read more...]
Relationship Notations
+0 Vote Up -0Vote Down

One of my students asked how to convert MySQL Workbench’s default Crow’s Foot (IE) diagram to one of the other supported formats – Classic, Connect to Columns, UML, and IDEF1X. Crow’s Foot is also known as the Information Engineering Model method (covered in Chapter 3 of my MySQL Workbench: Data Modeling & Development.

It quite simple, you open the Model Overview window, click on the Model menu choice. In the dialog, click on the Relationship Notation menu option. Click on one of the choices in the nested menu, like Column to Columns.

  [Read more...]
MySQL Image Architecture
+0 Vote Up -0Vote Down

The LinkedIn MySQL DB Development group posed a questions on how to handle images. Naturally, the argument always goes: Should images be deployed in the database or the file system? I believe they should be stored in the database because the cost and time associated is too high with regard to managing files, a file naming schema, and backing up the file system discretely from the database.

Since there’s a significant difference between the backup of transactional data and image data, they should be placed in different databases. The imagedb database is where you would place the images and large text descriptions, as shown in the MySQL Workbench ERD:

The imagedb ERD

  [Read more...]
Why Stored Programs?
+0 Vote Up -0Vote Down

Why should you use stored programs? Great question, here’s my little insight into a situation that I heard about in a large organization.

A very large organization is having a technology argument. In someway, like politics, half-truth drives this type of discussion. This company has hundreds of databases and they’re about half SQL Server and Oracle. The argument (half-truth) states that using T-SQL or PL/SQL yields “spaghetti” code!

It seems like an old argument from my perspective. After all, I’ve been working with T-SQL and PL/SQL for a long time. Spaghetti code exists in every language when unskilled programmers solve problems but the point here is one of software architecture, and an attempt to malign stored programming in general. Let’s examine the merit of the argument against stored programs.

First of all, the argument against stored

  [Read more...]
MySQL Workbench – Next-generation visual database design application
+0 Vote Up -0Vote Down

Visual database design is very essential. Almost every web application needs database. And by designing visual representation, relation of tables makes an developer easy to understand on the database. There are many tools to design database like DBDesigner, SQLYog. Recently I found another application that is called MySQL Workbench. It can be used to efficiently design, manage and document database schemata. After using it’s functionality, I found this is a really good tool for design database specially for MySQL.

It is available as both, open source and commercial editions. The Community (OSS) Edition is available from this page under the free software/opensource GNU General Public License (commonly known as the “GPL”) (http://www.mysql.com/company/legal/licensing/opensource-license.html)

  [Read more...]

What is your favorite database design book?
+0 Vote Up -0Vote Down

My wife wants to learn database design. She is an archaeologist. She wants to read a book written in plain English, not h4×0r jargon. She is smart and capable and knows her own data, but does not know SQL or database theory. She wants to be able to design databases and be understood by others who know database design. She also wants to be able to explain her ideas to a programmer who will build the systems she’s designing. Is there a book for her?

Books, Database Design
Vendor Neutral Database Certification
+0 Vote Up -0Vote Down

First saw this off of Planet MySQL:

 New Vendor Neutral Database Certification (Dave's Stuff)

Following the link to the CIW site, there are six domains that are being tested:

  • Relational Database Fundamentals
  • Relational Database Design and Application
  • Normalization and Database Design
  • Structured Query Language (SQL)
  • Relational Algebra and Databases
  • Transactions, Concurrency Control and Security
  • I wonder how much traction this independent certification will get, given the flavors each of the various database platforms use of SQL


      [Read more...]
    Designing databases with MySQL Workbench
    Employee +0 Vote Up -0Vote Down

    A week ago, Mike Zinner and his team released the beta version of MySQL Workbench on http://dev.mysql.com/workbench/. MySQL Workbench is a visual database design tool that is developed by MySQL. It is the successor application of the DBDesigner4 project.

    There are two different editions of MySQL Workbench at this point in time - an open source edition and a standard edition that is only available for paying customers.

    This means that MySQL Workbench introduces a new concept for MySQL. Until now, MySQL products have either been open source or only available through a commercial subscription (such as MySQL Enterprise Monitor

      [Read more...]
    Showing entries 1 to 10 of 10

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.