Data Models

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

The Relational Model is a complete mathematics theory, with its own "pure" mathematic 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 ERP implementations.

Data Model

In theory there are 3 main types of data models - the Conceptual, the Logical and the Physical model.

We are not going to deal with the first two - all our Data Models will be Physical models.

This doesn't mean that we will NOT need to know how our RDBMs (HFSQL, SQL Server etc) actually implement the Data Models.

That's the beauty of the Relational Model - we don't have to care about the actual implementation.

So a Data Model is:

• all the sets of tables, relations and constraints at the database level or
• the WX Analysis or
• the actual Database

"Micro" Data Model vs Data Model

Micro Data Models are subsets of the Data Model, that handle specific areas or entities - like Products, Sales, Accounting etc .

The important thing about the "micro" Data Models is that they are complete and independent - this means you can take a "micro" Data Model and use it as a starting point for your own implementations.

From now on, we will use the term Data Model, for the "micro" Data Model and the Data Model

Entity

An Entity is any "abstract object" that we want to model and store information about, in the ERP.

We use the term abstract object instead of object, 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

Object vs Entity

An Object is a record or a set of records (in one or more tables), that "inherits the structure" of an Entity

Database Table

A Database Table is a collection of related data, held in a structured format within a database. It consists of columns and rows.

If you don't "understand" what a database table is take a look here: https://en.wikipedia.org/wiki/Table_(database)

Primary Key (PK)

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

Every row (or record) must have a PK, although sometimes it may look stupid to add one.

Relations, Tuples, Referential Integrity Constraints, Domain Constraints etc

The best way to describe them is by implementing them in a specific Data Model.

You probably already know or understand many of the above concepts - more on these later.

Foreign Key (FK)

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

You probably already know or understand the concept of a FK - more on this later.

Dimension vs Fact tables

The alpha360 ERP has 2 main types of tables - Dimension tables and Fact tables.

An example of a Dimension table is the gIndustry table - {check it in the analysis}

An example of a Fact table is the aAccountingTransaction table - {check it in the analysis}

Generally, Dimension tables "provide" information for Fact tables through a relation.

But - as you can see in the analysis - a Dimension table can "provide" information for another Dimension table also !!!

An example of the above is the gParty table and the gCountry table - both are Dimension tables. The gCountry table "gives" information (like nNumericCode) to the gParty table through their relation. On the contrary, the gCountry table does not get any kind of info from any table in the analysis. So there is more to Dimension vs Fact tables than we have stated above - but for now this will do.

Naming Conventions in the Analysis

remember the Analysis is the Data Model

Table Naming

Dimension tables, used in many functional areas of the ERP, start with the lower case letter "g" - like gParty, gCountry etc.

All other tables - of Dimension or Fact type - start with a lower case letter that "shows" the functional area in which they are used.

the table sCustomer is a table that belongs to the functional area SALES(s)

Field Naming

• String fields start with the lower case letter "s"
• Memo fields start with the lower case letter "m"
• number-Integer fields start with the lower case letter "n"
• number-Real fields start with the lower case letters "nr"
• number-Decimal fields start with the lower case letters "nd"
• number-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"
• Boolean fields start with the lower case letter "b"

Primary key and Foreign key Naming

Primary keys end with PK and Foreign keys end with FK.

There are a few exceptions to this rule - more on this later.

Transactions

A Transaction is a set of operations - additions, modifications and/or deletions of records in one or more tables; either ALL of these operations must be performed 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.

An important issue with Transactions is the Isolation Mode of your RDBMs. This is available in C/S RDBMs (HFSQL C/S, SQL Server etc) but not in Classic HFSQL.

You can find more info about this at: HFSQL C/S Isolation Mode