Sample Project & Video

Top  Previous  Next

The Advanced Data Generator comes with a project that shows several of its capabilities. Scripts to create the database ready for the sample project are included as well.

 

The video shows the sample project and explains it, you can open this in another browser window if you like.

 

 

Advanced Data Generator - Sample Project Explained

 

Sample project data model

The sample project generates data for a small web-shop and explains how to generate data into tables, including how to use templates, referential links, lookups and custom SQL statements.

 

Tables in the sample database (diagram created with Database Workbench)

 

The idea is as follows:

visitors (or existing registered customers) can pick items from the PRODUCTS table and add them to a cart (via a record in the CART and CART_PRODUCTS tables)
when the contents of the cart is 'checked out' to create an actual order, this results in a record in the table CUSTOMERS for visitors that have to register as a customer
an order has one record in the ORDERS table and a line in the ORDER_LINES table for each of the selected products
the shopping cart is deleted

 

Sample project steps

The following steps are taken to create the data for the sample database.

 

1. Products table

Before we are able to shop around, we need to fill the PRODUCTS table.

 

Item settings: Fixed number of rows: 1000, empty table before generating.

 

PRODID

Set to a predefined template 'Sequence', generates values starting at '1', increasing by '1' in each step

PROD_NAME

Value from list, random

PROD_IMAGE

Set to a predefined template 'Product Images', a folder with sample product images

PRICE

Random values between 0.5 and 250, rounded to 5 cents

WEIGHT

Random values between 0.1 (100 grams) and 25 (kilos), rounded to 0.05 (50 grams)

 

2. Customers table

The second step, is filling the CUSTOMERS table.

 

Item settings: Fixed number of rows: 2500, empty table before generating.

 

CUSTID

Set to a predefined template 'Sequence', generates values starting at '1', increasing by '1' in each step

CUST_NAME

Random values, using a macro defined as 'Dutch first names, followed by a space and a Dutch last name'

CUST_EMAIL

Random values, random e-mail address

ADDRESS

Random values, using a macro defined as 'Dutch street names, followed by a space and a random number with a 4 digit maximum'

POSTCODE

Set to a predefined template 'Dutch postal codes'

COUNTRYCODE

Fixed value 'NL'

PHONENUMBER

Set to a predefined template 'Dutch phone numbers'

REGDATE

Random values between two dates

 

3a. Orders table

Next step is creating orders, we'll be doing that in two steps. Some of these contain customer notes.

 

The first step is to create an order with the same date as the customer registration date.

 

To show an example of using 'lookups', the project generates orders to have the same ORDER_DATE as the customer registration date. There's a lookup defined for the CUSTOMERS table for two columns: CUSTID and REGDATE. Values are taken 'unique'.

 

Item settings: All rows from lookup (2500), empty table before generating.

 

ORDERID

Set to a predefined template 'Sequence', generates values starting at '1', increasing by '1' in each step

CUSTID

Value from lookup 'CUSTOMERS', column 'CUSTID'

ORDER_DATE

Value from lookup 'CUSTOMERS', column 'REGDATE'. This is the value for the same CUSTOMER record as the previous CUSTID value

EXPECTED_DELIVERY_DATE

Value based on another column, column 'ORDER_DATE', between 4 and 16 hours are added (as the expected shipping date, not to-the-door delivery)

CUSTOMER_NOTES

Random values, a single random paragraph for about 20% of the rows

 

3b. Orders table, step 2

The second step for the ORDERS table, an additional 7500 rows, so we have 10000 rows in total for this table.

 

Item settings: Fixed number of rows: 7500, do not empty the table.

 

ORDERID

Set to 'increasing values', start at 2501 and increase by 1.

CUSTID

Referential link 'CUSTOMERS', column 'CUSTID', random

ORDER_DATE

Random values

EXPECTED_DELIVERY_DATE

Value based on another column, column 'ORDER_DATE', between 4 and 16 hours are added (as the expected shipping date, not to-the-door delivery)

CUSTOMER_NOTES

Random values, a single random paragraph for about 20% of the rows

 

4. Order_lines table

