Designing a database

The best design for a database is determined by the type of data you need to capture, and the intended type of analysis.

Before you start to design an iBase database, it is essential to define the requirements for the database, for example:

  • What data is to be stored?

  • How is that data to be entered?

  • How is the data to be used?

  • Who will use the database?

An overview of the design process is given below.

Entities and links

The modeling and analysis facilities in i2 applications are based on the concepts of entities and links. Entities are real-world objects, the things that are being represented, such as vehicles, people, and addresses.

Links represent relationships between entities, such as owner, associate, and marital status. Entities and links are defined in iBase Designer as entity types and link types. The most significant part of defining the database requirements is to identify the best entity and link types for the data given the type of analysis that is required.

In iBase, each entity and link is represented by a database record.

Gathering database requirements

Consider how the data is entered into the database, as this might influence the database design. There are many ways of adding data to an iBase database.

It is important to consider all the different types of user to find out what tasks they are responsible for, and how they complete these tasks. For example, are the users who enter and check the data different from the users who analyze that data? Do all analysts perform the same types of analysis? Designers can make allowance for the different types of user by designing data sheets. A data sheet is a custom form that is tailored to suit the task performed by the user.

If the database is to hold confidential data, you might need to identify specific user types (defined in iBase as user groups) in order to determine what sort of controls are required to protect the data.

Designers need to know what questions the users want to ask of the data, and also what they expect to produce from the database. Users might need to produce:

  • Queries

  • Reports

  • Charts

  • Maps

  • Data for export

Knowing how the data is used allows the designer to adapt the schema of the database so that it is possible to import and export data to third-party applications.

Selecting entity and link types

After a designer gathers information about the data to be held in the database, its users, and the types of analysis they perform, the designer is ready to define the entity and link types to represent the data. It is important to understand that there is no right or wrong way to do this.

The best design is the one that allows all the relevant data to be entered as quickly as possible, while also allowing users to complete the tasks they specified during the consultation phase of the requirement gathering process.

Selecting field types

Before you define the entity and link types, the designer needs to examine the format of the data in detail, and decide which field types are most suitable. It is a good idea to get this right before data is entered into the database as there are some limitations on changing between field types.

The total number of fields that you can define for an entity or link is 244. System-generated fields, such as Creation Date, account for some of the total. This limits you to a maximum of around 235 fields.

There is a wide range of field types, covering:

  • Free text (text typed by the user in whatever format they choose)

  • Fixed text (that is selected from the lists of various types)

  • Numbers and currencies

  • Dates and times

  • Documents on your network, on websites and intranets

  • Documents and pictures for inclusion in the database

  • User information (contact details)

  • Fields for use on charts (such as icons)

  • Geographical coordinates

  • Security information (such as Security Classification codes and cases)