You create a project in the desktop app without an account, design the demo store's customer (customer) and order (orders) tables in an ERD, and create them in a real database. It takes about 15 minutes, and all you need is the installed desktop app. To avoid running a separate database server, it uses SQLite, where a single file is the database.
Follow the SQLite example in the desktop app. To follow along in the web app or with another DBMS, enter that database's connection details in Create and edit a connection and do everything else the same way.
Before you start
- Install the desktop app (Install and update). When you first open it, the home screen appears without signing in (First run).
- Decide on a folder for the SQLite file. If you use another DBMS, use a practice database where you are allowed to create tables. The last step actually runs
CREATE TABLE. - The flow is easier to follow if you know that a change made in an ERD goes through two steps: saving and applying it to the database (ERDs and the real database).
Creating a local project
- Click the Create New Database Project card on the home screen. Result: The New Project dialog opens.
- Enter
Demo Storein the Project Name field (2). - Leave the Create Sample ERD (classicmodels) option (3) unselected and click the Create button (5). Result: An empty project with no connection is created as a local project that is stored only on your device, and the project screen opens (Sign-in and project types).
If you choose the sample ERD, you start with an ERD that already contains example tables. If you do not, an empty project is created. These steps build the tables yourself.
Creating a connection
To create an ERD's design in a database, you need a connection to that database. That is why you create the connection before the ERD.
- Click the Add connection button in the middle of the explorer on the left. Result: The connection dialog opens.
- Enter
shop-dbin the Connection name field and choose SQLite in the DBMS field (3). - Click the Connect to DB button. Result: The connection fields (5) appear.
- Enter the full path of the file to create in the Database File field (for example,
shop.dbin the folder you chose). To use an existing file, pick it with the Browse... button. Result: The JDBC URL field (6) follows along. - Click the Test Connection button (7). Result: The Connection succeeded. notification appears. If there is no file at the path you entered, the SQLite driver creates an empty database file at this point.
- Click the Save button (9). Result: The
shop-dbconnection and itsmainschema appear in the explorer. There are no tables yet.
What each field means, and the connection fields of other DBMSs, are covered in Create and edit a connection.
Creating an ERD
- Click the ERD button in the ERD and SQL list at the bottom left. Result: The Create ERD dialog opens.
- Enter
Order managementin the ERD Title field (1). - Leave the Connection field (2) at
shop-dband choosemainin the Schema field (3). - Click the OK button (4). Result: An empty ERD bound to the
mainschema ofshop-dbis created and its ERD tab opens. New tables you place in this ERD are designed as tables of themainschema (ERD and SQL list).
Drawing two tables
- Click the Add New Table button (3) in the toolbar, then click an empty spot on the canvas. Result: A
table1table appears. It is a virtual table that does not exist in the database yet, and it already has the primary key columnidfrom the connection's template. - Click the table to select it, and change the table name in its header to
customer. In the column grid, renameidtocustomer_id. - Add columns with the + button in the grid header to create
email(VARCHAR(200)) andname(VARCHAR(100)), and turn on their N/N cells. Result: The new columns are shown in green. - Repeat steps 1 to 3 to create the
orderstable. Rename its primary key toorder_id, and addstatus(VARCHAR(20)) andordered_at(TIMESTAMP). Result: The design of both tables exists only in the project, andshop.dbis unchanged.
What each cell means, default values and changing the column order are covered in Table node.
Drawing the relationship
An order belongs to one customer, so orders gets a foreign key that references customer. Draw relationships before you apply to the database for the first time. That way the foreign key goes into the CREATE TABLE statement itself. SQLite cannot add a foreign key to an existing table with ALTER TABLE, so if you draw it later, NeoSQL cannot generate the DDL and leaves only an explanatory comment.
- Click the Non-Identifying Relationship button (4) in the toolbar.
- Click the parent,
customer, then the child,orders. Result: The Add Foreign Key dialog opens. - Because
ordershas nocustomer_idcolumn, the New Column switch is on and the new column name iscustomer_id. Leave it as is and click the Add Foreign Key button. Result: Thecustomer_idcolumn and a foreign key are added to the design oforders, and a relationship line appears between the two tables. The relationship line is the foreign key.
Saving
- Click the Save button (2) in the info panel, or press
Ctrl+S(Cmd+Son macOS). Result: The ERD's layout and the design of both tables are saved to the project. The database has not changed yet, so the green color (1) stays.
Applying to the database
Applying from the info panel puts every table changed in the ERD into one dialog, and the referenced customer is created before orders. If you apply only orders from its own toolbar, the table it references does not exist yet, so the Referenced Table Missing dialog asks first.
- Click the Apply Changes to DB... button in the info panel. Result: The DDL Preview and Execution dialog opens.
- Check that the table list (3) contains
customerandorderswith theCREATEmark. - In the DDL preview (4), check the two
CREATE TABLEstatements and theFOREIGN KEYoforders. - Click the Execute DDL button (6) and confirm in the confirmation dialog. Result: The selected DDL runs against
shop.dband the two tables are actually created. NeoSQL reads the created tables back from the database and saves them as the design, so the green color disappears.
The dialog's options, and what to choose when an error occurs during execution, are covered in Apply changes to the database.
Checking the result
- In the explorer, click the refresh button of the
mainschema and expand the Tables group. Result: The explorer reads the list from the database again, andcustomerandorders, which you just created, are listed as tables in the real database. If the names are in the normal color rather than green, the database and the design are the same. - Expand
ordersand check that it has thecustomer_idcolumn. Double-click it to see the foreign key as well in the Table tab.
What's next
- Run your first query and edit the results: add data to the tables you just created with the SQL editor and query it.
- Document an existing database as an ERD: bring the tables of an existing database into an ERD and organize them.
- Use standard names from a data dictionary: create columns with standard names and types by entering logical names.
- ERD canvas: the canvas's other tools, such as comments, groups, auto layout and the Export Image... tool.