• 1st method: Creating the database schema (or Logical Data Model (LDM)) directly.
• 2nd metod: Creating the Conceptual Data Model (CDM), then creating the database schema from the CDM. For more details, see Generating the LDM from the CDM.

Creating a database schema (or LDM)

Adding a table to a database schema

The database schema allows you to define the structure of tables used by the project. In the database schema, a table can be:

• A new table.
• A preset table, provided with WINDEV, WEBDEV or WINDEV Mobile.
• A table imported from an existing database (using a specific format, for example).

The following paragraphs explain how to create a table.

To create a new table:

1. On the "Database schema" tab, in the "Creation" group, click "New table". The table creation wizard opens.
2. Select "Create a new table description".
3. Specify:
   • the name of the table. This name is the logical name of the table. It will be used to handle the table.
   • the caption of the table that briefly describes the file subject.
   • the representation of a record in the table. This representation makes the questions asked when describing the links easier to understand. This option must be preceded by an indefinite article (A or AN).
   • whether the table includes an "Automatic identifier" item. The value of this item is unique for each record and is automatically calculated by WINDEV, WEBDEV or WINDEV Mobile.
   • the type of database associated with the table:
     • Database HFSQLDrive (paid turnkey solution, on a shared server).
     • HFSQL database in PCSCloud (paid turnkey solution, on a dedicated server).
     • HFSQL database (Client/Server or Classic).
     • Database accessible via a Native Connector.
     • Other types.
     Depending on the type selected, this table will be handled by the HFSQL engine, an OLE DB driver or one of WINDEV or WEBDEV's Native Connectors (SQL Server, Oracle, etc.).
4. The new table becomes the current table. The description window of the table items opens automatically. It allows you to describe the file items.

To import a preset description of a table:

1. On the "Database schema" tab, in the "Creation" group, click "New table". The table creation wizard opens.
2. Select the option "Select a preset table description".
3. Choose the preset table to be created (you can select multiple tables). This table will be imported into the current database schema. This table can be modified later.
4. Select the items you want to keep. These items can be modified later.
5. The imported table is automatically inserted into the current database schema.

Note: To modify a table or the items in a table:

1. Select the table.
2. Select "Table description" or "Description of items" in the context menu.

Two solutions can be used to import an existing table description:
Solution 1: From the data model editor

1. On the "Database schema" tab, in the "Creation" group, click "New table". The table creation wizard opens.
2. Select "Use the tables from an existing database" and select the type of database.
3. Specify the source database containing the descriptions to be imported and the type of database. Depending on the selected type, specify the requested information.
4. Select the tables or tables whose description must be imported and validate.
5. The imported table is automatically inserted into the current database schema.

Solution 2: From Windows explorer
Drag the table description from the Windows explorer and drop it onto the data model editor.

For example:

This is an Oracle database on the server. SImply drag the name of the database and drop it onto the data model editor to transfer its description.

Creating an item

To create an item:

1. Double-click the table where the item will be created. The description window of the table items opens.
2. Click the first empty row in the table.
3. Specify the name, caption and type of the item.
4. In the right section of the window, indicate the details of the new item (type, size, default value, iteration direction, etc.).
5. Enter the details of the shared information by clicking"Parameters of the control bound to the selected item (shared information)".
6. Validate the item description.

Note: You can also create an item using the meta-types provided by WINDEV. To do so, click the button. The list of available metatypes appears.

Creating a link

Owner table and member table

When a link is defined between two tables, there is an owner table and a member table:

• the owner table is the owner of the key.
• the member table is a member of the database schema tables containing a copy of the key.

To set the link between two tables, the key of the owner table is copied to the member table.

For example, the key of the Supplier table is copied to each record of the Product table. Several records of the Product table can have the same key of the Supplier table:

• Supplier is the owner table,
• Product is the member table.

This type of link is represented as follows:

Cardinalities

Cardinalities are used to count the relationships between two tables.

Cardinalities are defined according to the answers to the two following questions:

1. For each record of the table, what is the minimum number of records in the other table this record is linked to?
   The answer provides the first part of the cardinality (minimum cardinality):
   • if the answer is "none", the cardinality is 0,X.
   • if the answer is "a single one", the cardinality is 1,X.
2. For each record of the table, what is the maximum number of records in the other table this record is linked to?
   The answer provides the second part of the cardinality (maximum cardinality):
   • if the answer is "a single one", the cardinality is X,1.
   • if the answer is "several", the cardinality is X,N.

The answer to these two questions defines the cardinality that can be: 0,1 ; 0,N ; 1,1 ; 1,N

Cardinalities are essential because they ensure referential integrity in a database.

To put it simply, these two questions can be broken down into four questions. For example, to describe a link between the Club and Member tables:

• each "Club" has at least one "Member": Yes/No
• each "Club" can have several "Members": Yes/No?
• each "Member" has at least one "Club": Yes/No?
• each "Member" can have several "Clubs": Yes/No?

Example of cardinalities:

