2.10SQL Statements for Creating Tables

CREATE TABLE [dbo].[AccessLog](

[RcdID] [int] IDENTITY(1,1) NOT NULL,

[TerminalSerialNo] [char](20) NULL,

[CardID] [char](20) NULL,

[CustomerID] [char](16) NULL,

[LogDateTimeTerminal] [datetime] NULL,

[LogDateTerminal] [int] NULL,

[LogTimeTerminal] [int] NULL,

[LogDateTimeServer] [datetime] NULL,

[DateOfBirth] [datetime] NULL,

[TransactionType] [smallint] NOT NULL,

[ExpiryDate] [int] NULL,

[Description] [char](20) NULL,

[Quantity] [smallint] NULL,

[Amount] [decimal](17, 2) NULL,

[BalPrepaid] [decimal](17, 2) NULL,

[BonusAmount] [int] NULL,

[AccSpending] [decimal](17, 2) NULL,

[MemberInfo] [smallint] NULL,

[StaffID] [char](20) NULL,

[TerminalCode] [smallint] NULL,

[BranchCode] [smallint] NULL,

[CompanyCode] [smallint] NULL,

[TransactionID] [char](20) NULL,

[CardSN] [char](16) NULL,

[BalBonus] [int] NULL,

[BlacklistVersion] [datetime] NULL,

[TransactionCounter] [int] NULL,

[RefCode] [int] NULL,

[ProposedExpiryDate] [int] NULL,

[Age][int] NULL

) ON [PRIMARY];

AccessLog	The transaction log which store all access log sent from Terminals
RcdID	An auto increment field.
TerminalSerialNo	The serial number of the Terminal where this log is from
CardID	The CardID of the card involved in this transaction 0-4294967295
CustomerID	The CustomerID of the card involved in this transaction. 0-9999999999999999
LogDateTimeTerminal	The time when the transaction is done on the Terminal based on Terminal local time.
LogDateTerminal	The integer representation of the date value of LogDateTimeTerminal. It is created for the convenience of query. Format: yyyymmdd
LogTimeTerminal	The integer representation of the time value of LogDateTimeTerminal. It is created for the convenience of query. Format: hhmmss
LogDateTimeServer	The time when the server receive this transaction
DateOfBirth	The date of birth of the member of the card involved in this transaction.
TransactionType	The code which identifies the transaction type.
ExpiryDate	The expiry date of the card involved in this transaction. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99.
Description	The item code of the gift of product selected from iBonus Display
Quantity	The quantity of the redemption transaction 1-31
Amount	The amount of money changed in this transaction 0-8388607.00
BalPrepaid	The amount of money of the card after this transaction.
BonusAmount	The amount of bonus point changed in this transaction. 0-16777215
AccSpending	The amount of accumulated spending in the card in this transaction. 0-2147483647.00
MemberInfo	The flag which represent the user’s answer in the multiple choice question of iBonus Display
StaffID	The staffID of the staff who is responsible for this transaction.
TerminalCode	The Terminal Code of the Terminal
BranchCode	The Branch Code of the Terminal
CompanyCode	The Company Code of the Terminal
TransactionID	The transaction ID sent from POS (cashier). It is used for relaying the iBonus transaction with POS transaction.
CardSN	The serial number of the card involved in this transaction.
BalBonus	The Bonus point in the card after this transaction.
BlacklistVersion	The version of blacklist saved in iBonus Terminal when this transaction takes place.
TransactionCounter	The number which serializes the transaction of each member card.
RefCode	The reference code used to identify the renew, replace and repair transactions.
ProposedExpiryDate	The expiry date calculated by the Terminal during renew.
Age	The age of the customer/member derived from his or her birthday and LogDateTimeTerminal. It is set as 0 if birthday is not defined.

