Please reload

 

alpha360 Express Data Model

The alpha360 Express Data Model is a subset of the alpha360 Universal Data Model, especially designed for the Retail Industry.

 It is a complete relational database model, delivered as a WX Analysis and it includes tables, constraints, relations and triggers and it supports HFSQL Classic and C/S databases.

 

Foundations

Βefore moving on, now is the right time, to introduce some general technical terms, used in the data models and the business applications.

The record structure of a table or the "record structure" of a view, is called an entity.

A Product, a Party and a VAT Category are all entities.

VAT Category is a simple entity, because it is represented by only one table in the data models.

On the contrary, a Party is a complex entity, because it is represented by many tables (or a view) in the data models.

Tables in the data models are divided into 4 categories:

  • Base tables - like Customers, Products and Contacts

  • Document tables - like Invoices, Orders and Shipments

  • Dimension tables - like VAT Categories, Colors and Payment terms

  • Transaction tables - like Inventory, AR, AP and Accounting transactions

Base and Document tables are usually complex entities and Dimension and Transaction tables are 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 GUIDs - universally unique unicode strings - with a few exceptions.

Every row (or record) must have a PK, although sometimes it may look stupid 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" records from one table, with records of another "related" table. They are also unicode strings.

A Transaction is a set of operations - inserts, updates 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.

 

Notation

Field 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"

  • Decimal fields start with the lower case letters "nd"

  • 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"

Primary keys end with _PK and Foreign keys end with _FK.

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

 

Party Entity

The Party entity is a complex entity, used to support customers, suppliers and employees in a unified way.

At the "heart" of the entity is the gParty table [not to be confused with the Party entity].

Every customer, supplier or employee has one [and only one] record in this table.

A customer has also a record in the sCustomer table, a supplier a record in the pSupplier table and an employee a record in the hEmployee table. All these tables create a "star type structure" and are connected by PKs and FKs.

  • gParty.sPartyPK is the PK of the gParty table.

  • sCustomer.sPartyFK is the FK of the relation between the gParty and sCustomer tables, but is also the PK of the sCustomer table.

  • pSupplier.sPartyFK is the FK of the relation between the gParty and pSupplier tables, but is also the PK of the pSupplier table.

  • hEmployee.sPartyFK is the FK of the relation between the gParty and hEmployee tables, but is also the PK of the hEmployee table.

Note: a Party Entity can be a Customer, a Supplier and an Employee at the same time.

"Around" the tables of this entity, are various Dimension tables [like gIndustry], connected by FKs.

You can find more information about the entity in the WX Analysis.

Product Entity (major changes in v2)

The Product entity is a complex entity, used to support Products and their variations in a unified way.

At the "heart" of the entity is the gProduct_Class table and the gProduct table [not to be confused with the Product entity].

Every product variation has one [and only one] record in the gProduct_Class table and one [and only one] record in the gProduct table.

To understand the above, think of a product like a shoe#abc.

It could come in many variations (colours, sizes etc) but all of the variations have the same price.

So a record would first be added for shoe#abc in the gProduct_Class table and many records - for each colour and size - would be added in the gProduct table.

These tables create a "one to many structure" connected by PKs and FKs.

  • gProduct_Class.sProduct_ClassPK is the PK of the gProduct_Class table.

  • gProduct.sProductPK is the PK of the gProduct table.

  • gProduct.sProduct_ClassPK is the FK of the relation between the gProduct_Class and gProduct tables.

Note: all inventory transactions use the gProduct table and its PK - think of the gProduct_Class table as just a "bucket" for common information, grouping and searching.

v1 vs v2

In v1 of the Data Models there was NO gProduct_Class table - this is new functionality in v2.

The table gProductClass, used in v1 for grouping fiunctionality, has been removed from v2.

 

"Around" the tables of this entity, are various Dimension tables [like gProductGroup], connected by FKs.

You can find more information about the entity in the WX Analysis.

 

Business Document Entity (major changes in v2)

The Business Document entity is probably the most unique and complex entity in the Data Models.

It is used to support all Sales and Purchase Documents - Quotes, Orders, Shipments, Invoices, Payments etc. in a unified way.

At the "heart" of the entity is the bBusinessDocument table [not to be confused with the Business Document entity].

