Database design question

[Stephen Glynn]

I'm trying to design a database that holds details of clients and their
'disability groups'. For my purposes there are 10 disability groups,
which aren't mutually exclusive (someone might be blind as well as deaf).

My initial reaction was to have ten disability group fields (yes/no) as
part of my tblClients. However, I've got an idea that normalisation
theory says I should have a tblClients, a tblDisabilities and a linking
tblClientsDisabilities.

First question: which was right? My initial reaction or my idea about
normalisation theory?

Second question: if I do need the three tables, I'm not completely sure
how I should handle this on the form I'll need so people can enter
details of clients and their disabilities. I'd certainly like the form
to contain ten check boxes but I'm not clear how I handle this.

You'll presumably enter the client's name, address and so forth on the
main form, but how do I then take the autonumber ClientID I've just
generated for him and transfer it to the subform to create an
indeterminate number of rows in tblClientsDisabilities, based on the
number of check boxes I've ticked for his various disabilities?

Or was my initial reaction right, and I'm just complicating the matter
needlessly?

Steve

 

[Douglas J Steele]

Your second approach is definitely correct. You'd have to modify your table
and (presumably) redesign your form when you get an eleventh disability
group with the first approach.

Probably the most common GUI approach is to use a form and subform. The form
would be based on tblClients. The subform would be based on
tblClientsDisabilities (linked on the Client Id), and you'd have a combobox
on the subform with tblDisabilities as the row source to provide a list of
disabilities to choose from.

Take a look at the Northwind database that came with Access. The tables
Products, Orders and OrderDetails correspond (more or less) to
tblDisabilities, tblClients and tblClientsDisabilities in your design, and
forms Orders and Orders Subform illustrate what I discussed above.

 

[Ed Warren]

You have described the following:
Clients
Disabilites
Client_Disability

So you will want (at least) the following tables

Clients
Disabilites
ClientDisabilities

With the following relationships
Each client can have 0:m disabilities
Each disability can be related to 0:m clients
so
Clients -->1:M ClientDisabilites (on ClientID) -->M:1 Disabilities (on
DisabilityID)

Now each client can have 0 to N (10) disabilities and you can query your
database to find how many are 'blind' and how many are 'deaf' or .....
In the 'other' design you will have to change your database DESIGN when you
find yet another disability. In the above design you will just add another
row in the disability table.

Ed Warren.

 

[Ed Warren]

Sorry I didn't read all of your post
You will build a form based on Clients with all the client information in
it.
You will build a form based on ClientDisabilities.
it will contain a textbox bound to clientID an a comboBox bound to
DisabilityID, with the lookup set to the Disabilities table (select
DisabilityID, DisabilityText from Disabilities orderby DisabilityText). Set
column count to 2, column width to 0;2
Add the second from to your Client form as a subform, linked by the ClientID

Ed Warren

 

[Stephen Glynn]

Thanks, both Ed and Doug. Your swift replies have really clarified
things for me!

Steve