Question about Database structure

Question

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

Fuctions 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.

Thanks in advance.

Answer 1

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.

Answer 2

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.