Every REAL Business Document has one [and only one] record in the bBusinessDocument table and depending on the nature of the document, record(s) in the following tables:

  • Purchase Quotes: 1 record in the bPurchaseQuote table and one or more records in the bBusinessDocument_ProductLine tables [for the products]

  • Purchase Orders: 1 record in the bPurchaseOrder table and one or more records in the bBusinessDocument_ProductLine tables [for the products]

  • Purchase Shipments: 1 record in the bPurchaseShipment table and one or more records in the bBusinessDocument_ProductLine tables [for the products]

  • Purchase Invoices: 1 record in the bPurchase table and one or more records in the bBusinessDocument_ProductLine tables [for the products]

  • Purchase Payments: 1 record in the bPurchaseAP table and one or more records in the bBusinessDocument_PaymentLine tables [for the payments]

There can also be one or more records [if it makes sense] in the following tables:

  • bBusinessDocument_ExpenseLine tables [for the expenses]

  • bBusinessDocument_TAXLine tables [for the taxes]

These tables create a "star structure" connected by PKs and FKs.

  • bBusinessDocument.sBusinessDocumentPK is the PK of the bBusinessDocument table.

  • *.sBusinessDocumentFK are the FKs of the other connected tables.

  • Some of the above FKs are also used as PK in their table [like bPurchaseAP.sBusinessDocumentFK is the PK of the table bPurchaseAP].

v1 vs v2

In v1 of the Data Models there where NO separate Quote, Orders, Shipments or Invoice tables in Sales and Purchases - they where part of ONE table (bSales or bPurchase).

"Around" the tables of this entity, are various Dimension tables [like gSalesTerms], connected by FKs.

You can find more information about the entity in the WX Analysis.

 
 

Transaction Entities (major changes in v2)

These are simple entities - just one table for every entity structure.

Here is a list of the Transaction entities:

  1. Inventory Transactions: uses the tInventoryTransaction table

  2. Party Transactions [Debits and Credits]: uses the tPartyTransaction table

  3. Check Transactions: uses the tCheckTransaction table

  4. Accounting Transactions: uses the aAccountingTransaction table

The first 3 transaction records are "created" by the BusinessDocuments entities, when they are posted.

Business Documents and the Post State

Every Business Document can be in a state of NOT Posted or Posted.

Think of NOT Posted as a temporary state: the data is stored but nothing has been posted to the transaction tables - you can change or delete the Business Document.

When a user Posts a Business Document, all the relative Transactions are "posted/created" in the transaction tables and various _sum tables are updated.

The transaction records have all the necessary information to automatically update the relative _sum tables.

The only Business Documents that do not create ANY type of transactions are Quotes and Orders.

What Transactions are Posted by each Business Document

You can find this information "embeded" inside the Project code of the alpha360 Retail application and in the BPs that handle Posting [especially the code bricks].

Business Documents and the PostGL State

Every Business Document can be in a state of NOT Posted to GL or Posted GL.

When a user Posts to GL, a Business Document, a Batch entry is automatically created and posted in GL - Quotes, Orders and Shipments do NOT Post to GL.

More information about the Accounting Transactions can be found in the Accounting section.

v1 vs v2

There have been major changes in the transaction and _sum tables in v2.

iWorkFlow (major changes in v2)

This functionality controls the "transformation" of Orders to Shipments, Orders to Invoices and Shipments to Invoices.

In v1 this functionality was controlled by the Inventory transaction records, special allocation tables and 100s of lines of code - probably the most complicated area in our design.

We have totally redesigned this area, eliminated all the allocation tables, moved the information for Quantity_Dues from the transactions to the bBusinessDocument_ProductLines, added super simple UI elements to support the functionality and used only a dozen or so lines of code [in BPs] to handle it.

You can find more information in the projects _add, _post and _unPost BPs

v1 vs v2

This area has been TOTALLY Redesigned.

 
 

Debit & Credit Allocations (naming changes in v2)

This functionality controls the "allocations or pairings" of Debit and Credit Party Transactions.

In v2 we have just changed the notation [in the code and the tables also] - we now use the term "allocation" instead of "distribution".

The table that supports allocation is now named: tParty_allocate,

and can handle and mix allocations - for the same party - from Sales and Purchases.

 

Diagram of Basic Party Entity Structure

Party, Customers, Suppliers and Employees

PartyEntity.png

Diagram of Basic Product Entity Structure

ProductClass, Product etc.

ProductEntity.png

Diagram of Basic Purchase Entity Structure(s)

ProductClass, Product etc.

PurchaseEntity.png

computerplus

Leoforos Dodonis 43,  45221

IOANNINA - GREECE

Registered VAT ID: EL084190121

sales@computerplus.gr