I informed him of what my report would be based around and how I predicted a computer-based approach would likely be the most effective way to take the business. He agreed and said money was the main issue when it came to these improvements.
Q: What happens to the data the customer gives you?
The data is stored along with the customer’s previous records. It’s a basic folder which we order by the keeping the most recent jobs at the front. Any new customers have a new folder created and are placed in alphabetical order with the other folders.
If the data is something I might need to hand I record it in a journal I keep with me in the garage.
Q: How long do you keep the forms for each customer and does the folder system make them harder to find?
At the moment I still keep everything for each customer because often I may need to look back over previous jobs to remind me of what the customers situation is in terms of their vehicle and there history with the company. Also while the business is still small there is no issue with overcrowding of information.
It doesn’t really take long to find the info because it’s all organised properly and efficiently but it still takes time to leave a customer on hold while I sort out the data they want.
Q: This problem relates to the customers, is there anything else you think the customers would like to see the business change.
I think part of why this business has been as good as it has is because of the straightforward basic approach we use. Customers who don’t want to talk to a mechanic through about five other people can just have face-to-face meetings to discuss the problem and make the decisions around that. It’s a trust thing, I don’t think they mind the paper-based approach but they would probably prefer it to be a bit more professional overall, and a computer system would probably accomplish this.
Q: What type of information do you need to get relating to jobs that sometimes isn’t available?
If you mean things like how a certain car works and how to deal with certain problems then I usually just rely on instinct. Sometimes I consult the cars handbook but usually the problem isn’t too serious to need that.
Q: If a new computer based system was introduced would you like a specific display screen, featuring perhaps the company logo or a specific colour scheme?
Yes, if that’s possible it’ll just make it look a bit more professional but the company has no unique colour scheme so you would probably have to make one.
That’s fine; I’ll give you a rough copy to check over before I use it properly
Q: What paperwork would you like to keep in a new system?
Well the information the customer receives, things like receipts, bills and other notes and stuff, they’ll need to be paper because most of my customers don’t have access to computers. They prefer the simplistic approach so I don’t want to change my approach of keeping the customer happy with little involvement.
Q: Would you like shortcuts for various office jobs, would they speed up the processes?
Anything that’s simple enough for me to use effectively and also speeds up the process for customers would be great but it would have to be pretty simple for me to control it.
Q: Would you or your employees need training to use a new computerised system?
Some of the people I hire are pretty accustomed to computer systems but for me and especially my wife we would probably need a short introduction into what does what.
Q: Is there anything else you would like the system to do?
Well I’m open to suggestions really but I wouldn’t know what else would be possible. If you have other ideas just run them by me and probably approve them for this new system.
4.6 Current Data Structures
Low-level DFD
Booking a Job
This is a low level DFD highlighting the processes required when booking a job.
The owner has to consider both whether he can complete the job and also what it will cost the customer to pay for the job and any resources.
The pricing list and current job notes are stored on paper as a checklist to be calculated. This process takes time so therefore numerous customers have to be called back before they know the full details about a job.
All the paper-based information regarding each customer contains no specific reference that it is related to the company. Each piece is usually headed with the customers name and what the sheet will be referring to.
A great deal of input into the system comes from customer word of mouth. Often time’s the owner will arrange a one to one meeting with a customer to discuss exactly what they want done. The interview notes are recorded and discussed with the customer afterwards to make sure they have gathered all the necessary information.
4.7 Requirements Specification
Output requirements
Input Requirements
Hardware and Software Requirements
4.8 Agreement to Requirements
A copy of a letter I sent to the owner questioning whether these requirements were acceptable can be found in the Appendix.
The letter contains requests for the previously listed system requirements and is signed by the owner as conformation of there agreement.
4.9 Alternate Solutions
Analysis of the problems faced by this business has given me ideas for various solutions. Although a computer based approach seems a faster easier solution to the problem, other solutions must be examined to make sure the most efficient approach is used.
Solution 1- with the owner feeling that the current system is still fairly efficient it maybe most effective to make subtle changes to this original system so as not to alienate employees unfamiliar with more complex methods. Remaining with the paper based system saves the need for large investments in newer software and hardware as well as the inconvenience of setting the system up. The installation process could take a fair amount of time and disrupt customer relations, which are such a high principle to the company’s success.
The solution would involve updating the string techniques for customer data. For example more secure folders and a quicker access system to find customer data.
Each customer would be given a job identification number based on their personal data and the time when their job was ordered. This way the manager can prioritise the filing system to have the most recent jobs and best customers in a more accessible manor.
Another approach to improve the current system would be to include partial computerisation. This would be a system where a search device would contain records of the locations of customer information. By using the search tool it will direct the manager to exactly where the documents they require are stored. This could be a very effective technique for when the business’s popularity starts to grow because it will mean a lot more storage places will be being utilised and customer documentation will become harder to find. If the business does start to grow this way however, the storage spaces may become limited for the amount of information it needs to keep secured and min good condition.
Solution 2- with using a computer system seeming the most effective choice different solutions become available using various different programs and approaches.
A possible solution would be to use a spreadsheet document and incorporate user forms. This approach has its advantages because by creating a form system they can be customized greatly to incorporate the company information and make them as simple and accessible to the customer as possible. This is very important for this business because the system must not appear confusing to customers who have chosen because they want a simple solution they cannot get elsewhere.
Getting the customer more involved with the business’s processes may appeal to some customers more than others. If the customer feels they are making an input to the business and is familiar with the processes they may become more supportive and confident with the new system.
One of the biggest advantages of this approach to the manager is the spreadsheets ability to perform complex calculations quickly. This is a great improvement on the conventional method of a calculator and paper workings.
The downside to this is that it would require a fair amount of time-spent training before the manager could understand the formulas needed for what may become a complex system with more customers joining it all the time. Although the system could be set up by someone else leaving a simple input system, the manager still requires knowledge of the system in case any mistakes are made with the system during inputs and they need to correct it without bringing someone else in to fix it.
It is important that the system does not disrupt the manager’s relationship with his customers.
The biggest disadvantage wit this system however is that it would become very crowded and difficult to understand the more customers join the system. This is because spreadsheets do not work with a one to many relationship. Any customer who requested multiple jobs would have to have their name and their details entered into the system multiple times rather than having the jobs kept under the same name. This leads to a great deal of memory being used up by the system. With a very large amount of data the system may become slower because of the number of details being processed all at once.
Solution 3- with a spreadsheet having a number of more complex inputs such as formulas and action buttons a more practical approach would be a database system. The Relational Database system I would use has a one to many relationship so it would allow each customer with their individual identification to have access to all their previous job details.
The system will work by having multiple tables linked through a primary key. This primary key will be the customer ID. This ID will be present in tables containing the customer’s information, the job details and the pricing system.
The customer table will contain their name, contact details and personal information.
The job table will contain descriptions of the most recent job performed for that customer and a summary of all their previous jobs.
The price table will be a list of each customer’s bills for their jobs. Using the ID to look a customer up the prices of each job will appear broken down into various segments so it is clear to them how a total was reached.
In order to produce receipts quickly the system will also include a mail merge system linking the database to a word processor. Then through a button click the user can replicate the billing information and print it of for the customer. This method saves the time previously spent writing out receipt details such as the company name, data and job details. This information can now be saved in a word document and produced quickly in large numbers.
4.10 Limitations of Chosen Solution
From the three solutions described above I have chosen to use Solution 3-a database system. This is because it provides the most efficient use of a computer and allows a customer to have various jobs under their name without overcrowding the system. The database can be set up by me and modified to fit the companies needs addressed in my one to one interview. For example the company name and a new logo will be present on the input forms as well as a constant colour scheme throughout the system.
I chose it over an advancement to the current method because it is much to difficult to anticipate whether there would be enough room to store personal data if there was a sudden increase. Using the database means all the information can be kept together in a much more secure environment.
The solution I have chosen is in itself not perfect. Here are some of the limitations facing the current system.
- A fairly high level of instruction will be needed before the system can be implemented. With the amount of important data on the system it is vital that the manager and all his employees are fully aware of the security measures that have to be in place.
- While a small amount of customisation is possible, a database application contains far less than a spreadsheet or word application making it harder to accurately represent the company logo and colour schemes.
- For the database to work without any slowdown or technical problems it will need a fairly modern computer set up incorporating a high-speed modem and a large memory capacity. These specifications up the price of a computer and for a smaller business such as WillCare this larger amount of money is harder to come by without risking loosing company profits. The investment would be a risk because if business started to decline the company would be struggling to cover expenses.
- The database application is unable to print what the business requires straight from the tables e.c.t. Instead a word processor must be incorporated for a proper receipt to be produced.
Design
Nature of the Solution
5.1.1 Objectives
Input Objectives
Process Objectives
Output Objectives
5.1.2 System Structure
This is a brief outline of the how the new implemented system will work. The structure will be kept as basic as possible to benefit the inexperienced workforce.
A top down structure- Hierarchical Diagram
Design of Forms
All of the following forms and explanations are based on an outline of the system created in a spreadsheet document. By using this method I will be able to show the system to the owner of Willcare and get some feedback on what he thinks works well and what improvements he would like.
All forms within the database will use this same colour scheme, as it is a professional look that will also help customers and employees recognise that they are still in the system. I felt that because the probable users will be inexperienced it would be useful to include a brief description of what functions the buttons will have in the database.
The font specifications are Arial, size 10 with the table definition Menu using Berlin Sans at font size 18.
The Willcare name is presented in a word art technique modified in its colour scheme and size. The contrasting colours help it show up on the system and are another way to reassure the user they are still correctly using the system.
This is the customer form where the user will be able to add a new customer, search for an existing one or delete an old member. All the buttons are clearly labelled to explain their functions and I have maintained the same style of headings and logo used on the main menu. To make searching through current customers easier I have incorporated navigational arrows for quicker access and easier browsing. When considering the users abilities with computers I also decided to include a button to return to the Main Menu on each form. This will be useful because it will allow quicker access when the user wants to use a different part of the system.
Each customer’s details will be filled in the shown boxes and various pieces of the information will be validated to make sure it is correct within the laws of the system.
The most important aspect of this page will be the member ID. Each customer will be given a unique number that will be used to look him or her up through the system. The owner may feel, however that giving all his current customers a number for the system would alienate them and waste company time. Therefore I may only use member ID’s for new customers and use a different look up method for existing customers e.g. their D.O.B.
For the customers D.O.B entry I will use a combo box method so data input can be sped up.
This form also includes a link to “Add a Job”. This button will allow the user, once they have entered the details to go straight to the job form so they can choose what they want done and find out the estimated price. The post code is an important way to identify customers legitimately and will contain a validation making sure it is accurately filled in.
During my interview with the company owner I learned he would require the system to produce an output in the form of a receipt. To quickly produce the receipt using a mail merge technique the produce a receipt button will create the receipt for the job currently recognised in the form entry. If this is not the required job for the customer, the user can search for a different one using the view customer job button.
The maintenance menu includes a link to job prices and a supplier list. These are options that are likely to be scarcely used. This influenced my decision to include them in a maintenance menu. If the user becomes confused with the system they can check this important information and hopefully save customer time. The suppliers list does not belong in parts of the system the customer views as it will confuse them and overcrowd parts of the system.
A mechanics business has a very wide variety of jobs that may be required. It is therefore important that the user can add a description of a new job as well as have quick access to common ones.
When a new job is entered a full description can be added or just a shorthand. The shorthand is necessary for it to be stored under the common jobs. The price remains an estimate because all jobs are different and customers may have required more or less work depending on their situation and car. The date of order and the expected completion date will both use combo boxes and the date of order will be immediately validated to display the current date automatically. The add to outstanding list will be covered under reports design as is the create receipt button.
As the business grows they are likely to have numerous suppliers therefore I felt different search criteria would help speed up the process. One or both of the criteria can be entered and then searched for within the database. A supplier list will contain all the suppliers and products the company have used in the past. This is where the database will search based on the criteria.
This is an example of one of the tables data will be processed from. Each Job will have a unique ID to help searching through the database faster and more efficient.
Design of Reports
With a growing number of customers I felt a report covering the outstanding jobs for the owner would be very useful. I decided to return to the original white background so the businesses paperwork looks professional and is always as clear as possible. The logo remains as it helps to identify the company documents against those from customers and suppliers. Including the ID means the report will be created through a single button click in the database. Keeping these reports at hand will save backtracking to the computer system to see what jobs need to be done.
The button to add information to this report is located on the job form detailed earlier. The button will add the currently displayed job onto this report. The user can then decide to print the report with any amount of jobs on it.
This report keeps the same fundamentals of the previous one, those being the plain background and company logo.
This report will contain a list of all the customers currently on the system database. This will be useful for the manager because it will help him remember customer information while on the job. It can act as a quick reference for customer contact details so that they can be kept informed of developments in the business.
This is a copy of a company receipt. It will be created in the same manner as a report but will contain a query system so that prices can be worked out without the manager needing to do anything. The font has remained constant; however I have now included bold print for the totals and to highlight problems. This will make identifying key information easier for both customer and manager.
In the style of many receipts I have now included a contact number for any problems or questions.
Pressing the “create receipt” button found on the Job form will create this receipt.
The receipt must include all the details of the transaction including the individual jobs and there prices so that it is clear to the customers where the total is obtained from. This will also make any mistakes easier to identify.
Design of Mail Merged Letters
A standard letter required by the user is to cover jobs that are going to be overdue. The letter will be created in a word document through a mail merge button present in the database. The letter once produced will be printed and sent to customers to inform them of the delay and the expected date for the job to be finished by. This shows the customer that they are not being forgotten and the business values their custom. By pressing the mail merge button a word document will automatically open and the outline of the letter be filled with the necessary information. The information will be entered through the use of a code on the word document.
You can find an attached plan of the mail merge layout in the appendix.
Design of Data Capture Forms
As this is a mechanic business there is no need for any data capture forms because they cover the details entry for members and employees.
A data capture form is usually sent to these people to be filled in. The information gathered is then input onto the computer system.
All information of this sort is obtained straight from the customer or over the phone so this technique is an unnecessary approach.
Relationships
ERD- Entity Relationship Diagram
TRD- Table Relationship Diagram
Table Designs
Customer (Customer ID, First Name, Surname, Date of Birth, Address Line1, Address Line2, Telephone Number)
Request (Customer ID, Job ID, First Name, Surname, Job Title)
Job (Job ID, Job Title, Estimated Price, Date of Order, Expected Completion Date)
Order (Job ID, Part Name, Price, Supplier Name)
Part (Part Name, Supplier Name)
Query Designs
Generic Buttons
Within the database there are various buttons that will be considered generic. This means they have a single function that remains constant throughout the whole database. The “Main Menu” button presented in the corner of every form will return the user to the main menu no matter where they are in the database. The “Search” buttons found on numerous occasions in the database will always perform the same function of searching the database for the data present on the page.
As I am designing this system for a user with very limited knowledge I have included a small explanation of what various buttons will do before they are pressed. For example “Produce a Receipt” is written next to a button labelled “Go”. This helps reassure the user that they are doing what they want.
Start action: The Customer option button is selected.
The Customer Form Screen is displayed
Start action: The Report option button is selected
The Report Form Screen is displayed
Start action: The Maintenance option button is selected
The maintenance menu is displayed
Start action: Customer details are entered into the customer entry form and the “store customer” button is pressed
The customer’s details are stored in the corresponding database table- Customer_TBL
Start action: A customer is selected on the customer forms and the “edit details” button is pressed.
The form will become active and allow the details to be changed by the user
Start action: A customers details are present in the customer form and the “delete customer button is pressed
A message box will appear asking for confirmation of the deletion.
The “Yes” option is chosen from this message
The customer’s details are deleted
Start action: The “Add a Job” is pressed on the customer entry form
The customer form remains open and the Job entry form appears.
Start action: The < button is pressed
The previous record in the system is displayed
Start action: The > button is pressed
The next record in the system is displayed
Start action: The “find” button is pressed while a member ID is displayed on the form.
The system will search for the customer details and display those corresponding to the ID
If no member ID is present the user will be prompted to enter one by a pop up message.
Start action: The “Add to outstanding list” button is pressed after a jobs details are entered.
The current job will be added to a report list that can be printed of and taken away to help remember what jobs need doing when the user is away from the system.
Start action: The “create receipt” button is pressed
A receipt will be produced using a mail merge based on the currently displayed information.
The receipt will appear in a word document and include company details
Data Dictionary
System Security
The system will only ever need to be used by the one manager so with a single password access method and a lock system tool the system should be as secure as it needs to be. I will advise the manager to have a fairly long password that includes both text and numbers to make guessing the password very difficult. He is also advised to change the password on a regular basis to protect the system further.
Levels of security are not a big issue for Willcare because with a very small workforce everyone sends information straight to the manager or his wife to be dealt with. It is very unlikely these employees would need to use the system and on the off chance they can be shown the password for a short time before it is changed.
This method also saves the lower employees from having to learn about the system, and the system can be designed exactly as the manager wants without worrying about other employee’s use of the system.
Data that needs to be changed e.g. a supplier’s part pricing or new customer details such as an address change are to be dealt with by the manager because he must approve the changes before they take place. He can therefore keep the system to his individual specifications.
Data Security
This security is very important because the system is protecting personal customer data. If this were to be released by accident or due to lack of security the manager would face various consequences that would effectively ruin his business.
Backups will be made and updated at the end of every day.
The system will be stored on memory sticks and in early cases floppy disks.
Backups will be made of all areas of the database that contain personal data. Therefore it is best to save the database as a complete file. This will be fine while the business remains small but as the database grows separate backups may be needed due to the size of the file.
One backup will be stored with the manager at all times while a second will be kept at a separate location away from the system.
It will be the manager’s responsibility to backup the system daily.
In the event of data loss in the system the data will be backed up on a new computer system to avoid data corruption. It will remain the manager’s responsibility but he may decide to employ a more experienced technician to make sure all data is restored properly.
Intended Benefits
- A faster mode of access to customer data to help with negotiations over the phone. Provides a guide to the manager to help him recognise customers, their history with the company and location. Previously paper based records had to be found within a large number of files and were unclear when compared to a computer presentation. With the system in place the manager can enter the customers name and search for all the information they have on file about them.
- A more secure way to store and protect customer’s personal data. Previously the data was only kept in a locked cabinet which was still susceptible to be breakage. There was no way to backup this data without a huge loss of company time and resources. The new system will allow data to be locked with the use of a password that can be updated on regular occasions. Backups will be easy to make and the customer data protected to the highest degree possible.
- Faster and more accurate calculations come as a benefit of a computer system saving the manager the job of manually working out bills for customers and suppliers. This improves customer service and gives the business a more professional look. With a query system the calculations occur without any work involved and are constantly correct.
Limits and scope
The method I chose for the company is a vast improvement but still has its own limitations due to the fact that the size of a database is constantly growing and with the system the company is incorporating it may cause slight slowdown.
Limitations may only become apparent as the business grows because more and more customers mean more data and a strain on the system memory.
Another problem is that the system can only provide a certain amount of help to those unfamiliar with computer systems. A basic training is still needed to familiarise the new user(s) with the fundamentals of the system.
There are various functions that would have been of great use if the system had used a different type of program. For example a spreadsheet-based system would have provided easier parameters for formulas such as working out a customers final job price. It would also have been easier to design various menu screens and visual aids for the user due to the compatibility of the program for designing from scratch.
The requested company logo is likely to be limited to something pre designed by the computer and as such is likely to be of a slightly lower quality. This is because there is only a certain amount of pre made designs on the computer system. It would be feasible to use a scanner to include a paper-based design however this would require more equipment, a skilled artist and a longer timeframe. Due to the fact that the user does not have the required equipment it would seem a waste of finances to invest in one for a single task. Therefore the logo will have to use computer graphics and be modified as much as possible to fit the users demands.
This table shows that when the business begins to grow substantially and the customer count reaches that shown of around a hundred then the total size of all records will reach a very high memory, in this case 28580 bytes. If the system begins to struggle with an overload of information an upgrade is likely to be required. However from gathering information about the business through my interview and gaining an overall impression of its set-up and position I feel the chosen system will work efficiently and without problem for a fairly large amount of time.
Time Line
Software Development
Input, Output and processing printouts
Forms
Theses are what will be visible to the user and the customer when the system is implemented. It is therefore important that I take into account the requests the user made during the interview regarding the new system.
This is the main menu that will act as a base from which the other forms can be accessed. The user will be able to return to this menu at any time through a “main menu” button.
As this is screen most likely to be seen by the customers and user I have distinguished it with a different colour scheme from that of the other forms. I made sure the colours used were meeting the specifications of the user, in that they are clear and complimentary.
I have also taken into account the computer knowledge of the likely users and maintained the design I had early chosen of a brief explanation of what each button will allow the user to do. This will help the user become comfortable with the system and maintain their understanding of how it works.
The owner also requested that there be the company name present on all slides and I made it sure it is clear and remains a constant design throughout all the forms.
For example when a new customer is joining the user can choose the customer option and open the customer form for the customers themselves to fill in.
The customer form shown here is slightly different from my original design in that it is no longer a completely blue background. I felt this design had a more professional look while still maintaining the clearness apparent on the original design.
The company logo however remains the same as that from the main menu and this helps the customer realise they are still in the correct place and are safe to enter their personal details. The customer ID will automatically move onto the next number when a new customer is being entered.
The “Add Customer” button will take the user to the next available empty form and allow them to enter new data. All of the buttons are “command” buttons. They allow different functions relating to forms, reports and others.
The arrows will take the user either back or forward by one through the records. This fast access will allow quick searches for customers who’s ID is close to that currently displayed.
The “Delete Customer” button will clear the form of the current information it is displaying. This in turn will delete this customer from the Customer_TBL.
The “Store Customer” will save the currently displayed customer to the system allowing the user to add another customer knowing the previous customers records are safe.
To help the user and also to make the system more accessible I have included the “Add a Job” button. This will allow the user once they have entered the customer’s details to immediately fill in a job that might be being requested for the first time. It is important to do this because otherwise the job will not be present in the system and therefore cannot be linked to that customer. It saves the user the time of returning to the main menu and re-opening the Job form.
If the user does need to return to the main menu for any reason they can do so by using the main menu button which will remain constant in the same position on all forms here after.
All of the buttons from my original design have been recreated except for the “Edit Customer” button. This has been removed because the option to change the data is available to the user automatically.
This is the Job Form where the user can enter details of new jobs as well as deleting uncommon ones. This form also links the customers ID to the job they want performed, therefore the “Print a Receipt” button can now use both these pieces of information to print a receipt I have created in a report. The report will be shown later. The arrow buttons move forward and back through the job records and the “Main Menu” button returns the user to the initial starting screen.
This is the Part form, which allows the user to store information regarding parts they are supplied with. The same options are available with the button commands allowing them to add or delete a record as well as moving between records. The part ID is automatic like the other ID’s in the database because it is an auto number. When a new record is added it automatically moves onto the next number. The “Main Menu” button retains its job from previous forms.
This is the supplier form. It allows the user to enter and store all of their current suppliers and keep track with what parts they provide by having a Part’s ID linked to its supplier.
Keeping the add and delete options for these form records as well as the “Main Menu” button keeps the user confident whilst operating the system. The same look and design of buttons ensure there is no confusion as to functions. This is also why I chose text to label the majority of my buttons.
Queries
Data Structures
Tables
This is the Customer_TBL. It records the information listed under the field name column.
Customer ID is the primary key because it is the most unique data available. It will allow records to be found easily through a search when the database is complete. The auto number data type means the number will automatically appear in a sequential order when another customer is being added.
The date of birth field is classified as Date/Time under data type because this makes it easier to add the data in this field. Under the validation rule field you can see the validation that will prevent dates being entered after the current date.
The validation text that will appear when an incorrect date is entered is shown here also. The text is user friendly and makes it clear what they will have to change. It prevents incorrect data being entered in the database that will jeopardise the system.
Telephone Number data type is number as this makes sure no text can be accidentally entered into the database.
Post Code remains a text data type because it incorporates both numerical and text entries.
Details of these fields and their validations can be seen in the data dictionary on the following page.
For the postcode field I have included an input mask shown on the previous screen shot. The code I have entered will only allow data to be entered when it fits in with the structure required. The code allows any number or letter to be used as long as it is positioned correctly. This is a vital validation because a postcode is a very unique piece of data and may well be used to find or identify a customer within the database.
This is the Job_TBL. It contains information about all jobs the company has previously undertaken. Any new jobs that are requested and the company feels they can carry out can be added to the list through the “Add a job” button on the corresponding form.
Job ID is now the primary key and is the data type “Auto number”.
The date of order and expected completion date fields both have validations preventing them from carrying inaccurate data. The date of order must be either the same as or before the current date and the expected completion date must be after the date of order date.
Customer ID is now classified as a number because when a customer requests a job their specific number will be entered here by the user, classifying what jobs they want completed on the system.
The Job Parts_TBL is a link table in the database. It is present because there was a many to many relationship between the Job_TBL and the Parts_TBL. This table allows the data from both of these tables to be stored together, with a one to many relationship leaving the table in both directions. This will be shown later in the Relationship Diagram.
Here you can see both the Job ID and Part ID present both classified as numbers under the data type.
The JobPartsID field now acts as an autonumber for this table so that the two pieces of data can be classified together. This will allow the production of receipts and other printouts to be faster and more accurate.
Part_TBL
This table follows the same patterns as the previous two in that the ID, in this case Part is the primary key and an auto number.
The price field uses the data type currency. This will automatically fill the cell with a pound sign when data is entered. It will help in calculations within the database and provide quicker usage for the user when filling in forms.
Supplier ID is present as well so that the user can see which supplier supplies them with a specific part.
Supplier_TBL
My final table contains information about the company’s suppliers. The table contains a part name and part ID field that will link to the parts table. The data types remain similar to those in the previous tables to keep a constant style throughout the system. It helps to keep the system less complicated for the user and helps problems become apparent quickly.
Relationship Diagram
Here are the five tables described above presented in their relationship format. There are only one to many relationships present because the Job Parts table has acted as a link table between the Job and Parts tables which were previously a many to many relationship.
