Data modeling

In Paradox, you can create a data model to help you structure a database. A data model is a graphical representation of the database structure that can be used to create a form or a report. You can create data models in the Data model designer or when you are creating a form or a report. You can modify data models by adding or removing tables and links. You can also create table aliases, which allow you to use a table more than once in a data model.

In this section, you'll learn about

  creating data models
  
  opening data models
  
  changing data models
  
  adding or removing tables in data models
  
  linking tables in data models
  
  modifying data model table properties when creating forms and reports
  
  creating and removing table aliases
  
  saving and renaming data models
  
  printing data models
  
Creating data models

You can create data models independently of other database objects, or you can create them while working on a form or a report.

When you create data models independently of other database objects, you use the Data Model Designer. The Data Model Designer looks similar to the Visual Database Designer, but their purposes are different. The Data Model Designer lets you link tables to create data models used with forms and reports. The relationships that you create between tables using the Visual Database Designer are actually saved in the table structure. For more information about the Visual Database Designer, see "Working with the Visual Database Designer."

You can base data models on tables, queries, or Structured Query Language. When you base a data model on tables, you can use a single table or multiple tables.

If you build a data model using multiple tables, you can establish the relationships, also know as cardinality, between the tables by linking them to one another. When you place more than one table in a data model, one table is defined as the parent table and can be linked to one or more child tables. For more information, see "Linking tables in data models."

You can also create complex data models. Complex data models can include a combination of single-value relationships and multi-value relationships. When creating complex data models, it is important that the indexes are correctly identified. For information about indexes, see "Defining and deleting secondary indexes."

To create a data model independently of other database objects

1	Click File  New  Data model.

2	Choose one of the following objects from the Object list box:

  Tables - lets you search for tables
  
  Queries - lets you search for queries
  
  SQL - lets you search for Structured Query Language
  
3	Click Directory browse (...).

4	Choose the drive and folder where the object is stored.

5	Click OK.

6	Choose an object from the list.

7	Click Add.

Notes

  Any query you must search for must be the master table in a multi-table design.
  
  Tables are placed on the workspace stacked on top of one another, but you can drag them to a new position in the design window.
  
Tip

  You can also choose a table alias from the Alias list box.
  
To create a data model when working on a form or report

1	Click Format  Data model.

2	Choose one of the following objects from the Object list box:

  Tables - lets you search for tables
  
  Queries - lets you search for queries
  
  SQL - lets you search for Structured Query Language
  
3	Click Directory browse (...).

4	For each object you want to add to the data model, choose the drive and folder where the object is stored.

5	Double-click the filename to add it to the Modeler palette.

6	Drag the tables to arrange them in the Modeler palette.

7	Click Save DM.

Note

  Any query you must search for must be the master table in a multi-table design.
  
Opening data models

A data model that isn't attached to any specific form or report can be opened in the Data Model Designer. You can also open a data model when working on a form or report.

To open a data model independently of other database objects

1	Click File  Open  Data model.

2	Choose the drive and folder where the data model is stored.

3	Click the filename.

4	Click Open.

To open a data model when working on a form or report

1	Click Format  Data model.

2	Choose Data models from the Object list box.

3	Click Directory browse (...).

4	Choose the drive and folder where the data model is stored.

5	Double-click the filename.

Changing data models

While designing a form or report, you can open a data model and change it. For example, you can add or remove tables.

To change a data model when working on a form or report

1	Click Format  Data model.

2	Change the data model.

To cancel changes to the data model when working on a form or report

1	Click Format  Data model.

2	Change the data model.

3	Click Cancel.

Adding or removing tables in data models

You can add or remove tables in data models that are independent of other database objects or to data models when working in a form or report.

To add a table to a data model that is independent of other database objects

1	Click Edit  Add object.

2	Choose Tables from the Object list box.

3	Click Directory browse (...).

4	Choose the drive and folder where the table is stored.

5	Click OK.

6	Click Add.

7	Click Close.

