SQLOnAir Data model

CartItem
Customer
Product
ShoppingCart

CRUD	Name	Type	Original Name
CartItem
	CartItemId	ntext
	ShoppingCart	parent
	Product	parent
	Quantity	decimal
Lookup	w/ associated ShoppingCart.CartNumber CartNumber The number asssigned to the items shopping cart	int32
Lookup	w/ associated ShoppingCart.CustomerName CustomerName Lookup to the customer name	string
Lookup	w/ associated ShoppingCart.CustomerEmailAddress CustomerEmailAddress Lookup to the customer email address	string
Lookup	w/ associated ShoppingCart.CustomerPhoneNumber CustomerPhoneNumber Lookup to the customer phone number	string
Lookup	w/ associated Product.Name ProductName The name of the product in the cart	ntext
Lookup	w/ associated Product.UnitPrice UnitPrice The Product's unit price	decimal
Formula	using Quantity * UnitPrice SubTotal SubTotal for the line item	decimal(18,4)
CartItem Query
SELECT CartItemId, ShoppingCart, Product, Quantity, CartNumber, CustomerName, CustomerEmailAddress, CustomerPhoneNumber, ProductName, UnitPrice, SubTotal FROM CartItem

Customer Each Customer may have zero or more associated ShoppingCarts
CRUD	Name	Type	Original Name
	CustomerId	ntext
	Name	ntext
	PhoneNumber	ntext
	EmailAddress	ntext
	TaxRate	decimal
Rollup	w/ associated ShoppingCart.Total using sum(values) TotalSales Total of all of customers shopping carts	decimal(18,4)
Formula	using IF(TotalSales > 100, 1, 0) IsWhale True for customers w/ $100+ in cart	boolean
Customer Query
SELECT CustomerId, Name, PhoneNumber, EmailAddress, TaxRate, TotalSales, IsWhale FROM Customer

Product Each Product may have zero or more associated CartItems
CRUD	Name	Type	Original Name
	ProductId	ntext
	Name	ntext
	UnitPrice	decimal
Rollup	w/ associated CartItem.SubTotal using sum(values) TotalInShoppingCarts SubTotal of product in every shopping carts	decimal(18,4)
Rollup	w/ associated CartItem.Quantity using sum(values) UnitsInShoppingCarts Quantity of product in every shopping carts	decimal
Product Query
SELECT ProductId, Name, UnitPrice, TotalInShoppingCarts, UnitsInShoppingCarts FROM Product

ShoppingCart Each ShoppingCart may have zero or more associated CartItems
CRUD	Name	Type	Original Name
	ShoppingCartId	ntext
	Customer	parent
	CartNumber	int32
	CartDate	datetime
Lookup	w/ associated Customer.Name CustomerName Lookup to the customer name	ntext
Lookup	w/ associated Customer.EmailAddress CustomerEmailAddress Lookup to the customer email address	ntext
Lookup	w/ associated Customer.PhoneNumber CustomerPhoneNumber Lookup to the customer phone number	ntext
Rollup	w/ associated CartItem.SubTotal using sum(values) SubTotal Calculated SubTotal	decimal(18,4)
Lookup	w/ associated Customer.TaxRate TaxRate Lookup to the customer's tax rate	decimal
Formula	using SubTotal * TaxRate Tax Tax on the order	decimal(18,4)
Formula	using SubTotal + Tax Total Order Total	decimal(18,4)
ShoppingCart Query
SELECT ShoppingCartId, CartNumber, CartDate, Customer, CustomerName, CustomerEmailAddress, CustomerPhoneNumber, SubTotal, TaxRate, Tax, Total FROM ShoppingCart

SQLOnAir Data model

Table of Contents

CartItem

Customer

Product

ShoppingCart