Starting point for a database

[CharlieNewbie]

Hello All.

I work for a charity and have been given a task to come up with a database for our homelessness Drop-in Centre that can monitor each visitor, their personal details, visits, services they use during the visit (shower, food, medical etc) and accommodation they are in at present. Then come up with a reporting structure monthly, annually etc.

Have started with some tables, trying to build relationships between them, come up with some forms, but never quite works out the way I'm seeing it. So reckon its the relationship stage I'm falling down on. Primary keys and relationship between them and other fields.

The Tables I have come up with are Visitor Personal Details, Visits (Dates etc), Services Used, Accommodation Type (homeless, sofa surfing, hostel etc changes with time).

Want to make a fool proof system that people can add in information easily, and the information is accessible.

Any ideas on a format? Please!! Makes a huge difference.

Thanks for any help on this one.

Cheers

Charlie

 

[jrdr]

Hi CharlieNewbie,

The best advice I can give would be "To know what you want it to do". In my view many developers are asked by users to "Create something Good". Often this is without knowing exactly what that good really is. It seems to me that this is similar to the customer going to a railway ticket office and asking for a ticket to somewhere nice. Whereas, the ticket seller (ie you) needs something more concrete and specific.

To make this advice of mine more useful to you I would recommend that you start the process by designing a paper system. If you can capture the requirements on paper forms where they are handwritten on using pen and paper and the users are able to use these on a daily basis to their satisfaction then you are nearly there. I realise that the fun part is the Access development but it is a lot more fun (and quicker) when you know exactly what you are aiming at.

When you are doing this you will find that the structure of the database will start to form naturally.

I have seen many projects succeed and many others fail. In my experience computerising a paper system will usually succeed. Computerising an open ended system from scratch will usually fail.

When you do start the Access development please resist the urge to do a big bang and release the completed project all in one go. Instead aim to release often and have each release do one additional part of the users requirements.

Good Luck
John

 

[CharlieNewbie]

Thanks John,

I do need luck.

Yep, should have said we have a paper based system in place. Client Personal Visit Form to be filled in on first visit. Then there is a tick register taken everyday, which shows which services used, the days date and what accommodation type the client has or hasn't got at present. This is going to continue to run alongside the database, as frontline staff have a tendency to lose paperwork, it would be good to complete data entry at end of each day.

One of the aims is to be able to email reports to Funders at a reasonable short notice, rather than manually counting up tick sheets, which is very time consuming. Also keep records electronic, and get rid of paperwork over a year old.

Is there anyone out there who works on a visitor database, would be a great help.

Cheers

Charlie

 

[MarlaC]

Charlie, how about posting your table structure in full (fields, types, relationships)? All the forms, queries, and reports you'll want will be dependent on getting these right and details will aid in helping you. It sounds like you're off to a good start with normalizing your data. Also, specific questions where you run into issues would be helpful. Which forms give you trouble, and what sort of trouble?

 

[CharlieNewbie]

Thanks Marla,

Hopefully this will post and make it clearer.

Test Attached (fingers crossed)

Database Design
Entities
· Clients
· Visits
· Accommodation
· Services

Relationships
· 1 Client can have more than one Visit 1:N
· Many visits can be made by one client M:1
· 1 client can use many services per visit 1:N
· Many services can be used by each client M:1
· 1 client has many types of accommodation 1:N
· Then are many types of accommodation needed per Client M:1
· There is 1 type of accommodation per visit 1:1
· For every visit there is one type of accommodation 1:1

NB/ There are no relationships between accommodation and services

 

[gemma-the-husky]

charlie, i have to say that starting from scratch is not the best place!

access is far harder than say excel to use as a beginner. much more powerful, i would say, for data handling, but it needs a lot of understanding to achieve anything.

a complex "business" system of this nature is going to take a fair while to develop. If this is your first experience with databases, you have a very difficult , if not impossible task

you really need to get some help on this. if your organisation can pay for some help, you will find it a lot easier to make progress.

 

[CharlieNewbie]

Thanks Gemma, much appreciated

I know exactly what you mean, thank you. Done quite alot of research. Unfortunately the charity doesn't have the cash, so will get something in place, need to learn a bit of SQL, get ahead on queries and the relationships side. As for the not the same as excel, totally agree, excel is simple compared to this task.

Was looking for something in short term, but wont meet that, but it is a must over next 6 months (damn the internet is right! It does take that long). To be honest though cant be harder than fundraising & marketing in a recession (which is my actual job).

I am hooking up with another charity near me, and will see if can copy theirs and whether we can adapt it to meet our needs as well. Trying all bases.

Thank you again! Great forum by the way! Everyone very friendly.

Cheers

Charlie

 

[MarlaC]

Okay, I'm looking at your database now, but I'd appreciate clarification on accommodations.

Relationships
· 1 client has many types of accommodation 1:N
· Then are many types of accommodation needed per Client M:1
· There is 1 type of accommodation per visit 1:1
· For every visit there is one type of accommodation 1:1

NB/ There are no relationships between accommodation and services

The many types of accommodation had by each client represent all the types ever used? Or the potential types s/he could use? And the types needed are what? Each visit has one type of accommodation that is ... what? The accommodation type used most recently? The 1:1 of accommodation per visit makes more intuitive sense to me than the 1:N, and if I get a better sense of exactly what you're trying to track with that, I'll be better able to assist.

 

[CharlieNewbie]

Hi Marla,

Thank you

Basically of someone say visits the centre today and is sleeping outside they have an accommodation of RoughSleeper for today's visit. But say next week we can find them accommodation in a hostel the accommodation type changes to hostel for that visit, then hopefully in time they will get private rented. Or more likely they swap from category to category if they are more chaotic.

So at any one point in time they have a certain accomodation but at another point of time they might have another.

Its doing my head in!!

If I can get the relationships sorted I think would make life a whole lot easier.

Thanks for looking at Marla very much appreciated any thoughts you have.

Cheers

Charlie

 

[MarlaC]

Charlie, I'm happy to be of assistance. It sounds to me like a 1:1 of visit to accommodation, yes? And possibly a 1:1 of visit to accommodation sought as well? No worries, this is not going to be hard to sort, and you've got a good start on your tables.

 

[way2bord]

Hi Marla,

Thank you

Basically of someone say visits the centre today and is sleeping outside they have an accommodation of RoughSleeper for today's visit. But say next week we can find them accommodation in a hostel the accommodation type changes to hostel for that visit, then hopefully in time they will get private rented. Or more likely they swap from category to category if they are more chaotic.

So at any one point in time they have a certain accomodation but at another point of time they might have another.

Its doing my head in!!

If I can get the relationships sorted I think would make life a whole lot easier.

Thanks for looking at Marla very much appreciated any thoughts you have.

Cheers

Charlie

Took a quick glance... Tables are not normalized. You have several lists of things, a list of Services, a list of accommodations. These you currently include in tables - each as an individual field. This should be corrected if you intend to use a Access database -- otherwise, should work fine in Excel.

EDIT: Charlie...this is the internet. I strongly urge you to remove all personal / business information from data files before posting.

 

[CharlieNewbie]

Cheers Way2bord,

Sorry didnt see your reply, or didnt see notification, Normalisation, Thank you so much mate, I'm on it. I have to get out of Excel thinking, and drop downs etc.

So Im getting the feeling all I need is an accommodation field and a services field. Then a Drop down menu for each field with certain services, am I off track?]

Really appreciate you taking a look, and taken on board info in Edit, had a mild heart attack, only my details I think. Cant remember which version I posted though, will have a look.

Cheers again Way2bord

Charlie