This example presents two different cardinalities:

• 0,1: A person can be a member of a single sport club. This person does not have to belong to a sport club.
• 0,N: A club can have zero or several members.

Advanced cardinalities

Cardinalities can be defined more precisely. This is referred to as advanced cardinalities.

These cardinalities allow you to specify exactly the minimum and maximum cardinality.

To define the advanced cardinalities, check "Display advanced cardinalities" in the link description.

Example of advanced cardinalities:

This example presents two different cardinalities:

• 0,1: A person can be a member of a single sport club. This person does not have to belong to a sport club.
• 0,10: A club can have from 0 to 10 members.

Referential integrity

Types of links

There are different types of links between tables:

• parallel,
• optional,
• complement,
• shared,
• complex.

Parallel link

For a parallel link, each record in a table (Product) is linked to a record in another table (Info), and vice versa.

In a parallel link, the identifier of the Product table is copied to the Info table. This identifier is also a unique key in the Info table.

Product is the owner table and Info is the member table.

Note: This type of link is rare, as the two tables can be combined into a single file.

The parallelism of records in the table is respected if the following operations are run simultaneously on the two tables:

• creation of a record.
• deletion of a record.
• reindexing with compression.

Optional link

For an optional link:

• Each record of a table (Category) has no matching records or has one matching record in the other table (Group).
• Each record of the other table (Group) is associated with no record or with a single record in the first table (Category).
  

An optional link is established by copying the identifier from one table to the other.

Complement link

For a complement link:

• Each record from a table (Product) has no matching records or has one matching record in another table (Details).
• Each record found in the other table (Details) necessarily has an associated record in the first table (Product).
  

Complement links are quite common. They are used when a record can have additional optional information.

In a complement link, the key of the Product table is copied to the Details table. To ensure the maximum cardinality of 1, it remains a unique key. Since this key is unique, it is not possible to insert more than one record in the Details table for a record in Product.

Product is the owner table and Details is the member table.

Shared link

For a shared link, the same record in a table (Supplier) can be shared by several records in another table (Product). In a shared link, the key of the Supplier table is copied to the Product table. It becomes a multiple key to optimize the speed of the integrity check.

Supplier is the owner table and Product is the member table.

Depending on the cardinality, we can distinguish between four types of shared links:

• Shared link with a 0, n - 0, 1 cardinality.
• Shared link with a 0, n - 1, 1 cardinality.
• Shared link with a 1, n - 0, 1 cardinality.
• Shared link with a 1, n - 1, 1 cardinality.

Shared link with a 0,n - 0,1 cardinality

With this type of link:

• an owner may have no member (a supplier does not necessarily have a product).
• a member may have no owner (a product does not necessarily have a supplier).

Shared link with a 0,n - 1,1 cardinality

With this type of link:

• an owner may have no member (a supplier may have no product).
• each member has a single owner (each product has a single supplier).

Note: This is a common type of link. WINDEV allows you to automatically create windows that manage tables with a 0,N - 1,1 relationship.

Shared link with a 1,n - 0,1 cardinality

With this type of link:

• each owner has at least one member (a supplier has at least one product).
• a member may have no owner (a product may have no supplier).
  

Shared link with a 1,n - 1,1 cardinality

With this type of link:

• each owner has at least one member (each supplier has at least one product).
• each member has a single owner (each product has a single supplier).
  

Complex link

For a "complex" link, you need to define a relationship file.

The relationship file will be automatically created. It will contain a unique key that includes the two keys of the related tables.

The relationship file can also contain information specific to the link.

A complex link includes two shared links.

Example of complex link

An order (Orders table) can contain one or more products. A product (Product table) can be used in several orders.

In this case, a relationship file is required (OrderLine table).

The OrderLine table contains:

• a unique key containing the keys of Product and Orders.
• the number of products ordered.

The links between the tables are as follows:

• Duplicate or copy a database schema. This makes it possible to have two identical database schemas with different names.
• Delete a database schema.
• Rename a database schema.
• Associate a database schema with a project.
• Zoom in and zoom out a database schema in the editor.
• Move around the view of the database schema in the editor.

• Duplicate/Copy a table.
• Delete a table.
• Rename a table.

• Duplicate/Copy an item.
• Delete an item.
• Rename an item.

• validate the changes made on the database schema (LDM).
• create the modules required for programming.
• automatically update the tables if necessary.

• Verification of the changes made and generation of the physical files corresponding to the database schema description.
• Automatic modification of accessible tables (tables in the "EXE" directory of the project).
• Project synchronization.

1. On the "Database schema" tab, in the "Database schema" group, click "Generation".
2. Select "Manage versions".
3. In the wizard, specify whether you want to work on the current database schema or on a specific database schema.

• one rectangle: few changes were made between the two versions.
• several rectangles: several modifications have been made between the two versions.

• Restoring a database schema.
• Undoing the last generation.
• Resetting the version number to 1.

Restoring a database schema

Undoing the last generation

Resetting the version number to 1