Difference between revisions of "Data Integration"
From Whirlwind eCommerce Wiki
| Line 12: | Line 12: | ||
==Example== | ==Example== | ||
| − | + | *;''From Web to MARCO:='' | |
| − | + | :*;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 | + | :''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. | + | :''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 | Users | ||
• userId INT(21) | • userId INT(21) | ||
Revision as of 18:08, 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 MARCO:=
- 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: o 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) o 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)