To add a table to a data model when working on a form or report

1	Click Format  Data Model.

2	Choose Tables from the Object list box.

3	Click Directory browse (...).

4	Choose the drive and folder where the table is stored.

5	Click OK.

6	Double-click a table.

To remove a table from a data model by using the Data Model Designer

1	Select a table.

2	Click Edit  Remove table.

Note

  Parent tables cannot be deleted from a data model unless they are unlinked.
  
To remove a table from a data model when working on a form or report

1	With a form or report open in the design window, click Format  Data model.

2	Select a table.

3	Right-click the table, click Remove table.

Notes

  Parent tables cannot be deleted from a data model unless they are unlinked.
  
  When a table is removed, the change is immediately reflected in your current form or report.
  
Linking tables in data models

When designing a data model, you can define the relationship between tables by linking them and the links are reflected in a design document, such as a form or report. You can link a parent table to a child table or a child table to a parent table. When you create a link from a parent field to a child field, you previously established referential integrity between the two tables you are linking, the referential integrity link automatically displays a line between the two tables. If you do not have referential integrity between the tables you are linking, you need to create the link. For more information about creating links, see "Defining referential integrity."

When creating data models for reports with group bands, you can link the tables backwards, from child table to parent table, rather than in the conventional way of parent table to child table. This gives you more choices of fields by which to group when adding a group band to the report. For more information about group bands, see "Working with bands and page breaks."

You can also remove links from a data model, either in the Data Model Designer or while working on a form or report.

Once you add links to your data model, the links are displayed as arrows. The direction of the arrow shows the direction of the link. Two tables linked with a double-headed arrow have a multi-value or one-to-many relationship. Tables linked with a single-headed arrow have a single-value or one-to-one relationship.

To link a parent field to a child field

1	With a form or report open in the design window, click Format  Data model.

2	Right-click the blank space in the Data model dialog box and click Links.

If you right-click a table, the Links command is not available on the submenu.

3	Click New.

4	In the Link property dialog box, choose the parent table from which you want to create a link from the Parent tables list box.

5	Choose a parent field from the Parent fields list.

6	Click the right arrow to add the parent field to the Parent keys panel.

7	From the Child tables dialog box, choose the child table to which you want to link the parent table.

If a child field was not automatically selected, click a child field from the Child index list.

8	Click the left arrow to add the field to the Child fields panel.

Notes

  You cannot create a link using BLOB, bytes, or logical fields because you cannot create an index on these field types.
  
  If a data model is based on only one table and referential integrity is declared between the table in the data model and a table which is not present in the data model, you may encounter problems if you attempt to edit the form's data, because you may violate referential integrity.
  
Tip

  You can create a link in the Data Model Designer window by dragging a field from the parent table to the field you want to link to in the child table. If referential integrity exists between the tables, the link automatically displays. However, if referential integrity does not exist, the Link property dialog box opens for you to create the link.
  
To link a child field to a parent field

1	With a form or report open in the design window, click Format  Data model.

2	Right-click the blank space in the Data model dialog box, and click Links.

If you right-click a table, the Links command is not available in the submenu.

3	Click New.

4	From the Child Tables list box, choose the child table from which you want to create a link.

5	Choose a child field from the Child index list.

  If you're using a composite key or index on the child table, select fields from the parent table to match some or all of the fields in the index.
  
6	Click the right arrow to add the child field to the Child fields panel.

7	From the Parent Tables box, choose the parent table to which you want to link the child table .

8	Choose a parent field from the Parent keys list.

9	Click the left arrow to add it to the Parent fields panel.

Note

  If you are using a composite key or index and match all its fields, Paradox creates a one-to-one link; otherwise Paradox creates a one-to-many link.
  
Tip

  You can create a link in the Data Model Designer window by clicking a field in the child table and dragging it to the field you want to link to in the parent table. If referential integrity exists between the tables, the link will automatically appear. However, if referential integrity does not exist, the Link Property dialog box will open.
  
