MySQL:Hire Co:DDL:Accounts

Introduction: Sales, Hire and Repair Company - Creating the Accounts tables
On this page we will look at how to implement a 1-to-many relationship in MySQL.

Developing a data dictionary
Before we look at creating the tables in MySQL, we need to do some analysis and work out what is required.

For the accounts we will need to have customers. Each customer can have many transactions, for example, they can be invoiced (after they hire goods we can work out how much to charge them), make payments (for example: if they hire a trailer you might ask for an initial payment, or they will want to pay the invoice), receive credits, pay a deposit.

Customer entity
 * For the customers you will want to store:
 * An identifier, Name (First and Last), Salute (Mr, Mrs, etc), Contact details (Address, Phone, email),Customer details (Discount rate, notes)

Transaction entity
 * For the transaction you will want to store:
 * An identifier, Type (Payment, Invoice, Deposit, Credit), Date, Amount, Notes.
 * A way to connect the transaction to a customer.

Entity Relationships
Fully attributed ERD for Accounts


 * Business rules
 * A client makes 0, 1 or many transactions
 * A transaction is made by one (and only one) client.

Table design




Create the database and tables and add some data
We will now add the following data to the client and transaction tables



Create the following text file and call it Hire_Acc1.sql

Now run this script in MySQL to test it is all working

Now add the following to the script file Hire_Acc2.sql

Now run this script in MySQL

Displaying the data using DQL and a DML Update
Create the following text file and call it Hire_Acc3.sql

Now run the script (SOURCE) to see that all is working.

Trouble shooting

 * 1) Check that the data types for both Primary and Foreign keys are the same.
 * 2) Check that the tables are created in the correct order (e.g. Client before Transactions)
 * 3) Check tables are deleted in the correct order (e.g. Transaction before Client)

Additional tasks

 * 1) You realised that you have not included a phone or email address for the client. Add both of these fields and include in the insert.