Hi all, i'm looking at putting together a system that keeps a record of my customers and their orders. I want the system to produce many queries, but I was wondering what kind of database setup and relationships would I need?

 

Clients Table

Services Table

Orders Table

 

How would I like them to produce as many queries as possible?

 

Thanks

Could you explain what you mean by as many queries as possible?

Well I would like to produce the following.

 

Produce an invoice per order

Allow the system to alert me when their service is due to expire

 

that kind of thing

You need to plan out what fields you want to store, then you normalise the structure to Third normal form.

 

If you post a proposed structure, I can help normalise.

thanks mate ok ill work on that and get back to you, many thanks mate

Hi Mate

 

First off, what language (PHP, ASP?) are you coding in, and what database server (MSSQL, MYSQL?)

 

T

hey mate,

 

im using php oop and mysql.

 

The tables I have so far are customer and services. I have joined them using php's JOIN LEFT instead of using a foreign key. Is that OK to use?

 

<?php
error_reporting(0);
require 'db/connect.php';
require 'functions/security.php';

// the stored records we want to display
$records = array();

// check if form has data entered
if (!empty($_POST)) {
	if (isset($_POST['first_name'], $_POST['last_name'], $_POST['website'], $_POST['start_date'], $_POST['service'])) {
		$first_name = trim($_POST['first_name']);
		$last_name = trim($_POST['last_name']);
		$website = trim($_POST['website']);
		$start_date = date('Y-m-d', strtotime($_POST['start_date']));
		$service = ($_POST['service']);
		
		if (!empty($first_name) && !empty($last_name) && !empty($website) && !empty($start_date) && !empty($service)) {
			$insert = $db->prepare("INSERT INTO customer (first_name, last_name, website, start_date, service) VALUES (?, ?, ?, ?, ?)");
			$insert->bind_param('ssssi', $first_name, $last_name, $website, $start_date, $service);
			
			if ($insert->execute()) {
				header('Location: index.php');
				die();
			}
		}
	}
}

// result of our database query
if ($results = $db->query("SELECT customer.*, services.service_name as service FROM customer LEFT JOIN services ON customer.service = services.id")) {
	if ($results->num_rows) {
		while ($row = $results->fetch_object()) {
			$records[] = $row;
		}
		$results->free();
	}
}
?>

<!DOCTYPE html>
<html>
<head>
<title>add customer</title>
</head>

<body>

<table>
	<tr>
		<td><a href="index.php">dashboard</a></td>
		<td><a href="add.php">add customer</a></td>
		<td><a href="">delete customer</a></td>
		<td><a href="">edit customer</a></td>
	</tr>
</table>

	<h3>Customer</h3>
	<?php
	if (!count($records)) {
		echo 'No records';
	}
	else {
	?>
	<table>
		<thead>
			<tr>
				<th>First Name</th>
				<th>Last Name</th>
				<th>Website</th>
				<th>Start Date</th>
				<th>Service</th>
			</tr>
		</thead>
		<tbody>
			<?php
				foreach ($records as $r) {
			?>
			<tr>
				<td><?php echo escape($r->first_name); ?></td>
				<td><?php echo escape($r->last_name); ?></td>
				<td><?php echo escape($r->website); ?></td>
				<td><?php echo escape($r->start_date); ?></td>
				<td><?php echo escape($r->service); ?></td>
			</tr>
			<?php
				}
			?>
		</tbody>
	</table>
	<?php		
	}
	?>
	
	<hr>
	
	<form action="" method="post">
		<div class="field">
			<label for="first_name">First Name</label>
			<input type="text" name="first_name" id="first_name" autocomplete="off">
		</div>
		<div class="field">
			<label for="last_name">Last Name</label>
			<input type="text" name="last_name" id="last_name" autocomplete="off">
		</div>
		<div class="field">
			<label for="website">Website</label>
			<input type="text" name="website" id="website" autocomplete="off">
		</div>
		<div class="field">
			<label for="start_date">Start Date</label>
			<input type="text" name="start_date" id="start_date" autocomplete="off">
		</div>
		<div class="field">
			<label for="service">Service</label>
			<input type="text" name="service" id="service" autocomplete="off">
		</div>
		<input type="submit" value="Insert">
	</form>

</body>
</html>

Okay great!

 

Theres no need to set up any relationship settings / foreign keys etc, just using LEFT JOINS like you have is perfectly fine!

 

But INDEX's on important column which you search on to keep things quick.

 

e.g on your orders page im guessing you will have order_id (which will be primary key), and customer_id to link the order to a customer. Because you will be using that field in your queries to find all orders from a particular customer etc, make sure that field has an INDEX.

 

Hope this helps

 

Cheers

T

Many thanks mate, much appreciated