Design the data model

A data model is a complete explanation of the database structure. Storing structured data is the whole point of a database, so this is the most important step to get right. Although the term "model" makes it sound preliminary, your data model becomes your real database structure.

Here are a few web tutorials on data modeling generally (not related to Axisbase). Further down on this page is a short introduction to data modeling, with information specific to Axisbase.

Terminology

A Record type is a thing or class, like "person", "customer", "sale", or "product". This is the basic structural element. In SQL, this is called a table, but the term "table" can be misleading because it suggests a way of presenting data, which it is not.
A Record is one instance of a record type. For example, "Sarah" and "Bob" are records of the type "person". All your data are stored as records of some type.
A Property is a detail of a record type. For example, properties of a "person" include "name", "date of birth", and "height". Properties of a "sale" include "date of sale", "total amount", and "customer".
A Property type (or data type) defines the storage available for each property. The basic property types include strings (same as text), numbers of different kinds, and dates. When you define a property (such as "date of birth") using the date property type, you are making the decision that no one can ever enter anything else besides dates for that property. (Databases derive their power from limitations like this.)
A Reference is a property type of one record type that refers to another record type. For example, if you have record types "person" and "sale", one of the properties of "sale" would be "customer". The property type of "customer" would be "reference to person".
A value or field value is what is entered for one property for one record, such as "Sarah" (an entry for the "name" property), "1/4/2001" (a date entry), or "92" (a numeric entry). Some fields are restricted to certain options; for example, "M" and "F" would be the only available options for "sex".

Mental exercise

If this is new and confusing, don't worry. Getting these terms straight is the single most complicated thing about creating databases. So be patient and take the time to focus when you have no distractions.

If you don't have it all straight, this might help. Try to arrange the following words, listed alphabetically, into a suitable collection of record types, with properties.

driver
drivers license number
Isuzu
license plate number
model
name
owner
truck
Volvo

Answer to mental exercise

There are two record types: "driver" and "truck".

A "driver" has the properties: "name" and "drivers license number".

A "truck" has the properties: "model", "owner", and "license plate number". The property "owner" is a reference to the record type "driver".

The words "Isuzu" and "Volvo" are not types or properties at all - they are example field values that would be appropriate for the "model" property.

Notice that the "owner" of the truck is shown as an arrow pointing to the whole driver, not one of the properies of the driver.

Data types

The available data types are named according to technical names in the Microsoft .NET platform. If you are a programmer, this makes things easy; if not, you get to learn something new today.

string: Any text; you choose the minimum and maximum length. (Axisbase works faster with shorter strings, but they can be as long as 16,000 characters.)
integer types: There are several numeric types, depending on the range you need. Integers are whole numbers with no fractions.
- uint8: Range 0 to 255. (The name uint8 stands for an unsigned integer occupying 8 bits of storage space.)
- int16: Range -32768 to +32767. (which is a signed integer occupying 16 bits of storage space.)
- int32: Range +/- about 2 billion
- int64: Range +/- 1019. (That's more than the number of grains of sand in the Sahara Desert, so you won't need this type very often.)
floating point types: These are numeric types with decimal places. If you don't need to store fractions, use integer types because they are faster, and use less space.
- double: Doubles have 15 digits of precision, and are recommended for scientific applications. They occupy 64 bits of storage.
- decimal: Decimals have 28 digits of precision, and are recommended for financial applications. They occupy 128 bits of storage.
datetime: Stores a moment in time. Datetimes are stored in universal time (Greenwich mean time) and displayed in the user's local time zone, so if you have users in multiple time zones, they will see different times. If you don't want this, you can use a string instead. Datetimes can never be blank; they default to the current time.
date: Stores a date (Unlike datetime, dates are unaffected by time zones, and cannot store a time component.) Dates can be blank.

bool: Short for "boolean" which means "true or false". Bool properties are normally displayed as a checkbox.
blob: Short for binary large object, which means it can store an image, sound, or the contents of any file. You choose the minimum and maximum length, just as with strings. Without programming, blobs can only be used for images.

If you've used other databases, you might be wondering about nulls, which are a special value that many systems use to designate an unknown or blank value. Axisbase does not use nulls. That choice was made to prevent confusion between "null" and "empty", a distinction that easily leads to errors. The exceptions to this are that dates and references can be left blank.

