Table Schema

Table schema for the tables created for reference implementation.

WAREHOUSE

Field name

Field definition

Comments

W_ID

2*W unique IDs

W is the warehouse number

W_NAME

Variable text, size 10

W_STREET1

Variable text, size 20

W_STREET2

Variable text, size 20

W_CITY

Variable text, size 20

W_STATE

Fixed text, size 2

W_ZIP

Fixed text, size 9

W_TAX

Numeric, 4 digits

Sales tax

W_YTD

Numeric, 12 digits

Year to date balance

Keys:

  • Primary key: (W_ID)

DISTRICT

Field name

Field definition

Comments

D_ID

20 unique IDs

10 are populated per warehouse

D_W_ID

2*W unique IDs

D_NAME

Variable text, size 10

D_STREET1

Variable text, size 20

D_STREET2

Variable text, size 20

D_CITY

Variable text, size 20

D_STATE

Fixed text, size 2

D_ZIP

Fixed text, size 9

D_TAX

Numeric, 4 digits

Sales tax

D_YTD

Numeric, 12 digits

Year to date balance

D_NEXT_O_ID

10, 000 unique IDs

Unique IDs for next available order number

Keys:

  • Primary key (D_W_ID, D_ID)

  • Foreign key (D_W_ID) references (W_ID)

CUSTOMER

Field name

Field definition

Comments

C_ID

96, 000 unique IDs

3, 000 are populated per warehouse

C_D_ID

20 unique IDs

C_W_ID

2*W unique IDs

C_FIRST

Variable text, size 16

C_MIDDLE

Fixed text, size 2

C_LAST

Variable text, size 16

C_STREET1

Variable text, size 20

C_STREET2

Variable text, size 20

C_CITY

Variable text, size 20

C_STATE

Fixed text, size 2

C_ZIP

Fixed text, size 9

C_PHONE

Fixed text, size 16

C_SINCE

Date and time

The date of registration

C_CREDIT

Fixed text, size 2

Credit: "GC"=good credit, "BC"=bad credit

C_CREDIT_LIM

Numeric, 12 digits

C_DISCOUNT

Numeric, 4 digits

C_BALANCE

Signed numeric, 12 digits

C_YTD_PAYMENT

Numeric, 12 digits

C_PAYMENT_CNT

Numeric, 4 digits

C_DELIVERY_CNT

Numeric, 4 digits

C_DATA

Variable text, size 500

For remarks

Keys:

  • Primary key (C_W_ID, C_D_ID, C_ID)

  • Foreign key (C_W_ID, C_D_ID) references (D_W_ID, D_ID)

HISTORY

Field name

Field definition

Comments

H_C_ID

96, 000 unique IDs

H_C_D_ID

20 unique IDs

H_C_W_ID

2*W unique IDs

H_D_ID

20 unique IDs

H_W_ID

2*W unique IDs

H_DATE

Date and time

H_AMOUNT

Numeric, 6 digits

H_DATA

Variable text, size 24

Keys:

  • Primary key: None

  • Foreign key (H_C_W_ID, H_C_D_ID, H_C_ID) references (C_W_ID, C_D_ID, C_ID)

  • Foreign key (H_W_ID, H_D_ID) references (D_W_ID, D_ID)

NEW_ORDER

Field name

Field definition

Comments

N_O_ID

10, 000, 000 unique IDs

N_D_ID

20 unique IDs

NO_W_ID

2*W unique IDs

Keys:

  • Primary key (NO_W_ID, NO_D_ID, NO_O_ID)

  • Foreign key (NO_W_ID, NO_D_ID, NO_O_ID) references (O_W_ID, O_D_ID, O_ID)

ORDER

Field name

Field definition

Comments

O_ID

10, 000, 000 unique IDss

O_D_ID

20 unique IDs

O_W_ID

2*W unique IDs

O_C_ID

96, 000 unique IDs

O_ENTRY_D

Date and time

O_CARRIER_ID

10 unique IDs, or null

O_OL_CNT

From 5 to 15

O_ALL_LOCAL

Numeric, 1digit

Keys:

  • Primary key (O_W_ID, O_D_ID, O_ID)

  • Foreign key (O_W_ID, O_D_ID, O_C_ID) references (C_W_ID, C_D_ID, C_ID)

ORDER_LINE

Field name

Field definition

Comments

OL_O_ID

10, 000, 000 unique IDs

OL_D_ID

20 unique IDs

OL_W_ID

2*W unique IDs

OL_NUMBER

15 unique IDs

OL_I_ID

200,000 unique IDs

OL_SUPPLY_W_ID

2*W unique IDs

OL_DELIVERY_D

Date and time, or null

OL_QUANTITY

Numeric, 2 digits

OL_AMOUNT

Numeric, 6 digits

OL_DIST_INFO

Fixed text, size 24

Keys:

  • Primary key (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)

  • Foreign key (OL_W_ID, OL_D_ID, OL_O_ID) references (O_W_ID, O_D_ID, D_ID)

  • Foreign key (OL_SUPPLY_W_ID, OL_I_ID) references (S_W_ID, S_I_ID)

ITEM

Field name

Field definition

Comments

I_ID

200, 000 unique IDs

I_IM_ID

200, 000 unique IDs

I_NAME

Variable text, size 50

I_PRICE

Numeric, 5 digits

I_DATA

Variable text, size 50

Keys:

  • Primary key (I_ID)

STOCK

Field name

Field definition

Comments

S_I_ID

200, 000 unique IDs

S_W_ID

2*W unique IDs

S_QUANTITY

Numeric, 4 digits

S_DIST_01

Fixed text, size 24

S_DIST_02

Fixed text, size 24

S_DIST_03

Fixed text, size 24

S_DIST_04

Fixed text, size 24

S_DIST_05

Fixed text, size 24

S_DIST_06

Fixed text, size 24

S_DIST_07

Fixed text, size 24

S_DIST_08

Fixed text, size 24

S_DIST_09

Fixed text, size 24

S_DIST_10

Fixed text, size 24

S_YTD

Numeric, 8 digits

S_ORDER_CNT

Numeric, 4 digits

S_REMOTE_CNT

Numeric, 4 digits

S_DATA

Variable text, size 50

Keys:

  • Primary key (S_W_ID, S_I_ID)

  • Foreign key (S_W_ID) references (W_ID)

  • Foreign key (S_I_ID) references (I_ID)

Parent topic: Objects Created for the Reference Environment