CODEWITHSUNDEEP
mysqldatabasedatabase-design
shin
shin

Reputation: 32721

Tracking system database structure

I am creating a simple customer support tracking system with PHP/MySQL

Functions are followings.

  1. An admin can CRUD an customer.

  2. An admin can add points/credits.: 60points (60min) etc.

  3. An admin can enter the work details, date, time duration, points and display these and point(time) remaining.

The question is how to structure the tables.

I have the customer table which is the easiest part.

CREATE TABLE IF NOT EXISTS `web_customer` (
  `customer_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `password` varchar(50) NOT NULL,
  `company_name` varchar(50) NOT NULL,
  `customer_name` varchar(150) NOT NULL,
  `phone_number` int(10) unsigned NOT NULL,
  `email` varchar(50) NOT NULL,
  `address` varchar(50) NOT NULL,
  `city` varchar(50) NOT NULL,
  `post_code` int(10) unsigned NOT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

I am hoping someone gives me good suggestion or examples or resources.

Upvotes: 0

Views: 247

Answers (2)

Magician
Magician

Reputation: 2113

You need to introduce another tables. Security table, admin table, and job table, which will relate to the customer table. I suggest you learn relational database first, and then design models for your need.

Upvotes: 0

Stephen Collins
Stephen Collins

Reputation: 3653

It sounds like this is (one of) your first forays into relational database design. I would suggest a book: Pro SQL Server 2000 Database Design by Louis Davidson (Here is is on Amazon). The implementation details are specific to SQL Server 2000, but the entire first half of the book is a great, easy to understand tutorial of relational database design; it covers how to make your design reflect your requirements, every Normalization rule, Denormalization, and the best ways to go about designing your database before even touching a computer. Its also relatively short, so you can get to designing pretty quickly.

Upvotes: 1

Related Questions