Display types

Axisbase provides display types as a way to refine the meaning of the data types. Most display types are used with the string data type.

For example, if you define a property called "order status" you may want to restrict the values that can be entered to such things as "shipped", "hold", "backordered", and so on. Creating that restriction would help keep your orders organized. To do that,

Create a display type and call it "statusoptions", and enter the various options (shipped, hold, etc) into the display type definition
Attach the "order status" property to the "statusoptions" dispplay type. (Details on how to do this in Axisbase are given below.)

There are several kinds of display types:

List of options: Allows a pre-defined list of field values, and forces the user to choose one of them. An example usage would be the order status explained above.
List of suggestions: Allows a pre-defined list of field values, but also allows the user to enter something else. This is useful to encourage consistent entries and avoid spelling mistakes. An example property would be a "name prefix" field, and the suggested entries would be "Mr", "Ms", "Dr", etc.
Encoded list: Allows the user to select from a list of explanatory names, but it stores a short code value in the database. An example usage would be a payment method, where "C" would be stored for "cash", "X" for "check", and so on. This is similar to a list of options, but with some important differences. First, the speed is better when using strings with a short maximum length. Second, the explanatory wording could be changed and it would change the way all existing data are displayed. Third, if you want to see the code in order to save space on reports, you can easily override the display type in the report. And fourth, encoded lists tend to lock you in to early choices, and are harder to manage if you change your mind about how to use properties.
Multiselect list: Allows the user to select one or more from a list, and combines all the selections on one line, separated by commas.
Checkbox: Forces a string property to display as a checkbox. Note that bool properties are checkboxes, so you would normally use bools when you want a checkbox.
Pattern: Allows you to define a specific entry format. Example uses would be for social security numbers, or some company defined code that is always, say, 2 uppercase letters followed by 3-5 digits. Patters are defined using regular expression syntax. [Insert web reference here]
Image: This is the only display type that is applied to the blob data type. When you define a blob property as an image, Axisbase allows the user to load and save image files to the database.
Counter: This applies a systemwide counter to a property, in cases where you want to assign a unique number to a record, such as a job number.

How do you create a data model?

One way to create a data model is to borrow a similar one and change it. [Some examples will be posted here.]

There are lots of professional tools for complex data modeling, but you can do it in a word processor just as well. For a very simple project you could skip typing it out and just enter it directly into Axisbase, but having it in a word processor has some advantages: It helps you organize your thoughts, and then after the project is live, you can use it for ongoing documentation and reference.

If you don't have the background and you are utterly bewildered at this point, but you still want to do the project yourself, consider outsourcing the work of developing the data model. As stated, it is the most critical thing to get right. If you have a professionally design data model, you are more likely to successfully carry the project forward from there than if you have mistakes in the data model.

Example date model

This data model stores responses to survey questions.

Note that the meaning of each property is explained in the notes column, unless the property name makes it obvious. Any use of display types is noted, as well as any restrictions on numeric range and the allowed length of string fields.

Record Type "surveyor" - a person who asks the survey questions. There are only a few of these records.

Prompt	Name	Type	Range	Notes
Initials	initials	string	1-5 chars
Comments	comments	string	0-1000 chars	Note any problems with this surveyor here

Record Type "session" - a record of a survey session and the person who answers a survey. (If the same person participates twice, there would be two records here for the person)

Prompt	Name	Type	Range	Notes
First Name	firstname	string	0-30 chars
Last Name	lastname	string	0-50 chars
Date of Birth	dob	datetime
Address	address	address
Surveyor	surveyor	reference to surveyor
Venue	venue	string	0-30 chars	use venues display type

Record Type "answer" - each answer in a survey session becomes one "answer" record.

Prompt	Name	Type	Range	Notes
Session	session	reference to session
Question number	qno	int16
Answer text	text	string	0-300 chars

This model represents the result of a set of choices about how you want to to business. For example, it indicates that if the same person takes a survey twice, the surveyor would enter a new session record the second time. Depending on the nature of the business, it might have been a better choice to add a new level - a record type "respondent" with properties firstname, lastname, dob, and address. The "session" type would still contain the properties surveyor and venue, and would also contain a new property, the reference to the respondent record. That would allow tracking sessions by respondent.

