Database layout

[phppup]

Can anyone offer some pointers for my next endeavor?

I want to create a platform for a small business that will log into when a receptionist answers a call.

The purpose is for management to track the calls and production, and ensure that customers get a timely callback (when necessary).

I've was thinking that the db would be best with two branches.

The first would simply be for inputs of id, name, number, and reason for call with a checkbox for "callback required."

The second would be more of an admin accessible table where each call's id would be the link for recording "follow up" call notes and outcomes with a "resolved" checkbox.

I'm sure there are fellow Freakers with more experience than me, so any advice or templates would be appreciated.

[gizmola]

This sounds like the use cases that people use CRM's for.

 

[sbrothy]

If you want to do it manually RDBMS-style what about somethnig along the lines of (MYSQL-pseudocode):

 

DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR NOT NULL
	adress VARCHAR NOT NULL
	[...]
);


DROP TABLE IF EXISTS customers;
CREATE TABLE IF NOT EXISTS customers (
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR NOT NULL
	adress VARCHAR NOT NULL
	phone VARCHAR NOT NULL UNIQUE
	[...]
);

DROP TABLE IF EXISTS tickets;
CREATE TABLE IF NOT EXISTS tickets (
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	customer_id BIGINT NOT NULL,
	employee_id BIGINT NOT NULL,
	topic VARCHAR NOT NULL,
	annwer VARCHAR NOT NULL,
	created TIMESTAMP NOT NULL,
	CONSTRAINT fk_customer_id FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT fk_employee_id FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE CASCADE ON UPDATE CASCADE
);

 

This is just a quick draft off the top of my head, but it would enable you to keep track of how old the ticket is, which employee looked at it last and of course you'd want to add some BOOLEAN to mark whether it's resolved or not. 3 tables probably isn't enough and foreign key would perhaps have to go several ways, but there lies the fun!

I like doing things from scratch (read: making life hard for myself), but then again I'm retired and just want a challenge.

 

Good luck!