Menu
This is basically a repost of this question, which didn't give a satisfactory answer.
I'm finishing up the design of a database using an EER diagram. I now want to forward engineer and import the database into a working database. I will then use phpMyAdmin to start adding data and coding the site.
Now, while coding, what is the best way to make necessary changes to the database structure? Is the only way to export from phpmyadmin and import in MySQL Workbench? If so, how does it handle the existing rows when I add/remove columns? And, when it's time to forward engineer it again and import it via phpmyadmin, will all of my data still be there?
How do you handle this?
Community♦
timetoflytimetofly
migrated from stackoverflow.comJan 3 '13 at 12:41
This question came from our site for professional and enthusiast programmers.
2 Answers
This is an arguable opinion, from limited personal experience.
I believe it was the Workbench who screwed up the process of round trip engineering several times. I don't remember all the detail, but it certainly synchronized more objects than had actually changed. It silently deleted some data a few times. And it sometimes generated SQL that the server rejected. It was a development DB, no big deal, especially compared to the benefits. But in production I'd rather refrain from being at such a tool's mercy.
Some other people's experience was similar, e.g., MySQL Workbench model/source synchronize issue
So for a production DB there may be risks, including that of losing your data. I would stick with phpmyadmin and do synchronization back into the model, always checking what the Workbench is going to do. Keeping the model just for reference.
Community♦
full.stack.ex
MySQL Workbench uses two-way synchronization that allows to send any changes in your model to the server and at the same time to get any change from the server into your model. The dialog used for this process allows to take out individual changes or even revert the direction. Use the menu Database -> Synchronize Model.. for this task.
Mike LischkeMike Lischke
Not the answer you're looking for? Browse other questions tagged mysqlmysql-workbench or ask your own question.Steps for Create Database Mysql
Create Database in two ways
1) By executing a simple SQL query
2) By using forward engineering in MySQL Workbench
In this tutorial, you will learn-
As SQL beginner , let's look into the query method first.
Create Database
CREATE DATABASE is the SQL command for creating a database.
Imagine you need to create a database with name 'movies'. You can do it by executing following SQL command.
Note: you can also use the command CREATE SCHEMA instead of CREATE DATABASE
Now let's improve our SQL query adding more parameters and specifications.
IF NOT EXISTS
A single MySQL server could have multiple databases. If you are not the only one accessing the same MySQL server or if you have to deal with multiple databases there is a probability of attempting to create a new database with name of an existing database . IF NOT EXISTS let you to instruct MySQL server to check the existence of a database with a similar name prior to creating database.
When IF NOT EXISTS is used database is created only if given name does not conflict with an existing database's name. Without the use of IF NOT EXISTS MySQL throws an error.
Collation and Character Set
Collation is set of rulesused in comparison. Many people use MySQL to store data other than English. Data is stored in MySQL using a specific character set. The character set can be defined at different levels viz, server , database , table and columns.
You need to select the rules of collation which in turn depend on the character set chosen.
For instance, the Latine1 character set uses the
latin1_swedish_ci collation which is the Swedish case insensitive order.
The best practice while using local languages like Arabic , Chinese etc is to select Unicode (utf-8) character set which has several collations or just stick to default collation utf8-general-ci.
You can find the list of all collations and character sets here here
You can see list of existing databases by running following SQL command.
Creating Tables MySQL
Tables can be created using CREATE TABLE statement and it actually has the following syntax.
HERE
Now let's see what the MySQL's data types are. You can use any of them depending on your need. You should always try to not to underestimate or overestimate potential range of data when creating a database.
DATA TYPES
Data types define the nature of the data that can be stored in a particular column of a table
MySQL has 3 main categories of data types namely
Numeric Data typesNumeric data types are used to store numeric values. It is very important to make sure range of your data is between lower and upper boundaries of numeric data types.
Text Data TypesAs data type category name implies these are used to store text values. Always make sure you length of your textual data do not exceed maximum lengths.
Date / Time
Now let's see a sample SQL query for creating a table which has data of all data types. Study it and identify how each data type is defined.
Best practices
MySQL workbench ER diagram forward engineering
MySQL workbench has utilities that support forward engineering. Forward engineering is a technical term is to describe the process of translating a logical model into a physical implement automatically.
Eset internet security 11.2.49.0 key. We created an ER diagram on our ER modeling tutorial. We will now use that ER model to generate the SQL scripts that will create our database.
Creating the MyFlix database from the MyFlix ER model
1. Open the ER model of MyFlix database that you created in earlier tutorial.
2. Click on the database menu. Select forward engineer
3. The next window, allows you to connect to an instance of MySQL server. Click on the stored connection drop down list and select local host. Click Execute
4. Select the options shown below in the wizard that appears. Click next
5. The next screen shows the summary of objects in our EER diagram. Our MyFlix DB has 5 tables. Keep the selections default and click Next.
6. The window shown below appears. This window allows you to preview the SQL script to create our database. We can save the scripts to a *.sql' file or copy the scripts to the clipboard. Click on next button
7. The window shown below appears after successfully creating the database on the selected MySQL server instance.
Summary
The Database along with Dummy Data is attached. We will be using this DB for all our further tutorials. Simple import the DB in MySQL Workbench to get started
Click Here To Download MyFlixDB
To reverse engineer a database in MySQL Workbench:
The EER diagram is now displayed on the screen.
Below are screenshots for the above steps.
Start
Select Database > Reverse Engineer from the top menu of MySQL Workbench. You don’t need to be connected to any MySQL instance at this stage – the wizard will ask you to connect at the next step.
Selecting “Reverse Engineer” from the “Database” menu starts the process.
Set Parameters
Set/review parameters for connecting to the DBMS then click Continue:
Here you enter the details of the DBMS that contains the database that you wish to reverse engineer.
Enter Password
Enter password if required, then click OK. This is the password required to make the connection. It will typically be the password you use when connecting to the instance via MySQL Workbench:
Connecting
Workbench will connect to the DBMS, fetch a list of databases, and check for any issues. Click Continue:
The “Connect to DBMS and Fetch Information” screen. Once it has connected to the DBMS specified in the previous step, it will display this screen.
Select Schema/s
Select the database/s you would like to reverse engineer, then click Continue:
The “Select Schemas” screen prompts you to select the schema/s you’d like to engineer. You can reverse engineer any of the schemas listed.
Retrieving Objects
MySQL Workbench will retrieve all objects from the selected schema/s and check the results. Click Continue:
The “Retrieve and Reverse Engineer Schema Objects” screen simply informs you that it has in fact, retrieved the objects from the database/s.
Mysql Workbench Create Database DiagramSelect Objects
Select the database objects you’d like to have reverse engineered, then click Execute:
The “Select Objects to Reverse Engineer” screen allows you to select the objects yo’d like to reverse engineer. The database in this example is simple and only contains 3 tables. If the database contained more objects, they’d be listed here.
Download PCDJ DEX 3 Crack Full Version with a single click below. This DJ program for windows systems gives you full control over your mixes. PCDJ DEX 3.7.5 Final Free Download supports dozens of multimedia formats including MP3, OGG, AVI, MOV, MKV, FLAC, M4A in addition to many more.PCDJ DEX allows you to mix, cut, combine, add effects and also edit tracks with ease. Install the software in your windows system. Pcdj dex 3.7.5 full version.
Generating
MySQL Workbench will now reverse engineer all selected objects and generate the EER diagram (behind the scenes). Click Continue:
Mysql Workbench Tutorial
The “Reverse Engineering Progress” screen simply informs you that the wizard is performing the reverse engineering process and generating the EER diagram (behind the scenes).
Summary
A summary is displayed. Click Close:
This is the final summary of the process. It outlines everything that was reverse engineered.
The Result
The EER diagram is now displayed on the screen. Here’s an example:
This EER diagram was generated by reverse engineering a database and its objects.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |