DeZign for Databases V4

Logical model

List of attributes

Attribute name	Entity name	Key	Data type	Not null
action	OrderLog		TINYINT	No
activeProductCount	Category		INTEGER	Yes
address1	UserAddress		VARCHAR	No
address2	UserAddress		VARCHAR	No
addressID	ExpressCheckout	FK	INTEGER	No
allowDownloadDays	ProductFile		INTEGER	No
amount	ShipmentTax		FLOAT	No
amount	Shipment		FLOAT	No
amount	Transaction		FLOAT	No
amountCurrencyID	Shipment		CHAR	No
availableProductCount	Category		INTEGER	Yes
billingAddressID	CustomerOrder	FK	INTEGER	No
capturedAmount	CustomerOrder		FLOAT	No
categoryID	SpecFieldGroup	FK	INTEGER	No
categoryID	CategoryImage	FK	INTEGER	No
categoryID	SpecField	FK	INTEGER	No
categoryID	Product	FK	INTEGER	Yes
ccExpiryMonth	Transaction		TINYINT	No
ccExpiryYear	Transaction		INTEGER	No
ccLastDigits	Transaction		CHAR	No
ccName	Transaction		VARCHAR	No
ccType	Transaction		VARCHAR	No
city	UserAddress		VARCHAR	No
code	State		VARCHAR	Yes
comment	Transaction		TEXT	No
companyName	UserAddress		VARCHAR	No
companyName	User		VARCHAR	No
count	OrderedItem		FLOAT	No
countryCode	DeliveryZoneCountry		CHAR	Yes
countryID	UserAddress		CHAR	No
countryID	State		CHAR	Yes
currencyID	Transaction		CHAR	No
currencyID	ProductPrice	PK,FK	CHAR	Yes
currencyID	CustomerOrder		CHAR	No
customerOrderID	OrderedItem	FK	INTEGER	Yes
dataType	SpecField		SMALLINT	No
dateAdded	OrderedItem		TIMESTAMP	No
dateCompleted	CustomerOrder		TIMESTAMP	No
dateCreated	ProductReview		TIMESTAMP	No
dateCreated	User		TIMESTAMP	Yes
dateCreated	Product		TIMESTAMP	Yes
dateCreated	CustomerOrder		TIMESTAMP	Yes
dateShipped	Shipment		TIMESTAMP	No
dateUpdated	Product		TIMESTAMP	No
defaultBillingAddressID	User	FK	INTEGER	No
defaultImageID	Category	FK	INTEGER	No
defaultImageID	Product	FK	INTEGER	No
defaultShippingAddressID	User	FK	INTEGER	No
deliveryZoneID	DeliveryZoneRealTimeService	FK	INTEGER	No
deliveryZoneID	ShippingService	FK	INTEGER	No
deliveryZoneID	TaxRate	FK	INTEGER	No
deliveryZoneID	DeliveryZoneAddressMask	FK	INTEGER	No
deliveryZoneID	DeliveryZoneZipMask	FK	INTEGER	No
deliveryZoneID	DeliveryZoneCityMask	FK	INTEGER	No
deliveryZoneID	DeliveryZoneState	FK	INTEGER	No
deliveryZoneID	DeliveryZoneCountry	FK	INTEGER	No
description	SpecField		MEDIUMTEXT	No
description	ProductFile		MEDIUMTEXT	No
description	UserGroup		TEXT	No
description	Category		MEDIUMTEXT	No
email	User		VARCHAR	No
extension	ProductFile		VARCHAR	No
fileName	ProductFile		VARCHAR	No
filterGroupID	Filter	FK	INTEGER	No
firstName	UserAddress		VARCHAR	No
firstName	User		VARCHAR	No
flatCharge	ShippingRate		FLOAT	No
gatewayTransactionID	Transaction		VARCHAR	No
handle	StaticPage		VARCHAR	No
handle	SpecField		VARCHAR	No
hits	Product		INTEGER	No
ID	ExpressCheckout	PK	INTEGER	Yes
ID	DeliveryZoneRealTimeService	PK	INTEGER	Yes
ID	NewsPost	PK	INTEGER	Yes
ID	Product	PK	INTEGER	Yes
ID	OrderLog	PK	INTEGER	Yes
ID	ShipmentTax	PK	INTEGER	Yes
ID	StaticPage	PK	INTEGER	Yes
ID	OrderedItem	PK	INTEGER	Yes
ID	ShippingService	PK	INTEGER	Yes
ID	ProductFileGroup	PK	INTEGER	Yes
ID	ShippingRate	PK	INTEGER	Yes
ID	TaxRate	PK	INTEGER	Yes
ID	Tax	PK	INTEGER	Yes
ID	DeliveryZoneAddressMask	PK	INTEGER	Yes
ID	DeliveryZoneZipMask	PK	INTEGER	Yes
ID	DeliveryZoneCityMask	PK	INTEGER	Yes
ID	DeliveryZoneState	PK	INTEGER	Yes
ID	User	PK	INTEGER	Yes
ID	SpecField	PK	INTEGER	Yes
ID	DeliveryZoneCountry	PK	INTEGER	Yes
ID	DeliveryZone	PK	INTEGER	Yes
ID	OrderNote	PK	INTEGER	Yes
ID	ShippingAddress	PK	INTEGER	Yes
ID	Shipment	PK	INTEGER	Yes
ID	SpecFieldValue	PK	INTEGER	Yes
ID	Transaction	PK	INTEGER	Yes
ID	BillingAddress	PK	INTEGER	Yes
ID	Category	PK	INTEGER	Yes
ID	UserAddress	PK	INTEGER	Yes
ID	AccessControlAssociation	PK	INTEGER	Yes
ID	ProductReview	PK	INTEGER	Yes
ID	ProductRelationshipGroup	PK	INTEGER	Yes
ID	UserGroup	PK	INTEGER	Yes
ID	SpecFieldGroup	PK	INTEGER	Yes
ID	State	PK	INTEGER	Yes
ID	Filter	PK	INTEGER	Yes
ID	CustomerOrder	PK	INTEGER	Yes
ID	CategoryImage	PK	INTEGER	Yes
ID	ProductFile	PK	INTEGER	Yes
ID	ProductImage	PK	INTEGER	Yes
ID	Manufacturer	PK	INTEGER	Yes
ID	Currency	PK	CHAR	Yes
ID	Role	PK	INTEGER	Yes
ID	FilterGroup	PK	INTEGER	Yes
isAdmin	OrderNote		BOOL	Yes
isAdmin	User		BOOL	Yes
isBackOrderable	Product		BOOL	Yes
isCancelled	CustomerOrder		BOOL	Yes
isCompleted	Transaction		BOOL	Yes
isDefault	Currency		BOOL	No
isDisplayed	SpecField		BOOL	No
isDisplayedInList	SpecField		BOOL	No
isEnabled	Product		BOOL	Yes
isEnabled	NewsPost		BOOL	No
isEnabled	DeliveryZone		BOOL	No
isEnabled	User		BOOL	Yes
isEnabled	Category		BOOL	No
isEnabled	FilterGroup		BOOL	No
isEnabled	Currency		BOOL	No
isFeatured	Product		BOOL	Yes
isFinalized	CustomerOrder		BOOL	Yes
isFractionalUnit	Product		BOOL	Yes
isFreeShipping	DeliveryZone		BOOL	No
isFreeShipping	Product		BOOL	Yes
isInformationBox	StaticPage		BOOL	Yes
isMultiValue	SpecField		BOOL	No
isPaid	CustomerOrder		BOOL	Yes
isRead	OrderNote		BOOL	Yes
isRealTimeDisabled	DeliveryZone		BOOL	No
isRequired	SpecField		BOOL	No
isSavedForLater	OrderedItem		BOOL	No
isSeparateShipment	Product		BOOL	Yes
isVoided	Transaction		BOOL	Yes
keywords	Category		MEDIUMTEXT	No
keywords	Product		TEXT	No
lastName	User		VARCHAR	No
lastName	UserAddress		VARCHAR	No
lastUpdated	Currency		TIMESTAMP	No
lft	Category		INTEGER	No
longDescription	Product		MEDIUMTEXT	No
manufacturerID	Product	FK	INTEGER	No
mask	DeliveryZoneAddressMask		VARCHAR	No
mask	DeliveryZoneZipMask		VARCHAR	No
mask	DeliveryZoneCityMask		VARCHAR	No
method	ExpressCheckout		VARCHAR	No
method	Transaction		VARCHAR	No
methodType	Transaction		TINYINT	Yes
minimumQuantity	Product		FLOAT	No
moreText	NewsPost		MEDIUMTEXT	No
name	Product		MEDIUMTEXT	No
name	ShippingService		MEDIUMTEXT	No
name	ProductFileGroup		MEDIUMTEXT	No
name	Tax		MEDIUMTEXT	No
name	Filter		MEDIUMTEXT	No
name	Manufacturer		VARCHAR	Yes
name	SpecField		MEDIUMTEXT	No
name	State		VARCHAR	No
name	DeliveryZone		VARCHAR	No
name	Category		MEDIUMTEXT	No
name	FilterGroup		MEDIUMTEXT	No
name	SpecFieldGroup		MEDIUMTEXT	No
name	Role		VARCHAR	Yes
name	UserGroup		VARCHAR	Yes
name	ProductRelationshipGroup		MEDIUMTEXT	No
newTotal	OrderLog		FLOAT	No
newValue	OrderLog		TEXT	No
oldTotal	OrderLog		FLOAT	No
oldValue	OrderLog		TEXT	No
orderID	ExpressCheckout	FK	INTEGER	Yes
orderID	OrderLog	FK	INTEGER	No
orderID	OrderNote	FK	INTEGER	Yes
orderID	Shipment	FK	INTEGER	Yes
orderID	Transaction	FK	INTEGER	Yes
parentNodeID	Category	FK	INTEGER	No
parentTransactionID	Transaction	FK	INTEGER	No
password	User		CHAR	Yes
paymentData	ExpressCheckout		TEXT	No
perItemCharge	ShippingRate		FLOAT	No
perKgCharge	ShippingRate		FLOAT	No
phone	UserAddress		VARCHAR	No
position	ProductRelationship		INTEGER	No
position	ProductFile		INTEGER	No
position	FilterGroup		INTEGER	No
position	NewsPost		INTEGER	No
position	ProductRelationshipGroup		INTEGER	No
position	StaticPage		INTEGER	No
position	ShippingService		INTEGER	No
position	SpecFieldValue		INTEGER	No
position	ProductFileGroup		INTEGER	No
position	Tax		INTEGER	No
position	SpecField		INTEGER	No
position	Filter		INTEGER	No
position	CategoryImage		INTEGER	No
position	ProductImage		INTEGER	No
position	DeliveryZone		INTEGER	No
position	SpecFieldGroup		INTEGER	No
position	Currency		INTEGER	No
postalCode	UserAddress		VARCHAR	No
price	ProductPrice		NUMERIC	Yes
price	OrderedItem		FLOAT	No
priceCurrencyID	OrderedItem		CHAR	No
pricePrefix	Currency		TEXT	No
priceSuffix	Currency		TEXT	No
productFileGroupID	ProductFile	FK	INTEGER	No
productID	ProductPrice	PK,FK	INTEGER	Yes
productID	ProductFile	FK	INTEGER	No
productID	ProductReview	FK	INTEGER	No
productID	SpecificationStringValue	PK,FK	INTEGER	Yes
productID	SpecificationDateValue	PK,FK	INTEGER	Yes
productID	OrderedItem	FK	INTEGER	Yes
productID	ProductFileGroup	FK	INTEGER	No
ProductID	ProductRelationshipGroup	FK	INTEGER	No
productID	SpecificationNumericValue	PK,FK	INTEGER	Yes
ProductID	ProductRelationship	PK,FK	INTEGER	Yes
productID	ProductImage	FK	INTEGER	Yes
productID	SpecificationItem	PK,FK	INTEGER	Yes
productRelationshipGroupID	ProductRelationship	FK	INTEGER	No
rangeDateEnd	Filter		DATE	No
rangeDateStart	Filter		DATE	No
rangeEnd	Filter		FLOAT	No
rangeStart	Filter		FLOAT	No
rangeType	ShippingService		TINYINT	No
rate	TaxRate		FLOAT	No
rate	Currency		FLOAT	No
rating	Product		FLOAT	No
realAmount	Transaction		FLOAT	No
realCurrencyID	Transaction		CHAR	No
relatedProductID	ProductRelationship	PK,FK	INTEGER	Yes
reservedCount	Product		FLOAT	No
reservedProductCount	OrderedItem		FLOAT	No
rgt	Category		INTEGER	No
roleID	AccessControlAssociation	FK	INTEGER	Yes
salesRank	Product		INTEGER	No
serviceClassName	DeliveryZoneRealTimeService		VARCHAR	No
shipmentID	ShipmentTax	FK	INTEGER	Yes
shipmentID	OrderedItem	FK	INTEGER	No
shipping	CustomerOrder		TEXT	No
shippingAddressID	CustomerOrder	FK	INTEGER	No
shippingAmount	Shipment		FLOAT	No
shippingServiceData	Shipment		TEXT	No
shippingServiceID	Shipment	FK	INTEGER	No
shippingServiceID	ShippingRate	FK	INTEGER	No
shippingSurchargeAmount	Product		NUMERIC	No
shippingWeight	Product		NUMERIC	No
shortDescription	Product		MEDIUMTEXT	No
sku	Product		VARCHAR	Yes
specFieldGroupID	SpecField	FK	INTEGER	No
specFieldID	SpecificationNumericValue	PK,FK	INTEGER	Yes
specFieldID	FilterGroup	FK	INTEGER	Yes
specFieldID	SpecificationItem	PK,FK	INTEGER	Yes
specFieldID	SpecificationDateValue	PK,FK	INTEGER	Yes
specFieldID	SpecificationStringValue	PK,FK	INTEGER	Yes
specFieldID	SpecFieldValue	FK	INTEGER	No
specFieldValueID	SpecificationItem	PK,FK	INTEGER	Yes
stateID	UserAddress	FK	INTEGER	No
stateID	DeliveryZoneState	FK	INTEGER	No
stateName	UserAddress		VARCHAR	No
status	CustomerOrder		TINYINT	No
status	Shipment		TINYINT	No
stockCount	Product		FLOAT	No
subdivisionType	State		VARCHAR	No
subtotalPercentCharge	ShippingRate		FLOAT	No
subtotalRangeEnd	ShippingRate		FLOAT	No
subtotalRangeStart	ShippingRate		FLOAT	No
taxAmount	Shipment		FLOAT	No
taxID	TaxRate	FK	INTEGER	No
taxRateID	ShipmentTax	FK	INTEGER	Yes
text	NewsPost		MEDIUMTEXT	No
text	ProductReview		TEXT	No
text	OrderNote		TEXT	No
text	StaticPage		MEDIUMTEXT	No
time	NewsPost		TIMESTAMP	No
time	OrderLog		TIMESTAMP	No
time	Transaction		TIMESTAMP	No
time	OrderNote		TIMESTAMP	Yes
title	ProductReview		VARCHAR	No
title	NewsPost		MEDIUMTEXT	No
title	ProductFile		MEDIUMTEXT	No
title	StaticPage		MEDIUMTEXT	No
title	CategoryImage		MEDIUMTEXT	No
title	ProductImage		MEDIUMTEXT	No
totalAmount	CustomerOrder		FLOAT	No
totalProductCount	Category		INTEGER	Yes
trackingCode	Shipment		VARCHAR	No
type	Product		TINYINT	No
type	SpecField		SMALLINT	No
type	Transaction		TINYINT	Yes
type	OrderLog		TINYINT	No
URL	Product		TINYTEXT	No
userAddressID	BillingAddress	FK	INTEGER	Yes
userAddressID	ShippingAddress	FK	INTEGER	Yes
userGroupID	User	FK	INTEGER	No
userGroupID	AccessControlAssociation	FK	INTEGER	Yes
userID	CustomerOrder	FK	INTEGER	No
userID	ProductReview	FK	INTEGER	No
userID	BillingAddress	FK	INTEGER	Yes
userID	ShippingAddress	FK	INTEGER	Yes
userID	OrderLog	FK	INTEGER	No
userID	Transaction	FK	INTEGER	No
userID	OrderNote	FK	INTEGER	Yes
value	SpecFieldValue		MEDIUMTEXT	No
value	SpecificationDateValue		DATE	No
value	SpecificationStringValue		MEDIUMTEXT	No
value	SpecificationNumericValue		FLOAT	No
valuePrefix	SpecField		MEDIUMTEXT	No
valueSuffix	SpecField		MEDIUMTEXT	No
voteCount	Product		INTEGER	No
voteSum	Product		INTEGER	No
weightRangeEnd	ShippingRate		FLOAT	No
weightRangeStart	ShippingRate		FLOAT	No