Creating your first database

tutorial available

Developing a data model
Create database
Database objects
Understanding and using views
Comparing data models

Developing a data model

A data model includes everything that is going to sit inside the database. If you are new to database development, it's worth taking a little time and effort to read up on the theory of database design. We recommend the database technology article; Database design and database normalization as a basic introduction to database model development.

Before you start you need to make a few rules and stick to them. For example, primary keys should always be a simple BIGINT internal generator ID, not influenced in any way by any actual data. Many developers use unique information fields as primary keys, such as a social security number or membership number. But what if the social security number system changes or the membership card is stolen and a new membership with the same member details needs to be created and the old one made invalid? You are bound to encounter problems if you rely on such information for your primary key. And compound primary keys (primary keys consisting of more than one field) will almost always lead to problems at some stage as the sequence of the fields concerned must be identical in all referenced tables, and compound keys will always slow performance.

Another consideration is how to structure your data. This is where basic information about database normalization comes in. If you store your customer address data in your customer table and your supplier address data in your supplier table, you may end up with double entries (a supplier can also be a customer, a single customer may have more than one address). So create an address table with relationships to the customer and supplier tables. Using views the end user sees his customer, customer number and address or supplier, supplier number and his address.

Always start at the highest level, make sure you have got your entities correct. Construct your main tables and relationships. More information about the various kind of data relationships can be referred to below (Relationships). Don't get bogged down by the details at this initial stage; attributes can be added later. Scope it first - how big is it going to be? How's is it all going to fit together?

And when you do get down to the details, don't start using your fantasy or trying to look too far into the future. Only store information that is real and existent.

Naming conventions

You need to develop a naming convention that enables you and others to find and identify keys, table fields, procedures, triggers etc. simply and quickly, using a simple but effective combination of table names, field names, keys and relationships.

Please name things simply and logically: call a spade a spade, not a "manual excavation device" or "portable digging implement"! Another decision to be made is whether to name things in the singular or plural. If you have a team developing the same database, you are bound to have conflicts here and maybe even duplicates (e.g. CUSTOMER and CUSTOMERS), if you don't make a decision before you start! As the singular form is shorter than the plural in most languages, this is recommended, i.e. CUSTOMER instead of CUSTOMERS, ORDERLINE instead of ORDERLINES etc. Please note that in the db1 database, ORDER had to be named ORDERS, because ORDER is a Firebird keyword. The table could still be named ORDER but would have to be defined in inverted commas, which could lead to other problems. So English-language developers need to be aware of Firebird keywords and avoid eventual conflicts.

Another tip is to avoid using $ in your database object names, as $ is always used in system object names. All Firebird and InterBase^Ž system objects begin with RDB$ and IBExpert system objects begin with IBE$.

Primary keys are easily recognizable if the field name has the prefix PK (alternatively: ID) followed by a reference to the table name. Foreign keys should logically then contain the prefix FK followed by the table name which they reference.

Relationships

You need to be able to uniquely identify each row in each table, so each table requires a primary key. Other tables referencing this should be given a foreign key.

In our sample database, db1, each product is assigned to a category. The category_id links the product table to the category table, alternatively FK_category would also be a suitable name for the column referencing the relationship to the category table. In fact, if a relationship exists between two tables, put it in - make sure the database knows about it. It will help you in the long run, and in this way you can improve integrity, for example, you can enforce every product to be assigned to a category. Please refer to the Keys chapter for a comprehensive guide to Firebird/InterBase^Ž keys. Further information regarding constraints generally can be found in the Constraints chapter.

There are various kinds of relationships between data, which need to be taken into consideration when defining the constraints:

1:1

Within your application you have relationships which are 1:1. Many people say that if you have a 1:1 relationship between two tables, then it should be put together and become one table. However this is not always the case, particularly when developing one application for different clients with different requirements. There are often good reasons for maintaining a core customer table that is distributed to all customers, and then a customer_x table that includes information for a specific client. It prevents tables becoming too wide and confusing.

Another reason for 1:1 tables may be that in the case of wide tables with huge amounts of data, searching for specific information just takes too long. For example most journalists search in a press agency database using keywords for anything relevant to a particular subject (e.g. concerning 9/11) or for all recent articles (e.g. everything new in the last two days). They initially wish to see a full list of relevant articles including the title, creation date and short description. At this stage they do not need to view the whole article and accompanying photos for each article which meet their search conditions. This information can be returned later, after they have selected the article that particularly interests them. To improve performance, the table is split into four separate tables (each with a 1:1 relationship), the initial key information table (now containing the information most intensively searched for) being now only 2% of the size of the original single table. The second table is used to store all other information, the third table stores the RTF articles themselves, and the fourth table the full-text search contents.

n:1

n >= 0 Each category may contain one or more products, it may have no products.
n > 0 Each category must contain at least one product.

As you can see n:1 relationships can be defined in accordance with your business logic and rules. The multiplicity is defined by yourself. You may need to define an n:1 relationship where n is > 0 but < 10. Maybe n can be <null>; when it is <not null> you are enforcing a relationship.

The demo database, db1, demonstrates a simple n:1 relationship whereby all products have one category, but one category can have many products or no products assigned to it.

n:m

