It is really difficult to give a complete and simple definition of the terms, Data Models and Relational Model.

The Relational Model is a complete mathematics theory, with its own "pure" mathematics notation and definitions, and it is difficult for non-mathematicians to understand the complete theory.

So we will try to keep things simple and use the following list of simple - and probably incomplete - definitions that we will need in the Business applications. 


Database schema

The structure of the alpha360 databases - in WX it is the Analysis.


An Entity is a "type of Class" that we want to model and store relevant information in the Database.

We use the term "type of Class" instead of Class, to avoid confusion with the standard terminology in OOP.

In OOP terms, an Entity would be a Class and NOT an Object.


Entities can be:

Simple  - implemented by one database table

Complex - implemented by many tables and relations


* Note: sometimes - in the documentation - the above terms can be interpreted as structure definitions or as data -  their actual meaning depends on the context ..


Tables (or Files) contain actual data in the database and are build from data records/fields.

In the alpha360 documentation we use only the terms Entity, Table and Field - and their actual meaning depends on the context ..

Tables in the data models are divided into 4 categories:

  • Dimension tables - like Warehouse, VAT Categories, Colors and Payment terms,
  • Foundation tables - like Parties and Products,
  • Document tables - like Invoices, Orders and Shipments,
  • Transaction tables - like Inventory, Customer, Supplier and Accounting transactions,
  • State tables - not used in v3.


* Note that depending on your industry (or your implementation) Dimension tables and Foundation tables could be the same.

In a Business application, for Logistics, a Warehouse is a Foundation table, but in a simple Business application it is a Dimension table !!!


Foundation and Document tables are usually complex entities and Dimension and Transaction tables are usually simple tables.

​

A Primary Key (PK) is a field in a table which uniquely identifies each row (or record) in the table.

All PKs in the data models are INTEGERS - 8 byte auto integers - with a few exceptions.

Every row (or record) must have a PK, although sometimes it may seem "strange" to add one.

​

A Foreign Key (FK) is a field (or a collection of fields) in one table that uniquely identifies a row of another table. 

They are used to "connect or link" records from one table, with records of another "related" table. They are also INTEGERS.

​

A Transaction is a set of operations - inserts, updates and/or deletions of records in one or more tables.

If it "affects" many tables, either ALL of these operations must be performed successfully or NONE.

The classic example is a banking application where we transfer money from one account to another.

Here we actually have 2 operations:

  • Reduce the money in one account,
  • and Increase the money in another account.

To ensure integrity, both the operations - or the set of operations - must be performed, otherwise NONE should be performed.


Field types and naming:

String fields start with the lower case letter "s"

Memo fields start with the lower case letter "m"

Integer fields start with the lower case letter "n"

Real fields start with the lower case letters "nr"

- in v3 we use the currency type instead of the real type ...


Decimal fields start with the lower case letters "nd"

- in v3 we use the currency type instead of the decimal type ...


Currency fields start with the lower case letter "c"

Date fields start with the lower case letter "d"

DateTime fields start with the lower case letters "dt"

Time fields start with the lower case letters "d"

Boolean fields start with the lower case letter "b"


In alpha360 v3, every numeric field that has decimals - like a quantity or amount etc - is a currency type in the database schema.

Although this type of selection might sound "strange", currencies are really just decimal numbers, with very good rounding behavior.

​

RI Constraints are constraints, forced by links in the database, and in alpha360 they are of type [1,1]-[0,n]-> and [0,n]-[1,1]->


Virtual links are constraints, implemented by the programmer, in code.


An important issue with Transactions is the Isolation Mode of your implementation. 

Isolation mode available in most RDBMs  - HFSQL C/S, SQL Server etc - but not in Classic HFSQL.