To complete the orders, there are order lines. We have a reference to the PRODUCTS table and a copy of the PRICE at the time of the order (in our example, it's possible that product prices in the PRODUCTS table can be modified, by copying the price, the order stays valid).

 

To copy the price and name for the product, we need to use a 'lookup', it's created for table PRODUCTS, columns PRODID, PROD_NAME and PRICE. Values are taken randomly.

 

Order lines are numbered starting from 1 for each order.

 

Item settings: Rows based on 'For each in', between 1 and 5 rows per row in table ORDERS, empty table before generating.

 

ORDERID

Column value from the 'master' (of the 'for each in' loop), in this case, column ORDERID

ORDERLINEID

Increasing values, start at 1, increase by 1 each step, reset this value for each master

PRODUCTID

Value from lookup 'PRODUCTS', column 'PRODID'

QUANTITY

Random values, between 1 and 20

PRICE_PER_ITEM

Value from lookup 'PRODUCTS', column 'PRICE'. This is the value for the same PRODUCTS record as the previous PRODID value

ITEM_DESCRIPTION

Value from lookup 'PRODUCTS', column 'PROD_NAME'. This is the value for the same PRODUCTS record as the previous PRODID value

 

5. Cart table

There's also temporary shopping carts available for some customers.

 

Item settings: Fixed number of rows: 50, empty table before generating.

 

CARTID

Random value, random GUID (a globally unique identifier)

CUSTID

Referential link to CUSTOMERS, column CUSTID. This will take a random value from the CUSTOMERS table

CARTDATE

Random value between two dates

 

6. Cart_products table

This table holds items added to the temporary shopping carts. Cart lines are numbered starting from 1 for each cart.

 

Item settings: Rows based on 'For each in', between 1 and 5 rows per row in table CART, empty table before generating.

 

CARTID

Column value from the 'master' (of the 'for each in' loop), in this case, column CARTID

CARTLINE

Increasing values, start at 1, increase by 1 each step, reset this value for each master

PRODUCTID

Referential link to PRODUCTS, column PRODID. This will take a random value from the PRODUCTS table

QUANTITY

A random value between 1 and 25

 

7. 'insert shipping costs line'

To finish up on our orders, we'll add an order line with a calculated shipping cost for the order. We will insert an additional record in the ORDER_LINES table for each row in ORDER, by calculating the total weight of the order. This is done using a lookup with a specific SQL statement:

 

SELECT

 ORDER_LINES.ORDERID,

 Sum(PRODUCTS.WEIGHT) as total_weight,

 case                                  

   when sum(products.weight) > 40 then 3

   when sum(products.weight) > 10 then 2

   else 1

 end as quantity

FROM

 ORDER_LINES

 INNER JOIN PRODUCTS ON ORDER_LINES.PRODUCTID = PRODUCTS.PRODID

GROUP BY

 ORDER_LINES.ORDERID

 

This lookup returns a result set with a calculated value for the order lines for each order, based on the product weight for each order line. As you can see above, if the total weight is above '40', it will return '3', if it's above '10', it will return '2' and if below '10', the value of '1' is returned. These values will be used as the 'quantity' of the shipping costs. In this example, a total weight of 40 or more, will cost you 3 times the normal shipping cost.

 

A custom INSERT SQL-statement is used to process all rows from the lookup. Each row of the lookup will only be used once. This is the SQL item to process:

 

INSERT INTO ORDER_LINES

(

ORDERID,

ORDERLINEID,

PRODUCTID,

QUANTITY,

PRICE_PER_ITEM,

item_description

)

VALUES

(

:ORDERID,

:ORDERLINEID,

:PRODUCTID,

:QUANTITY,

:PRICE_PER_ITEM,

:item_description

)

 

Item settings: All rows from lookup 'Calc Shipping Costs'.

 

ORDERID

Value from lookup, result set column ORDERID

ORDERLINEID

Fixed value '999'

PRODUCTID

Empty

QUANTITY

Value from lookup, result set column QUANTITY (value of 1, 2 or 3)

PRICE_PER_ITEM

Fixed value '7.5' (currency)

ITEM_DESCRIPTION

Fixed value 'Shipping and handling'

 

 

 

 

In the video above, you can see how to (re)create this sample project and the results.