Monday, 1 August 2016

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2

Converting a Model into another Type

All the work that you put into the Conceptual model need not go to waste.  We can utilize it as the basis for the Logical model.
  • Select File > Model Conversion... from the main menu to open the Convert to New Model dialog.
  • Choose Logical as the Model Type and click OK:
Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2
Convert to New Model

Note that the Target Database dropdowns are disabled because a Logical model does not contain database-specific information.

Navicat Modeler will create a new Logical Model of the Sales Star Schema.  Entities and their relationships will be carried over, so that we can dive right into attribute (a.k.a. field) creation.

Here are the attributes for each of the four entities:

Name Type (Char) Length Decimals* Not Null Default Value**
id (PK) INTEGER 0 0 Yes N/A
date_id INTEGER 0 0 Yes N/A
store_id INTEGER 0 0 Yes N/A
product_id INTEGER 0 0 Yes N/A
units_sold INTEGER 0 0 No NULL

*Decimals only apply to numeric fields.
** Can be either NULL or an EMPTY STRING.

t_dates:

NameTypeLengthDecimalsNot NullDefault Value
id (PK)INTEGER00YesN/A
dayCHAR20NoNULL
monthCHAR20NoNULL
yearCHAR40NoNULL
quarterNUMERIC10NoNULL

t_stores:

NameTypeLengthDecimalsNot NullDefault Value
id (PK)INTEGER00YesN/A
store_numINTEGER00YesN/A
nameCHAR VARYING2550YesN/A

t_products:

NameTypeLengthDecimalsNot NullDefault Value
id (PK)INTEGER00YesN/A
prod_codeCHAR VARYING250YesN/A
nameCHAR VARYING2550YesN/A
prod_categoryCHAR VARYING500YesN/A

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2
The Converted Sales Star Schema Model
  • The easiest way to define an entity’s attributes is to right-click it and choose Design Entity from the popup menu.

That brings up the Entity Design dialog.  It provides tabs for managing attributes (fields), indexes, relations, uniques, and comments:

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2
t_dates - Entity

Simplifying MySQL Database Design using a Graphical Data Modeling Tool - Page 2
The Completed Sales Star Schema Logical Model