To remove a link from a data model in the Data model designer

1	Click Edit  Links.

2	Choose a link from the Links list.

3	Click Unlink.

To remove a link from a Data model when working on a form or report

  Right-click a link, and click Unlink table.
  
Modifying data model table properties when creating forms and reports

When creating a form or report, you can modify a data model from a form or report and specify certain properties. For example, you can protect a table from being edited in a form.

You can also apply strict translation to a table, which restricts the characters you can input into a table to those in the character set of the table's language driver.

You can specify to auto-append a new record in a table. For example, when you are entering data in a table, a blank record is automatically added whenever you move beyond the last record in the table.

To protect a table from being edited

1	With a form or report open in the design window, click Format  Data model.

2	Right-click a table, and click Read only.

A check displays beside the Read only command to indicate the table is protected.

To apply strict translation to a table

1	With a form or report open in the design window, click Format  Data model.

2	Right-click a table, and click Strict translation.

A check displays beside the Strict translation command to indicate the table is protected.

To auto-append a new record

1	With a form or report open in the design window, click Format  Data model.

2	Right-click a table, click Auto-append.

A check displays beside the Auto-append command to indicate the table is protected.

Creating and removing table aliases

Paradox lets you create  and remove table aliases. An alias is an alternate name for an existing table. Aliases allow you to refer to the same table in a data model by using a different name, which lets you use the same table more than once in a data model. In addition, you can use a table alias in a recursive relationship. A recursive relationship occurs when a single table is used as if it were two tables. The table contains all the fields necessary to link to itself in a one-to-many relationship. 

You can change table aliases to conform to the naming conventions of your SQL server when you upsize your application. You can also refer to tables in ObjectPAL code using table aliases. This means you can change the table your code refers to without breaking the code or requiring table name modifications.

As well, you can use table aliases instead of table names when you create calculated fields. If you must change tables, you can keep the calculated field expressions by assigning the table alias to the new table.

To create a table alias

1	Right-click a table in a data model, and click Table alias.

2	In the Table name box, type an alias for the table.

Note

  A table alias must contain an alpha character as its first character and can be up to 32 characters in length, but it cannot contain spaces.
  
To remove a table alias

1	Right-click a table in a data model, and click Table alias.

2	Choose a table alias from the Table name box.

3	Press DELETE.

Saving and renaming data models

Paradox lets you save the data models you create. When you save a data model, the properties specified for each table are saved in the data model. You can also rename data models.

To save a data model

1	In the Data Model Designer, click File  Save.

2	Choose the drive and folder where you want to save the data model.

3	Type a name in the Filename box.

4	Click Save.

Note

  When you save a data model, the layout of the table(s) is saved.
  
Tip

  When working on a form or a report, you can save a data model by clicking Save DM.
  
To rename a data model

1	In the Data Model Designer, click File  Save as.

2	Choose the drive and folder where you want to save the data model.

3	Type a new name in the Filename box.

4	Click Save.

Printing data models

You can print a data model, and you can preview the data model before it is printed.

To print a data model

1	In the Data Model Designer, click File  Print.

2	In the Destination area, choose a printer from the Name list box.

If you want to print the data model to a file, enable the Print to file check box.

3	Click Print.

You can also	

Change your printer properties	In the Print dialog box, click the Main tab and click Properties.

Check print history and status	In the Print dialog box, click the Main tab and click Status.

Note

  You can print a data model only from the Data Model Designer. To print a data model attached to a form or report, save the data model with a .dm filename extension in the Data model dialog box, and open it the Data Model Designer to print it.
  
To preview the data model before printing

1	Click File  Print preview.

If the data model has multiple pages, click Next page or Previous page.

2	File Close.

You can also	

Print the data model from print preview mode	Click File  Print.

Modify the page setup from preview mode	Click the Page setup button on the toolbar.

View a specific page if the data model has multiple pages	Click View  Go to. Type a value in the Page number box.






