Database critique, review, and some help with a problem

[AndyHumpherson]

Hi

I’m embarking upon my first real access system for work, it’s to manage a pin number (access code) for people who use our service.

I had a look at database answers and couldn’t really find a good example data model so had to design the concept myself… I’ve got past the conceptual stage and have set up my tables and relationships as best I can.

Some description:
- A person (tbl_person) is allocated some information about them using a table called tbl_prescriber_details (prescriber is someone who uses our service) which records information such as work base and organisation) I have some preset ones set up already so that’s why I use a junction table.
- I have two tables which again act as junction tables for contact numbers (tbl_Contact_Allocation) and e-mail addresses (tbl_email_allocation) as I want to record types of contact number and e-mail.
- I also will record information about prescribers training sessions (tbl_training_bookings) and group them together using tbl_staff_groups.

There are two types of pin number a prescriber might have, and the information is different so I thought to split them.

Prescribing Pins (Tbl_Pin_Allocation) have a set element of 2 letters and 4 numbers which describe their job code and trust (which are related using tbl_job_codes and tbl_trust), and they also have a unique 4 number element at the end. Each job code also has some billing information attached (tbl_bill_ref).

Authorising pins (Tbl_Auth_PIN_Allocation) are different and each has a ref cat (tbl_authoriser_ref_cat) which describes what the authorising pin can do.

Each authorising and prescribing pin is also linked to a trust (tbl_trusts)

I know it’s now a good time to review and check so was hoping for some choice words of criticism from users here… I've attached the database to this post.

Now I’ve also done some testing using some test data and mostly it seems alright. However if you look at Qry_Register which is going to be the basis of my training course register I get an error message talking about “ambiguous outer joins” and suggesting I use two queries. I’ve never done this before – does anyone have any suggestions or links to good articles on this?

I suppose I should say that the system will replace an excel workbook and I aim for it to assist in the creation, allocation, amendment and cancellation of pins using a graphical interface, as well as managing training courses.

Thanks

 

[plog]

Not bad, but I see a few things. First and foremost, notate your database fields. In design view every table has 3 fields: Field Name, Data Type and Description. With something this complex, fill in the Description--what its for, where it links to etc. It would have helped me go through it, and more importantly in 6 months when someone asks you to add a new feature to it, it will help you.

Here's some other notes/questions:

1. I would consolidate TBL_Email_Allocation and TBL_Email_Types into TBL_Contact_Allocation and TBL_Contact_Types. They have the same fields, there's no need to have seperate tables when this data can all be put into one table.

2. TBL_Training_Dates is a misleading name. I looked at the data and some have the same date. I would rename this TBL_Training_Sessions.

3. What's the difference between TBL_Staff_Groups and TBL_Team_Codes?

4. I don't fully understand your TBL_Staff_Groups and TBL_Team_Codes, so please explain why there is a Person_ID and a Team_Code field in TBL_Pin_Allocation.

5. I didn't really follow your explanation about the fields Prescribing_Pin and Auth_Pin, but I think they contain multiple pieces of data. Is that correct? Something like the letters mean this, certain digits mean this and the last numbers mean this? If so, that's the incorrect way to store it. You should store data in its discrete elements. So if it contains 3 pieces of information, you should store it in 3 fields and not altogether.

As for your query, your ambigous joins is caused by your LEFT and RIGHT Joins intersecting on the same tables (TBL_Contact_Allocation). I would change the join between that and TBL_Contact_Types to show all from TBL_Contact_Allocation. However, as I advised before, I would merge your Contact and Email tables. Doing that eliminates 2 of the tables in this query and makes it work.

 

[AndyHumpherson]

Thanks Plog

Cheers for your comments - i'll take them onboard and make necessary changes.

Ok, firstly staff groups are how as a service we 'group' people together, this can be irrespective of organisation, team, workbase, pin allocation or job code. In our excel workbook, these were the individual 'tabs' showing the groups of staff members.

Teams are slightly different. Within our service, we have a charging structure which looks a bit like this:

-Trust
-------Team
-------------Pin Number

So, each pin number can be allocated one team code.

As for the prescribing pin number, yes, it is made up of 4 indiviudal bits of information.
1) 'LR' which denotes our service
2) The trust code (two digits)
3) The job code (two digits)
4) Four unique numbers.

all of these together make up the prescribing pin number.

Now the authorising pin is slightly different...
1) 'LR' which denotes our service
2) The trust code (two digits)
3) The authorising ref cat (two digits)
4) Four unique numbers.

Are you suggesting that I store each individual peice in the same table and then concatenate the fields when I want to present it?

 

[plog]

Are you suggesting that I store each individual peice in the same table and then concatenate the fields when I want to present it?

Yes. Every discrete piece of information should be stored in its own field.