Advice needed on DB design (1 Viewer)

[AKUK]

Hello all. I only started this design this morning, I am out of touch with Access so please bear with me. I should be able to pick most things up as I did use Access for my A level IT years ago but have not used it since. I am looking for some critique on my proposed method and also some help as I have no idea how to implement some features.

I am a self employed IT technician. I consult home/corporate clients on how IT hardware can help them become more efficient, I repair, upgrade and build PCs, setup networks, reduce cost of utility bills etc.


Aim:

• To store client information such as address (for on-site work) and contact details.

• To store multiple contacts for each company or home client along with their relevant positions and contact details.

• To store past and future job information for each client for reference.

• Perhaps to be able to update job records with additional date stamps e.g. if a single job is carried out over more than one date the details can be linked or consolidated into a single record with details of what work was carried out on what date. This could be done in the Job Notes section but I am unsure if a separate field would be useful for each date work is carried out.

• To link or attach past job records to invoices (in PDF format).

• To be able to mail merge all clients, just home clients, just corporate clients or corporate clients of a specific size (by number of employees).

• To be able to lookup clients by use of any field.

• Ability to search for keywords in details fields such as Job Details and filter by company/client name.

• To be able to open a record and view client details. If multiple contacts stored for a client to have a drop down list, when any contact name is selected the appropriate position (e.g. Owner, secretary etc.), contact email and phone number be displayed. The ability to select details by position (e.g. if I needed to contact the head of a particular department and I was unsure of their name).

Initial Design:

Table1: Client data
User type (drop down box to select home user or corporate)
Company/Client name
Address Line 1
Address Line 2
Town/City
County
Postcode
Country
No. of Employees
Notes (This field will contain misc. information about the client)

Table2: Contact data
Company/Client Name
Contact Name
Position
Contact Email
Telephone Number
Fax

Table3: Job data
Company/Client Name
Date of Job
Status (Completed/Ongoing)
Job Details


I plan to use the Company/Client name field to link all three tables together IIRC this is the Primary Key.

How would be best to display client details? What I would like is to be able to open a record and display all fields in Table1 and Table2. I would like Contact Name and Position to each have a list in a drop down box containing all records from Table2 for that Company/Client. If I select 'Mr. J Bloggs' from the Contact list I will have his relevant position and contact details displayed, If I select 'Secretary' from the Position list I will have the name and relevant contact details displayed. Can a report offer such an interactive function? I cannot remember if macros could help

At the bottom of the above record I could have a button linked to a macro which is labelled 'Jobs'. When clicked it could show me all past and future jobs for that client and I could filter them down if necessary and search for keywords.

This post is getting a little long so if anyone could express their views on the above and help me to create the database I would be most grateful

Thanks in advance!

 

[Rabbie]

Firstly it is normally more efficient to link tables through Keys which are in number format. They index better than text fields. I usually use Autonumber keys.

Secondly read up on database normalization. Wikipedia has a good article.

Thirdly for interactive functionality Forms are the best way. Reports are most suitable for printer output.

In general remember not to use Access reserved words such as Date etc as table Names or field names. Also do not have any spaces in names.

 

[AKUK]

Thanks for the input Rabbie, I will take your advice on board implement autonumber keys when I begin building my DB. I will read that wiki article now.

Any more advice and tips to achieve some of my desired features?

Thanks!

 

[Lightwave]

AKUK

Just be aware that this is a big job AKUK even for someone that has a lot of experience of doing these things. From your initial post you appear to have a good ability to logically analyse the situation which will help.

If I was doing a job like that I probably would want to spend at least 3 months on it and just be careful if it is a business critical system. I probably wouldn't be totally happy with it for another 3 months. Teaching people to use these things is incredibly time consuming as well and if you haven't got everything tied down and easy to operate people may not use it.

It is a project in which you could potentially be required to use most of the range of commands in Access. So we can only help with a broad outline of structure. You might want to check out this link.

http://www.databaseanswers.org/data_models/

Your project sounds similar but not the same to the contract management item...

http://www.databaseanswers.org/data_models/contract_management/index.htm

Also you appear to understand one to many relationships.

I would expect you will need many to many relationships at some point.
For this you would use a junction table. These are tables that sit between the two tables you wish to link. You may wish to read up on them.

Here's to a small example I put together for someone else on junction tables..
It has an example database in it which you may find useful

http://www.access-programmers.co.uk/forums/showthread.php?t=189113

 

[AKUK]

Thanks for the reply, I will look into the links you have posted.

I tried to create my own database and it was going ok for the basic functions, then I found a template in Access 2010 called contacts which is perfect for storing Contact details. I am currently adapting it to store basic Client details with a button on the form to open another form to store individual contacts per company. I will then work my way through to store individual JobIDs for each Client. Using attachment as a table field I hope to link documents to a JobID such as invoices.

The mail merge will be added last as I have enough to be considering at the moment.

 

[Lightwave]

Sounds good yes I was going to suggest you start from an example...

Your probably starting to get a feel of how big a job it is to get it exactly how you want it.

 

[AKUK]

I will end up making some sacrifices and compromises to have a functional DB asap so I can get on with expanding my business

 

[Dairy Farmer]

Remember the following:
Create all the table you think you may need.
The db that contains the tables is now your BE (BackEnd)
For Forms, Queries, Reports and code start a new db that will be the FE (FrontEnd) and link the tables that are in your BE.
You may want to have multiple FE's at some stage to allow users limited access. So make sure you have one FE that has everything. Subsiquent FE's would have items removed and code changed.
Once you are happy with the design password protect the BE and make your FE a .accde.
Lastly deploy your app as a Runtime. That way the user neither has to have Access nor the version you designed the app in. Just the *free* Runtime for the version created in (i.e. 2010).

As always make plenty of backups as you go along.