Please Help- Job agency database, not sure about the relationships

[DinoBaggio]

Hi all,



I am looking to build a database that can help a job support agency but I am finding the task quite difficult and would appreciate any help possible. I am quite the novice in database design and would love someone to review and let me know what my work so far is like.

The organisation is set up so that clients come to them, upon referral and the organisation helps the client to get back into employment.

When a client comes to the organisation they are caseloaded and assigned a mentor from the employee staff to look after their case.

The organisation helps clients by sending them to skills courses. There are 5 categories of course programmes that a client can be sent to. Within each category a client will need to undertake a skills programme however the names of each are specific to the client and will need to be manualy typed in under the correct category they fall under.

The organisation also helps organisations by helping them get into employment. Again their are a number of categories of employment types that the clients can be sent to and the organisation needs to keep track of the employment type a client is in and the employer details that they have went to.

I have spent a few weeks now trying to develop this database and have ended up with a design that I am just not sure about. It seems to work, but so much of it replies on the primary key of the case table. If anyone could take a look and see if what I have done is ok, it would be much appreciated! Please help people because I am completely unsure if my design is massively flawed.

I have just attached two screenshots which I placed in a word document to show you all what I have done in relating the tables, and the form and subform I have for users to interact and enter the data needed. please help and if my wording here is very confusing, please let me know.

[bigroo]

Hi DinoBaggio,

Welcome to this forum, you will find lots of good information and helpful members here.

I looked over what you have on the database design screenshot, looks good. One thing I would change is everywhere you have as your primary keys as only "ID", I would change to a different field name, such as under the Calls table, "CallsID". In my early days of designing databases, I did the same thing, it did causes headaches later in the design.

Under the customers table, I would break out the City, County, Postal code and Country to separate table, it will save errors in someone mis-spelling a city name etc when entering information.

The best thing to do is make a copy of the database, structure only and with the original database beginning filling in information and see if the results are what you expect.
Good Luck!!

Thanks

[DinoBaggio]

Hi BigRoo,

Thankyou so much for getting back to me, it really is much appreciated!!And also, thanks for the welcome! I am quite a novice to databases and felt the forum could offer me some good advice and learning

I' so glad my design looks pretty good. I was really worried that because I had used the CaseID as the foreign key in so many tables that my database design would be wrong, especially because I am quite a beginner and so you can imagine my worries when I clicked into my Casetbl and clicked the little cross beside the CaseID, that usually expands to show subsheet data from related tables, only to be confronted with a message box asking me to select the subdatasheet I wanted to view here. But I quess that is just because so many of the child tables are on the same level it is not possible to view the datasheets from each table as a subdatasheet in the Case table?? (agh, dont know if that makes sense but I hope you know what I mean )

Thanks for the advice too, I'll change those ID's in the tables now and was actually also curious before I posted about how to avoid the issue of users mispelling those address fields, so thanks also for the advice there. Just to be sure I know what you mean there though, are you saying I should make another table that can act as a lookup and put in there the city/town name, county, country and post code extension attributed to that area?

Thanks so much again for getting back to me BigRoo, I am very grateful for everything, the welcome, the advice, and the time you have taken to check my work, it really is great that people like you are a part of this forum and willing to help!

[bigroo]

Hi DinoBaggio,

No problem, I am glad to help out.

I clicked into my Casetbl and clicked the little cross beside the CaseID, that usually expands to show subsheet data from related tables, only to be confronted with a message box asking me to select the subdatasheet I wanted to view here. But I quess that is just because so many of the child tables are on the same level it is not possible to view the datasheets from each table as a subdatasheet in the Case table?? (agh, dont know if that makes sense but I hope you know what I mean )

Yes, you are correct, it is becuse you have some many tables.

Just to be sure I know what you mean there though, are you saying I should make another table that can act as a lookup and put in there the city/town name, county, country and post code extension attributed to that area?

Yes, create a new table for the location data, this will prevent mis-spelling indeed. and link to a locationID field or CityID field.

Thanks

[DinoBaggio]

Hi BigRoo,

Once again, thanks for everything!! I really appreciate all your help! You have solved all my database queries and problems and I feel so much more confident in my database now.

Thank you so much! Your a true gentleman