CREATE TABLE [dbo].[Member](

[CardSN] [char](16) NOT NULL,

[CardID] [char](20) NULL,

[CustomerID] [char](16) NULL,

[MemberInfo] [smallint] NULL,

[DateOfBirth] [datetime] NULL,

[ExpiryDate] [int] NULL,

[BalBonus] [int] NULL,

[AccSpending] [decimal](17, 2) NULL,

[BalPrepaid] [decimal](17, 2) NULL,

[LastVisit] [datetime] NULL,

[Blacklisted] [bit] NULL,

[Replaced] [bit] NULL,

[CustomerIDDuplicated] [bit] NULL,

[TransactionCounter] [int] NULL,

[ProposedExpiryDate] [int] NULL,

CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED

([CardSN] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];

Member	The table which stores the detail of all member cards and brand new cards.
CardID	The CardID of the card. 0-4294967295
CustomerID	The CustomerID of the card. 0-9999999999999999
DateOfBirth	The date of birth of the member of the card involved in this transaction.
ExpiryDate	The expiry date of the card. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99.
BalPrepaid	The amount of money of the card.
AccSpending	The amount of accumulated spending in the. 0-2147483647.00
MemberInfo	The flag which represent the user’s answer in the multiple choice question of iBonus Display
CompanyCode	The Company Code of the Terminal
TransactionID	The number which serialize the transaction of the member card.
CardSN	The serial number of the card.
BalBonus	The Bonus point in the card.
LastVisit	The time when this card last did transaction.
Blacklisted	The flag stating if this card is blacklisted
Replaced	The flag stating if this card is replaced
ProposedExpiryDate	The new expiry date requested during renew.

CREATE TABLE [dbo].[Staff](

[CardID] [char](20) NULL,

[CardSN] [char](16) NOT NULL,

[StaffName] [char](16) NULL,

[StaffInfo] [char](16) NULL,

[Blacklisted] [bit] NULL,

CONSTRAINT [PK_Staff] PRIMARY KEY CLUSTERED

([CardSN] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];

Staff	The table which stores the detail of all staff cards.
CardID	The CardID of the card. 0-4294967295
CardSN	The serial number of the card.
StaffName	Not Used
StaffInfo	Not Used
Blacklisted	The flag stating if this card is blacklisted

CREATE TABLE [dbo].[Terminals](

[SerialNo] [char](20) NOT NULL,

[StatusCode] [smallint] NULL,

[Warning] [smallint] NULL,

[TimeLastTransaction] [datetime] NULL,

[TimeLastVisit] [datetime] NULL,

[UsingBlacklistVer] [datetime] NULL,

[IPAddress] [char](15) NULL,

[BranchCode] [smallint] NULL,

[TerminalCode] [smallint] NULL,

[Description] [char](40) NULL,

CONSTRAINT [PK_Terminals] PRIMARY KEY CLUSTERED

([SerialNo] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];

Terminals	The table which stores the status, timing and info about every terminal.
SerialNo	The serial number of the Terminal
StatusCode	The status of the Terminal. active = 1 inactive = 2
Warning	The warning which states if there is anything abnormal about the terminal Can be the OR of the followings: Ok = 0 Exceed last visit = 1 Exceed transaction log =2 Exceed linger request =4 Duplicated branch code and terminal Code = 8 Blacklist not update = 16
TimeLastTransaction	The time when the Terminal sends back transaction.
TimeLastVisit	The time when the Terminal visit server.
TerminalCode	The Terminal Code of the Terminal
BranchCode	The Branch Code of the Terminal
CompanyCode	The Company Code of the Terminal
IPAddress	The IP address that the server recognize the Terminal as.
UsingBlacklistVersion	The blacklist version which is in the terminal.
Description	The text which describes the terminal.

CREATE TABLE [dbo].[RenewRequests](

[TerminalSerialNo] [char](20) NOT NULL,

[RefCode] [int] NOT NULL,

[CardSN] [char](16) NOT NULL,

[Status] [smallint] NULL,

[TimeChgState] [datetime] NOT NULL,

[ProposedExpiryDate] [int] NULL,

[RecordExpiryDate] [datetime] NULL,

CONSTRAINT [PK_RenewRequests] PRIMARY KEY CLUSTERED

(

[TerminalSerialNo] ASC,

[RefCode] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];

RenewRequests	The table which stores the detail of all renew request.
TerminalSerialNo	The serial number of the request originating terminal
RefCode	The ref code the which refer to the request
Status	The approval status of the request
TimeChgState	The time when the status is last changed
CardSN	The serial number of the card
ProposedExpiryDate	The new expiry date generated in the renew request operation.
RecordExpiryDate	The expiry date of this record.

CREATE TABLE [dbo].[ReplaceRequests](

[TerminalSerialNo] [char](20) NOT NULL,

[RefCode] [int] NOT NULL,

[CardSN] [char](16) NULL,

[Status] [smallint] NULL,

[TimeChgState] [datetime] NOT NULL,

[RequiredBlacklistVersion] [datetime] NULL,

[ExpiryDate] [int] NULL,

[CardID] [char](20) NULL,

[AccSpending] [decimal](17, 2) NULL,

[MemberInfo] [smallint] NULL,

[DateOfBirth] [datetime] NULL,

[BalBonus] [int] NULL,

[BalPrepaid] [decimal](17, 2) NULL,

[CustomerID] [char](16) NULL,

[TransactionCounter] [int] NULL,

[RecordExpiryDate] [datetime] NULL,

CONSTRAINT [PK_ReplaceRequests] PRIMARY KEY CLUSTERED

([TerminalSerialNo] ASC,

[RefCode] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];

ReplaceRequests	The table which stores the detail of all replace request.
TerminalSerialNo	The serial number of the request originating terminal
RefCode	The ref code the which refer to the request
CardID	The CardID of the card. 0-4294967295
Status	The approval status of the request
TimeChgState	The time when the status is last changed
RequiredBlacklistVersion	The blacklist version which the terminal is required to have before this request can be approved.
CustomerID	The CustomerID of the card. 0-9999999999999999
DateOfBirth	The date of birth of the member of the card involved in this transaction.
ExpiryDate	The expiry date of the card. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99.
BalPrepaid	The amount of money of the card.
AccSpending	The amount of accumulated spending in the. 0-2147483647.00
MemberInfo	The flag which represent the user’s answer in the multiple choice question of iBonus Display
CompanyCode	The Company Code of the Terminal
TransactionID	The transaction ID sent from POS (cashier). It is used for relaying the iBonus transaction with POS transaction.
CardSN	The serial number of the card.
TransactionCounter	The counter which serialize the member card transactions
BalBonus	The Bonus point in the card.
RecordExpiryDate	The expiry date of this record.

CREATE TABLE [dbo].[RepairRequests](

[TerminalSerialNo] [char](20) NOT NULL,

[RefCode] [int] NOT NULL,

[CardSN] [char](16) NULL,

[Status] [smallint] NULL,

[TimeChgState] [datetime] NULL,

[ExpiryDate] [int] NULL,

[CardID] [char](20) NULL,

[AccSpending] [decimal](17, 2) NULL,

[MemberInfo] [smallint] NULL,

[DateOfBirth] [datetime] NULL,

[BalBonus] [int] NULL,

[BalPrepaid] [decimal](17, 2) NULL,

[CustomerID] [char](16) NULL,

[TransactionCounter] [int] NULL,

[RecordExpiryDate] [datetime] NULL,

CONSTRAINT [PK_RepairRequests] PRIMARY KEY CLUSTERED

([TerminalSerialNo] ASC,

[RefCode] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];

RepairRequests	The table which stores the detail of all repair request.
TerminalSerialNo	The serial number of the request originating terminal
RefCode	The ref code the which refer to the request
CardID	The CardID of the card. 0-4294967295
Status	The approval status of the request
TimeChgState	The time when the status is last changed
CustomerID	The CustomerID of the card. 0-9999999999999999
DateOfBirth	The date of birth of the member of the card involved in this transaction.
ExpiryDate	The expiry date of the card. If the expiry date is in last visit mode, the format is yyyymmdd. If the expiry date isn’t set in the card it will be zero. If the expiry date is fixed month mode the format is yyyymm99.
BalPrepaid	The amount of money of the card.
AccSpending	The amount of accumulated spending in the. 0-2147483647.00
MemberInfo	The flag which represent the user’s answer in the multiple choice question of iBonus Display
CompanyCode	The Company Code of the Terminal
CardSN	The serial number of the card.
TransactionCounter	The counter which serialize the member card transactions
BalBonus	The Bonus point in the card.
RecordExpiryDate	The expiry date of this record.