Creating Relationships in SQLDBM

Creating Database Relationships in SQLDBM

 Introduction

The identifying feature of relational databases such as SQL Server, Oracle, Postgres and MySQL, is relationships. Relationships are the one feature which more than any other, gives relational databases so much flexibility and power in accurately modeling real-world business and organizational setups.

For instance, your company is in the business of receiving various PRODUCTS from a number of SUPPLIERS, then selling and shipping the same to CUSTOMERS in batches called ORDERS, each of which consists of one or more ORDER ITEMS. Each of these entities can be represented as a separate table in our relational database, with table names such as ORDERS, CUSTOMERS, SUPPLIERS, ORDER_ITEMS, and so on. And just as in real life, these entities are related to each other – an item shipped to a customer is part of an order; an order must be delivered to a specific customer, a product is received from a specific supplier, and so on. All these are examples of relationships that can be modeled in relational databases.

 

SQLDBM and Database Relationships

So how do you set up relationships in Sqldbm? First of all, here at Sqldbm we believe in following best practice. And best practice dictates that relationships are best defined at the table level. Or in other words, you as the database developer should ideally define relationships when creating your tables, rather than doing it later at the column level. For instance, when creating an ORDERS table to define your customer orders, that is also the best time to also reflect the real-world relationship between an order and the customer to whom the order is to be delivered.

And here is where Sqldbm does the heavy lifting for you. You do not need to write SQL code to define relationships between say, your ORDERS and your CUSTOMERS tables. Instead you simply create your tables – CUSTOMERS and ORDERS. No need to define any columns yet. Then select the ‘Relationships’ (Identifying or Non-Identifying depending on your need) menu item from the Sqldbm feature list (see this highlighted in screenshot Figure1 below). Once you click the Relationships menu item, you will be prompted to first click on the parent table. At this point click on the CUSTOMERS table. Then you will be prompted to select the child table by clicking on it, which you will do by clicking on the ORDERS table. The relationship is now defined between the two tables, and shown in the Diagram view as a solid line linking the two tables.

 

creating database relationship using sqldbm sql designer
Figure 1

 

Later, when you get to create specific columns, you can create a Customer_id column within the CUSTOMERS table, and define it as a primary key. Sqldbm will again ease your life by automatically creating a corresponding column of the same name in the child table ORDERS. The ‘automatic’ part is because you had previously created a relationship between the two tables, so Sqldbm knows that because a relationship is already defined between the two tables, the primary-key column(s) in the parent MUST necessarily be reflected as a foreign-key in the child table. Relationships are defined at the table level and implemented at the column level, and Sqldbm gives you the perfect way to do this.

Viewing and Editing Relationships

Once you have created a relationship in Sqldbm, you can view a lot of interesting information about it by clicking on the line that represents it in the Diagram view. The extra information is shown in the Relationship Properties section on the far-right of your Sqldbm window (see sample screenshot in Figure2 below).

 

editing database relationship using sqldbm sql designer
Figure 2

You can also edit your relationship, again in the Relationship Properties section. You can do the following by ticking or unticking the relevant checkboxes for each feature (See Figure 3):

 

database relationship properties at sqldbm sql designer
Figure 3

 

  • Change the relationship to an identifying relationship (or change it to a non-identifying relationship by deselecting the checkbox).

 

  • Generate an index for the foreign-key index column in the relationship. If you do so, a name is also automatically created for the new index.

 

  • Select to make the foreign-key index a constraint.

 

  • Alter the rolename. A rolename in this case is the name given to the column name in the child table. If you remember, the participating column in the child table was automatically created and given the same name as the column in the parent table. You can choose to change that column name here. You can see an example of how this is done in the sample screenshot in Figure2 below. In this case, the parent table is CUSTOMER and its relationship column is CustomerId. The child table is ORDER, and its relationship column was also originally named CustomerId. But it has been changed here to Customer_id (with an underscore in the name) in order to differentiate it for the sake of explanation. This change is done in the Rolename subsection of the Relationship Properties