Another important choice this data model makes is that the company does not store the survey questions in the database at all. An alternate design would be to add record types "survey" and "question" (continaing a reference to the survey); and changing the "answer" type to contain a reference to the question being answered.

It all depends on how you want to do business.

Included record types

Axisbase allows you to re-use record types within other record types. A very common use of this is for mailing addresses. If you have record types for clients and vendors and employees separately, and they all have mailing addresses, you can save some work by defining the address type once and use it in all three places.

Example, you define "address" with the properties line1, line2, city, state, and zip. (The example is for US addresses.)

Properties for the "employee" type are: name, address, and department. The "address" is not a reference to an address record; it is rather an inclusion of all the address properties within the employee record type.

Axisbase shows this as "one address", as opposed to "reference to address".

When you use included types, and you change the included type, Axisbase makes the change consistently throughout all types. For example, if you change the "address" type to include a country property, then clients, vendors, and employees will all be changed to include that new property.

Enforcing business rules

Axisbase knows how to enforce some the rules that you type into your data model document. "Enforcing" rules means preventing users from entering records that don't adhere to the rules. It doesn't know how to enforce all rules, but there are ways to write code to enforce all the rules.

Going the extra mile up front to make sure that rules are enforced usually saves work later on. (A stitch in time saves nine.) For example, if you have a large database and discover that hundreds of customer records have blank names, you will probably wish that you had prevented those errors from being entered in the first place.

The term business rule usually refers to the meaning or accuracy of the data, such as the rule that an email address must contain the @ character. A data integrity rule usually refers to the structure of the database, such as the rule that a line item on a sale must refer to an existing product. Here, we will not distinguish between these.

Here is a list of different kinds of rules, and how you can enforce them.

Required entry: To make a string or blob entry required, set the minimum length to 1. It is not possible to blank out a numeric entry, so numbers are always required. To enforce entry of references, write business logic code. (see Complex rules, below)
References: Axisbase automatically makes sure that references are intact (this is called referential integrity). When you add or change a reference, it must refer to an existing record. When you delete a record, Axisbase will make sure that no other record is referring to it; if there are references, you won't be allowed to delete it.
Range: To enforce a numeric range, enter the range in the property definition form.
Length: To enforce string length, enter the minimum and maximum length in the property definition form
List of options: To enforce a list of options, use a Display type. However, this is a weak form of enforcement that only affects entry through the Axisbase client software. For stronger enforcement, write business logic code (See Complex rules)
Uniqueness: To make sure that only one record has a certain value, check the Unique checkbox on the property definition form. For example, if you assign customer codes and want to make sure that two customer records don't have the same code, this option will enforce that rule.
Complex rule: To enforce any other rule, write business logic code (specifics are not posted as of this writing because the feature is not available in the pre-release version.)

Under the hood

The technical information in this section clarifies how Axisbase does some things, which you may find useful when working out a data model. You don't need to know this information.

Performance of strings. Strings are stored in one of two ways. The fast storage method is only used for short strings, and those in which the minimum length is near or equal to the maximum length. For example, if you have a confirmation code property, and you know it will always be between 18 and 20 characters, then you should specify the minimum and maximum lengths accordingly, so that Axisbase uses the fast storage method. If you have a one-letter code, and you specify minimum length = 1 and maximum length = 1, then Axisbase will only use 16 bits for the code per record. If you leave the maximum length at the default of 100, then Axisbase will use the slower storage method, which uses a lot more space.
References. Each record has a system-assigned unique record number, or key. Keys are stored using the data type int32. A reference is therefore stored as if it was a property of type int32.
Displaying references. Axisbase looks up the display string for references at the time the reference is first displayed. If several rows in a list of records have the same reference key value, the display string is only looked up once. Still, looking up references can often take up the majority of the time it takes to display data.
Indexes are built automatically to speed up searching for references, and anytime the user searches using a filter. Indexes allow Axisbase to find records without having to scan the entire database. If you are used to defining indexes yourself in other database tools, there are some differences. Axisbase indexes are always built from one property; it does not support multi-property indexes. Axisbase indexes are built the first time a need for one is encountered, and they are deleted after a period of 14 days of non-use.