Difference between revisions of "Data Integration"

Revision as of 18:11, 2 April 2009

The integration details can be a bulleted list like below. In addition to each transfer being described, we will need example transfers for each type. All details (field names, data types, tables, etc) should be from the perspective of your system (MARCO).

Description

Breakdown the list in the following format:

• From and To System (so we know what direction we are sending the info)

• Transfer Name

Details about transfer like how often to execute, how it will connect, any ‘triggered actions’ that may take place as it occurs and the data format. Any information about returned data(error codes, etc) will be here as well.

• Flat Table Name

• Data Field Name DATATYPE (Expected layout) [examples or fixed values]

Example

• From Web to Point Of Sale

• Orders

Transferred every 15 minutes, all orders that have been COMPLETED since last transfer. Transfer method will be FTPing a file to ftp://address/folder using user name “user” and password “password”. The data will be in comma delimited format

• Orders

• OrderNumber INT(21)
• CreateDate DATETIME (YYYY-MM-DD HH:MM:SS)
• CompleteDate DATETIME (YYYY-MM-DD HH:MM:SS)
• SourceCode VARCHAR(20)
• userId INT(21)

• Products

• itemId INT(21)
• SKU VARCHAR(25)
• PricePer FLOAT(10,2)
• Quantity INT(4)

• Product Options

• optionId INT(21)
• productId INT(21)
• optionName VARCHAR(25);
• optionValue VARCHAR(100);
• optionPrice FLOAT(10,2)

• Discounts

• discountId INT(21)
• discountCode VARCHAR(25)
• discountDetails VARCHAR(100)
• amount FLOAT(10,2)

• Payments

• paymentId INT(21)
• cardNumber VARCHAR(20)
• expDate DATE (YYYY-MM-DD)
• status VARCHAR(2) [Approved, Declined, Manual…]
• firstName VARCHAR(50)
• lastName VARCHAR(50)

• Addresses

• addressed INT (21)
• shippingOrPayment BIT [1,2]
• firstName VARCHAR(50)
• lastName VARCHAR(50)
• address1 VARCHAR(100)
• address2 VARCHAR(100)
• city VARCHAR(50)
• state CHAR(2)
• zip VARCHAR(5)
• phone1 VARCHAR(21)
• phone2 VARCHAR(21)
• emailAddress VARCHAR(100)

• User Accounts

Transferred once a day at 4:00am EST. Transfer method will be an http call made by MARCO to address http://urlhere/folder/file . The file will be closed with a “<!—COMPLETED -->” tag if the request was successfully fulfilled. The data will be in comma delimited format.

• Users

• userId INT(21)
• emailAddress VARCHAR(100)
• password VARCHAR(20)
• firstName VARCHAR(50)
• lastName VARCHAR(50)

• From MARCO to WEB

• Shipping (Fulfillment) Status

This transfer will occur every 1 hour on the hour. MARCO will drop an FTP file in FTP://urlhere/folder/file user name: “user” password “password”, Each order that comes in triggers an email to the customer with the tracking information. Email content will be sent later. The data will be fixed width according to the field sizes below.

• ORDERS

• trackingNumber VARCHAR(100)
• itemId INT(21)
• ordered INT(21)

• Product Pricing

This transfer will occur weekly on Sunday at 4:00 am EST. The web site will make an HTTP call to http://urlhere/folder/file to retrieve the data. The data will be in XML format.

• PRODUCTS

• productNumber INT(21)
• listPricePer FLOAT(10,2)
• pricePer FLOAT(10,2)