A classic example can be seen in db1: one customer can purchase several products and a single product can be purchased by many customers. To make this happen you need to have some linking table in the middle. The db1 example shows the link from customer to orders; orders is linked to orderline and orderline to product. All these relationships are built up using primary and foreign keys, thus forming an n:m relationship between customers and products. It is also possible to specify what should happen to these related data sets should one of them be updated or deleted. For example if you delete a customer in the customer table that has no orders (and therefore no order lines or products related to him) there is no problem. If however you attempt to delete a customer that has already placed orders, an error message will appear, due to a violation of FOREIGN KEY constraint "FK_ORDERS_ID" on table "ORDERLINE". This is necessary to maintain the database's integrity. Update and delete rules can be defined on the Constraints page in IBExpert's Table Editor. Please refer to Constraints, Referential integrity and Table Editor/Constraints for details.

To ascertain which relationships a table has with other database objects, and which dependencies other database objects have on a certain table, view the object editor's Dependencies page.

Data modeling using IBExpert's Database Designer

A simple method to initially design and visualize a new database is the IBExpert Database Designer. You can quickly and easily define what goes where, where are your key relationships, etc. It can also be used to graphically document an existing database, providing a logical view of the database structure and is an extremely quick and simple method to create views. Databases can be created or updated based on amendments made in the Designer by generating and running a script (please refer to Generate Script). They can be saved to file, exported and printed.

Create database

First choose a location where the file of your datbase will be stored. It is likely that you will have to discuss this with the administrator of your computer/computer network, as you will need to ensure that there is sufficient room to store not just the database but also the data entered into it. Another consideration is the security of the database location; data are sensitive - in most situations you don't want to let just anyone see them.

You will also need to consider page size, because Firebird/InterBase^Ž stores all metadata and data in pages. These are loaded into the cache as and when Firebird/InterBase^Ž needs them. Therefore the size of individual records should be less than the page size. If a single record is stored in several pages, you get a large number of pages and decreasing speed of access to the database. It makes much more sense to have several records on one page. More information regarding page size can be found in the IBExpert chapter, Page size.

You can either use the command-line tool, isql, part of the Firebird package or the IBExpert SQL Editor to use DDL (Data Definition Language) to create your database manually. An easier option is to use the IBExpert Database menu item, Create Database.

When the database is created, a new file is created on the disk. This file is the storage of the data. Inside the storage auxiliary tables are created and filled in automatically. These tables are called metadata and are used by the database itself and contain auxiliary information.

Refer to the following subjects for further information:

Database objects

All database objects along with the how and when to use them are described in detail in the IBExpert documentation. Firebird/InterBase^Ž offer the following database objects:

The number of objects in a database is unlimited.

Understanding and using views

A view can be likened to a virtual table. It can be treated, in almost all respects, as if it were a table, using it as the basis for queries and even updates in some cases. It is possible to perform SELECT, PROJECT, JOIN and UNION operations on views as if they were tables. Only the view definition is stored in the database, it does not directly represent physically stored data.

Views simplify the visual display of of complex data. However when creating updatable views, a number of factors need to be taken into consideration.

Simple views displaying only one table can be updated as if they were a table. But complex views containing many tables can only update if the business logic has been well thought through and implemented with triggers. This is necessary for the database to understand and know how it is to react in certain situations. For example, a user alters a category from cartoon to animation in a data set. Should the database a) allow the user to do this, b) alter the category just for this data set or c) alter the category for all films assigned to the cartoon category? Indeterminate views will damage your data integrity. Before creating a view, you need to decide whether to allow access to the view directly by the user, whether the user is only able to view data, or whether you wish to allow data updates using triggers or stored procedures.

You can simplify the relationships between data and tables for the user by flattening key information for them into a single view. We can add security by allowing users, for example, to update a film title but not allow them to alter a film category, by creating triggers on the view.

A further security option is to create views leaving fields with sensitive information (PIN numbers, passwords, confidential medical details and such like) blank. For example, in a product table with the fields: ID, FIRSTNAME, LASTNAME, ACCOUNT_NO, PIN,ADDRESS, ZIP and TOWN etc, a view of the table could be created as follows:

 as
 select
   id,
   firstname,
   lastname,
   account_no,
   '',
   address, etc.

Without suitable triggers and constraints, it is possible to add data to the "blank" column, but it still cannot be seen in the view.

Another good reason for introducing views is for reasons of compatibility following data model improvements and the subsequent metadata alterations. For example, you need to split your product table up into two smaller tables, product_main and product_detail. All new triggers, procedures, exceptions etc. will be written based on these new table names and contents. However if you do not wish to update and alter all existing dependencies, you can simply create a view with the old table name and the old table structure. Universal triggers can be used to forward any data alterations made here onto the new tables.

Views can also be defined as stored SELECTs, for example:

 CREATE VIEW Vw_Product_Short(TITLE,TXT)
 AS
 Select p.title,c.txt
 from product p
 join category c on c.id=p.category_id

Views can be created using SQL in IBExpert's SQL Editor and then saved as a view using the Create View icon. Alternatively they can be created in IBExpert's View Editor.

Once created, they can be treated in SQL SELECTs exactly as if they were tables:

 select * from Vw_Product_Short

Further information can be found in the IBExpert documentation chapter, Updatable views and read-only views. For further information on IBExpert's View Editor, please refer to View Editor. To create a view in the SQL Editor, please refer to Create view or procedure from SELECT.

Comparing data models

IBExpert also offers you the possibility to compare the metadata of two different databases, and generate a script which alters the structure of the first database, making the structure the same as the second database.

A huge advantage of Firebird is that metadata can be manipulated and altered during runtime. Regardless of whether you are adding fields to tables or changing the basic structure, users can still work on the database data. Please note that there is a limitation of the number of metadata changes you may make to any single table, before having to perform a backup and restore (please refer to 253 changes of table <table_name> left).