Cara menggunakan mysql database schema generator

A database model is a type of data model that determines the logical structure of a database and fundamentally determines in which manner data can be stored, organized and manipulated. There are many kinds of data models, but the most popular type is the relational model, which uses a table-based format.

Usually, the data warehousing staff of a business will design one or more types of data models in order to most effectively normalize the tables and pan how to most efficiently store and retrieve business data. Another advantage of doing this exercise upfront is that many professional tools like Navicat can utilize the models as plans and build the database according to their specifications.

That being said, it is an unfortunate fact that all-too-often, data models get misplaced or deleted over time. In that event, DBAs have no recourse but to either redraft the models from scratch or, if they're in the know, let their Database Management Tool create models for them based on the existing database.

In today's tip, we'll learn how to create a model from a variety of database objects in Navicat Premium.

The process of extracting design information from a software product is known as “reverse engineering”. In Navicat, you can reverse engineer a database/schema, tables or views to a physical model.

To reverse engineer a database schema, right-click it in the Navigation Pane and choose Reverse Schema to Model… from the popup menu:

Navicat will then generate a physical model from the selected schema and open it in a new Model window:

You can then work with the new model just as you would one that you created from scratch. For example, you can add relationships, move objects around, and save the model.

Individual tables or views may be reverse engineered into physical models as well by right-clicking them in the Navigation Pane and selecting Reverse Tables to Model… from the popup list:

That will open the selected table in a new Model window:

It is also possible to select more than one table or view by selecting them in the Objects pane:

Right-clicking anywhere within the selection and choosing Reverse Tables to Model… from the popup list will now open those tables/views in a new Model window:

Navicat also supports the importing of databases, schema, tables or views from the Model window. A step-by-step wizard is provided to guide you through the import process.

• Begin by opening a new Model window, either by:
• Clicking the Model button on the main toolbar followed by the New Model button on the Objects toolbar:


OR

• Selecting File > New > Model… from the main menu:

• Enter the Database Vendor and Version number in the New Model dialog and click OK to open a new Model window for that product:

• Select File -> Import from Database from the Model window menu:

• On the Import from Database dialog, select a Connection.
• Choose the databases, schemas, tables or views you want to import:

• Click Start to create the model from the selected objects.

Should the need ever arise to reverse engineer database objects into a model, Navicat has you covered. Available in Navicat Premium and Enterprise Editions, the Reverse Engineering feature takes the challenge out of physical model creation from databases, schema, tables or views.

The Schema Designer (introduced in SQLyog version 6.1) is a visual interface where tables and table structures can be defined, displayed and manipulated. The Schema Designer provides a convenient graphical way to perform common operations as an alternative to the menu-based way of operation that was always supported. Also, the Schema Designer provides a graphical overview of (complete or only parts of) your databases.

To manage the databases using Schema Designer, goto the ‘File’ menu and select the click option “New Schema Designer”,

This option can also be selected by clicking on the "+" icon in the query editor line to the right of all open tabs and select the option “New Schema Designer” or just use the keyboard shortcut ‘Ctrl+Alt+D’,

The Schema Designer implementation has two parts:

1) A Tab type displayed in the Query Editor area of the program window. This Tab contains a single element only: a 'canvas' (drawing area). You can configure SQLyog to display one such TAB when the program starts in program's 'Preferences'. If you need more such TABs you can  open more from the 'file' menu any time.

2) An XML-based data file format for storing information generated from the Schema Designer.

The Schema Designer Tab

The canvas of the Schema Designer is very similar to the canvas of the QueryBuilder and their operation is almost identical. You can move existing tables from the Object Browser into the canvas by double-clicking it or by drag'n'drop and you can move the tables around and resize the table symbols on the canvas.

For the rest of this paragraph we will refer to a Schema Designer Tab with table(s) (and what else information is supported) on it as a 'LAYOUT'.

Table operations

When a table is added to the canvas, the table symbol will include a toolbar displaying small icons that let you perform the operations:

•  Alter Table
•  Manage Indexes
•  Manage Foreign Keys
•  Change the table view to display only the column name or both the column name and data type

The same operations are available from the context menu for each table. Actually this context menu does the same as the 'table' entry in the program main menu and the context menu for a table in the Object Browser. The graphical and the menu-based way of doing things are functionally identical.

Also a context menu is available on the canvas itself for where you can add existing tables to the canvas or create a new table for the selected database. To create a table, select 'CREATE TABLE' from the canvas context menu. You will see that the CREATE TABLE dialogue will open. Just enter the parameters describing the table definition and close the CREATE TABLE dialogue as you normally would. The table will be created in the database, the Object Browser updated and a table symbol will be added on the canvas.

Also you can any time 'Refresh' the canvas from the context menu.

Concepts

You have already now seen two concepts in work that are basic design concepts of the SQLyog Schema Designer:

1) Whatever you add or change from the Schema Designer will be stored to the database exactly the same way as when you work with the menu's of the program. There is no need to 'upload', 'apply', 'synchronize' the LAYOUT or anything like that. SQLyog Schema Designer is a graphical implementation of direct client functionalities. We work with the database that we are connected to - not with any kind of 'model' internal for the program

(but note that if you want a 'model' before applying the changes to the 'real' or 'live' database you can simply use another database as a 'model' - and use Schema Synchronisation to synchronise from the 'model database' to the 'real database'. The two databases need not be on the same server).

2) Whenever an operation will need to open another dialog to get additional information from the user, you will see that the Schema Designer uses the same dialog as the rest of the program.

Zooming the canvas can be done using the dropdown or CTRL+mouse scroll button. Canvas can be saved as .bmp graphics and printed. There is an option to see how the print will split into more pages (using the settings of the current default printer). These options are in the canvas context menu.

Defining Foreign Keys ('relationships')

The image above shows a canvas with tables added. You will also see that some lines are drawn between some of the tables. In the Schema Designer such line defines a FOREIGN KEY between two TABLES in the database (whereas in the Query Builder it defines a JOIN-condition for a query RESULT). When drawing a line from one table to another the common 'MANAGE FOREIGN KEY' tab will open and 'propose' you to create the FK where the 'child' column is the column from where you draw. The 'parent' column will be proposed according to the rules:

    * If there is a Primary Key on the parent table it will be proposed.

    * If there is no Primary Key on the parent table it will propose the first Unique Key found (if any) in table definition.

That procedure would find the 'parent' column of the Foreign Key that would make sense in most situations. If you need something more special then just change in the 'MANAGE FOREIGN KEY' tab.

When a line is drawn in the canvas "1" and "infinity" symbols will display at either end of the line. This symbol indicates what kind of index exists on the columns: "1" indicates a PRIMARY KEY or a UNIQUE index - "infinity" symbol indicates a non-UNIQUE index. Also the line has a context menu from where you can drop and edit the Foreign Key.

Langkah langkah membuat database dengan MySQL?

MySQL menggunakan model database apa?

Langkah awal membuat database MySQL?

Apa itu schema dalam basis data?