logical approach to building a database? (1 Viewer)

[merlin777]

Can anyone recommend a guide to building an access database from scratch?

I'm looking for something that outlines a general, logical approach to what sequence to do things in, from pencil and paper to final formatting and splitting etc.

I'm not too worried about detail, just the sequence - especially the earlier parts.

Many thanks IA

 

[Uncle Gizmo]

What will your db do?

Sent from my SM-G925F using Tapatalk

 

[JKE]

Yes, I think you would need to specify...
I mean, the database design, the relations, the tables etc, are the same or similar, no matter the engine!

 

[jdraw]

There is a good step by step outline for database design here .

An overview with Normalization here.

Here is a tutorial from RogersAccessLibrary (Roger Carlson) that will guide you through a step by step process to design a database to support a stated set of business requirements. You have to work through it, but you'll learn/experience the process that can be used with any database. The tutorial includes a solution.

Also included is the Hernandez process in nutshell -- an overview that should be helpful.

 

[merlin777]

Thanks for the replies everyone.

The database is an appointments database for scheduling appointments between therapists and patients.

There are different types of therapists, sometimes more than one in a single appointment with a patient.
I will need to print out a weekly schedule of appointments for each current patient.
Therapists will need a printed schedule of their own appointments and be able to see other therapists' appointments.
Appointments include:
one or more therapists including their specialty
one patient
travel time to location of appointment for a therapist
a start time
a duration
a location
a purpose
a notes field

At the moment I have these tables:
patient ID
name
address
hos no
nhs no
phone1
phone2
email
notes

appointmentID
day
place
purpose
patient ID
therapistID
notes
start
length

therapistID
name
discipline
title

-I think I will also need tables for: purpose, discipline, job title
-appointment times are not completely variable - they are only between 9 and 5 and in 15 minute blocks so I might use a pick list for those.

So far I have tripped myself up by trying to add a lookup field to a table in design view and started to get all sorts of errors as a results and I think part of my problems is not doing things in the right sequence.

 

[jdraw]

Suggest you do NOT use spaces within field or object names. It wiil come back and haunt you with syntax issues.
Be cautious of Reserved Words, they will also bite you.

I strongly advise building a data model (pencil and paper), then using some sample data, make sure your model meets your requirements. The model will be a blueprint for your database. I wouldn't jump into details of Access too quickly.

 

[merlin777]

This is all great stuff - thanks everyone.

One thing I'm still not clear on - at what point do you start entering sample data?

I did it after I had set up the tables and relationships but when I tried to use lookup fields I got into trouble. From what I can see of the advice given, it's best to wait until you add forms so you can avoid lookup fields in tables but that means you can't conveniently enter data from lists until then. Is that correct or is there another option. What do you guys do?

 

[CJ_London]

design with access set to overlapping windows so you can see tables side by side. use pen and paper to note primary keys generated for new records - or notepad if you wish. test data should not need to be more than a few records to check tables are working as expected before starting to create a form. Don't get in the habit of using lookups (or calculated or multivalue fields)

Note sure if mentioned in the links so far provided, but the rule is one table, one form. With forms the relationships are managed using subforms and the subform linkchild/linkmaster properties.

 

[merlin777]

design with access set to overlapping windows so you can see tables side by side. use pen and paper to note primary keys generated for new records - or notepad if you wish. test data should not need to be more than a few records to check tables are working as expected before starting to create a form. Don't get in the habit of using lookups (or calculated or multivalue fields)

Note sure if mentioned in the links so far provided, but the rule is one table, one form. With forms the relationships are managed using subforms and the subform linkchild/linkmaster properties.

Got that CJ - thanks.

If you have one table, one form, how can you have an input screen for entering lots of data into lots of tables? Do you create another form which contains all the individual ones?

 

[CJ_London]

how can you have an input screen for entering lots of data into lots of tables?

as stated in my post

With forms the relationships are managed using subforms and the subform linkchild/linkmaster properties.

- you will cover subforms in one of the tutorials