I.T. Discussion Community!
-Collapse +Expand
DBA
Search DBA Group:

Advanced
-Collapse +Expand DBA Store
PRESTWOODSTORE

Prestwood eMagazine

August Edition
Subscribe now! It's Free!
Enter your email:

   ► KBRole-Based T...DBA & Data   Print This     
  From the May 2015 Issue of Prestwood eMag
 
DBA & Data:
Common Table Structures
 
Posted 17 years ago on 11/19/2002
Take Away: Reference of common table structures

KB100054

Note

Every business domain requires its own unique table structures. During the process of gathering requirements you'll need to gather what data needs to be stored. The table structures here are simply to be used as reference.

Common Table Structures

The tables that follow list some common table structures. Use these suggested database structures when you're not inspired or when you need a hand. An asterisk after the field name indicates that the field is part of the primary key.

Field Type Legend

$

Money field type (you can substitute a float with 2 decimals)

A

Character field (The number after represents the field width)

D

Date

F

Formatted large text field (supports RTF, the number following applies to Paradox and dBASE only)

G

Binary graphic field type (the number following applies to Paradox and dBASE only)

M

Large text field (the number following applies to Paradox and dBASE only)

T

Time Field type (no date in this type, you can substitute a DateTime field type or use a character field type)

Address book table.

Field Name (Type) Index(es)

SS# (A11)*

Name (A30) **

Address (A30)

City (A15)

State/Province (A2)

Zip (A10)

Phone (A15)

Born (D)

Age (N)

Notes (M10)

Picture (G0)

Area code table.

Field Name (Type) Index(es)

Area Code (A3)*

State (A2) **

Full State (A21)

Country (A30)

ATM transactions.

Field Name (Type) Index(es)

Transaction Number (N)*

Date (D) **

Amount ($)

Transaction Type (A16)

Customer table.

Field Name (Type) Index(es)

Customer No (N)*

Name (A30) **

Street (A30)

City (A15)

State/Prov (A20)

Zip/Postal Code (A10)

Country (A20)

Phone (A15)

First Contact (D)

Employee table.

Field Name (Type) Index(es)

SSN (A11)*

Department (A30)

Position (A30)

Desk Phone (A15)

Manager SSN (A11) **

Start Date (D)

Shift Start Time (T)

Shift End Time (T)

Salary (per year) ($)

Notes (F)

Picture (G)

Expenses table.

Field Name (Type) Index(es)

Expense No (+)*

Date (N) **

Type (A20)

Transaction Type (A20)

Transaction Number (N)

Comments (A20)

Cassette Label Template.

Field Name (Type) Index(es)

Label Set (A15)*

Label Number (S)*

Header (one line) (A70) **

Songs (up to three lines) (A255)

Side (one character) (A1)

Band name (or tape name) (A60)

Note (one line) (A70)

Inside label (A50)

Loans table.

Field Name (Type) Index(es)

Description (A20)*

Date of first Payment (D)

Loan Amount ($)

Interest Rate (N)

Term of Loan In Years (N)

Payments Per Year (N)

Total Payments (N)

Payment Amount ($)

Mail System table.

Field Name (Type) Index(es)

Status (A1)*

Time (T)*

To (A20)* **

Date (D) **

From (A20)

Subject (A50)

Message (M10)

FileName (A30)

Second address book table.

Field Name (Type) Index(es)

Last Name (A20)*

First Name (A15)*

Middle Initial (A1)*

Title (A30)

Company (A30) **

Address1 (A30)

Address2 (A30)

City (A15)

State (A3)

Zip (A10)

Phone Number (A15)

Work Number (A15)

Fax Number (A15)

Notes (F10)

Flexible phone number table for use with second address book.

Field Name (Type) Index(es)

Last Name (A20)*

First Name (A15)*

Middle Name (A15)*

Phone # Name (A15)* **

Phone Number (A15)

Phone Ext (A5)

Line items of an invoice.

Field Name (Type) Index(es)

Order No (N)*

Stock No (N)*

Selling Price ($)

Qty (N)

Line Total (M)

Line item look-up table (Orders detail look-up table).

Field Name (Type) Index(es)

Stock No (N)*

Desc (A20) **

Orders table.

Field Name (Type) Index(es)

Order No (N)*

Customer No (N)

Sale Date (D)

Ship Date (D)

Ship VIA (A7)

Total Invoice ($)

Amount Paid ($)

Balance Due ($)

Terms (A6)

Payment Method (A7)

States look-up table.

Field Name (Type) Index(es)

State (A2)*

Full State (A21)

Country (A30)

Stock or inventory table.

Field Name (Type) Index(es)

Stock No (N)*

Vendor No (N)

Equipment Class (A30)

Model (A20)

Part No (A15)

Description (A30)

Catalog Description (F10)

Qty (N)

List Price ($)

Vendors or companies.

Field Name (Type) Index(es)

Vendor No (N)*

Vendor Name (A30)

Street (A30)

City (A20)

State/Prov (A20)

Country (A15)

Zip/Postal Rt (A10)

Phone (A15)

FAX (A15)

Preferred (A3)

ZIP codes look-up table.

Field Name (Type) Index(es)

Zip code (A5)*

City (A25) **

State (A2) **


Comments

0 Comments.
Share a thought or comment...
 
Write a Comment...
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P1181A1
Enter key:
KB Post Contributed By Mike Prestwood:

Mike Prestwood is a drummer, an author, and creator of the PrestwoodBoards online community. He is the President & CEO of Prestwood IT Solutions. Prestwood IT provides Coding, Website, and Computer Tech services. Mike has authored 6 computer books and over 1,200 articles. As a drummer, he maintains play-drums.com and has authored 3 drum books. If you have a project you wish to discuss with Mike, you can send him a private message through his PrestwoodBoards home page or call him 9AM to 4PM PST at 916-726-5675 x205.

Visit Profile

 KB Article #100054 Counter
8310
Since 4/2/2008
Follow PrestwoodBoards on: 


©1995-2019 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech