My SQL Database Structure

Following is the structure of the MySQL database. It has five tables.

1. Customers
2. Payments
3. Sales
4. Profile
5. Users

Customers Table

Customer table uses to store customer details. Cus_ID is the primary key of this table. Sex uses Single bit char value to store one letter. M = Male, F = Female. Status also same with default value of A = Active, D = Deleted, B = Black Listed

Cus_ID          int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY

Cus_Date       date NOT NULL,

Cus_Name     varchar(120) NOT NULL,

Cus_NIC        varchar(10) NOT NULL,

Cus_Tel          varchar(10) NOT NULL,

Cus_Email     varchar(120) DEFAULT NULL,

Cus_Sex        char(1) DEFAULT NULL,

Cus_Add       text,

Cus_Status    char(1) NOT NULL DEFAULT 'A',

Payments Table

Payments table is to record customer payment details. P_Sale is a foreign key and is the primary key of Sales table. P_Type records the type of payment D = Down Payment, I = Installment

P_ID int(11) NOT NULL AUTO_INCREMENT,

P_Date date NOT NULL,

P_Sale int(11) NOT NULL,

P_Value decimal(10,2) NOT NULL,

P_Type char(1) NOT NULL,

P_Detail text,

PRIMARY KEY (P_ID)

Profile Table

This table uses to record company details.

Name varchar(40) NOT NULL,

Address text NOT NULL,

Telephone varchar(20) DEFAULT NULL,

PRIMARY KEY (Name)

Sales Table

This table records sales details.

  S_ID int(11) NOT NULL AUTO_INCREMENT,

  S_Date date NOT NULL,

  S_Customer int(11) NOT NULL,

  S_Item varchar(120) NOT NULL,

  S_Description text,

  S_Value decimal(10,2) NOT NULL,

  S_Installments smallint(6) NOT NULL,

  S_Installment_Value decimal(10,2) NOT NULL,

  S_Interest decimal(10,2) NOT NULL,

  S_Status char(1) NOT NULL DEFAULT 'A',

  PRIMARY KEY (S_ID)

Users Table

This table records system users details.

  User_ID smallint(6) NOT NULL AUTO_INCREMENT,

  User_Name varchar(20) NOT NULL,

  User_Password varchar(20) NOT NULL,

  User_Privileges text NOT NULL,

  PRIMARY KEY (User_ID)

Following the complete MySQL dump file to import database in to PHPMyAdmin.

-- phpMyAdmin SQL Dump

-- version 3.2.0.1

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Mar 05, 2011 at 10:31 AM

-- Server version: 5.1.36

-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: prezire_030311

--

-- --------------------------------------------------------

--

-- Table structure for table customers

--

CREATE TABLE IF NOT EXISTS customers (

  Cus_ID int(11) NOT NULL AUTO_INCREMENT,

  Cus_Date date NOT NULL,

  Cus_Name varchar(120) NOT NULL,

  Cus_NIC varchar(10) NOT NULL,

  Cus_Tel varchar(10) NOT NULL,

  Cus_Email varchar(120) DEFAULT NULL,

  Cus_Sex char(1) DEFAULT NULL,

  Cus_Add text,

  Cus_Status char(1) NOT NULL DEFAULT 'A',

  PRIMARY KEY (Cus_ID)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--

-- Dumping data for table customers

--

INSERT INTO customers (Cus_ID, Cus_Date, Cus_Name, Cus_NIC, Cus_Tel, Cus_Email, Cus_Sex, Cus_Add, Cus_Status) VALUES

(1, '2011-03-04', 'Susantha Herath', '813030608V', '0712105424', 'susanthahm@gmail.com', 'F', '195/5, Mahabuthgamuwa,\r\nAngoda', 'B');

-- --------------------------------------------------------

--

-- Table structure for table payments

--

CREATE TABLE IF NOT EXISTS payments (

  P_ID int(11) NOT NULL AUTO_INCREMENT,

  P_Date date NOT NULL,

  P_Sale int(11) NOT NULL,

  P_Value decimal(10,2) NOT NULL,

  P_Type char(1) NOT NULL,

  P_Detail text,

  PRIMARY KEY (P_ID)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--

-- Dumping data for table payments

--

INSERT INTO payments (P_ID, P_Date, P_Sale, P_Value, P_Type, P_Detail) VALUES

(4, '2011-03-05', 1, '3570.00', 'I', ''),

(2, '2011-03-04', 1, '15000.00', 'D', 'CHQ 455568545 (COM)');

-- --------------------------------------------------------

--

-- Table structure for table profile

--

CREATE TABLE IF NOT EXISTS profile (

  Name varchar(40) NOT NULL,

  Address text NOT NULL,

  Telephone varchar(20) DEFAULT NULL,

  PRIMARY KEY (Name)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--

-- Dumping data for table profile

--

INSERT INTO profile (Name, Address, Telephone) VALUES

('Prezire Software Solutions', '195/5, Kotikawatte', '0115752806');

-- --------------------------------------------------------

--

-- Table structure for table sales

--

CREATE TABLE IF NOT EXISTS sales (

  S_ID int(11) NOT NULL AUTO_INCREMENT,

  S_Date date NOT NULL,

  S_Customer int(11) NOT NULL,

  S_Item varchar(120) NOT NULL,

  S_Description text,

  S_Value decimal(10,2) NOT NULL,

  S_Installments smallint(6) NOT NULL,

  S_Installment_Value decimal(10,2) NOT NULL,

  S_Interest decimal(10,2) NOT NULL,

  S_Status char(1) NOT NULL DEFAULT 'A',

  PRIMARY KEY (S_ID)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--

-- Dumping data for table sales

--

INSERT INTO sales (S_ID, S_Date, S_Customer, S_Item, S_Description, S_Value, S_Installments, S_Installment_Value, S_Interest, S_Status) VALUES

(1, '2011-03-04', 1, 'Singer LED TV 21', 'Model No. E458785\r\nYear: 2010\r\nColor: Black / Silver', '34000.00', 12, '3570.00', '5.00', 'C');

-- --------------------------------------------------------

--

-- Table structure for table users

--

CREATE TABLE IF NOT EXISTS users (

  User_ID smallint(6) NOT NULL AUTO_INCREMENT,

  User_Name varchar(20) NOT NULL,

  User_Password varchar(20) NOT NULL,

  User_Privileges text NOT NULL,

  PRIMARY KEY (User_ID)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--

-- Dumping data for table users

--

INSERT INTO users (User_ID, User_Name, User_Password, User_Privileges) VALUES

(1, 'admin', 'go2010', 'Manage Inventory, Billing, Manage Users, '),

(3, 'Susantha', '123', 'Manage Inventory, Billing, ');