Hi all

I'm trying to redesign our team's access database as it's gotten rather "clunky" over the years. I can't quite get my head around what I should include in each table, so I'm hoping for some advice.

What I'm planning on having is:

Complainant - a table with ID details of the person complaining
Service User - a table as above but for the service user

However, a complainant may be the service user OR they may be contacting us on behalf of a service user.... should I actually have 1 table for all people who contact us, then include in there a field where we indicate if they are the complainant or the service user? I would need to display both the complainant and the service user on the form, so I'm not sure what's the best way to deal with this bit.

Secondly, I have a large number of fields associated with each complaint that I need for reporting purposes. I'm not sure whether to split them, or whether to keep them in one table. I have fields relating to the classification of the complaint (i.e. which service it is regarding), I have complaint receipt details (i.e. date received, who by, when acknowledged etc), I have fields relating to our resolution planning (i.e. agreed responder, investigator, date for response,, case manager etc), and I have resolution fields (i.e. date of response, if a monitoring form has been received, whether the complaint is closed NFA)....

So, there are a lot of fields there, however the combination of them all is likely to be unique to each complaint. So, do I have them all in one table, or split it? We have so far had all the fields (and many many more) in one table which has proved extremely unwieldy, but I can't make myself think in any other way - a nudge in the right direction would be gratefully appreciated!!

The first thing consists in drawing a schema of the whole system, then analyze and detail every part of it.

Normal forms can help you here. Your application deals with Complains. Each complain has a (hopefully) defined number of characteristics (it's properties). Some of those chararacteristics can be common to several complains (they are repetitive), so they can be defined and stored into secondary (lookup) tables, etc.

As for the normal forms, the strict minimum requirement is to have your database in the first normal form (you'll quickly run into nasty problems if you do not comply with this). Second and third normal forms reinforce data integrity and help building a more robust and coherent system, but you pay the price of a more complex and rigid schema. Going beyond the third normal form is a pain in the neck and provides very few advantages because the data is organized in a way that's not directly usable. This means that you have to de-normalize the data when you use them.

Among many others, see:
Database Tutorial
A Simple Guide to Five Normal Forms in Relational Database Theory
3 Normal Forms Database Tutorial
Normalizing Your Database: First Normal Form (1NF)
First Normal Form (1NF) - Normalising Your Database | Database Solutions for Microsoft Access | databasedev.co.uk