Database Design Idea

I’m looking to create, in my view, a complex database in Access 2007 and
would like your advice and views on my design plan.

First I will explain what the database will be trying to achieve. It is
going to be a database for a solicitors. At the moment we have regular
clients and new clients all of the time.

The current database stores the client’s details, the matter, court details,
archive details etc in one table. Each time a client has a new matter their
details are entered again in a different record and the new matter is opened.
It also uses Access 2000. It isn’t the most user-friendly interface and
therefore I am looking to create a new database with a new, user-friendly
feel and look to it.

My idea for the new database would be to have a Client ID for each client and
then through this open the matter (thus saving typing all the clients
information again). Client’s may have several matters at the same time and
once the matter is finished it is given an archive number. The look of the
database would be - the clients details (name etc) on the main view and then
in a sub form, the clients matters (which unfinished files at the top). Then
from the sub form you can access each individual matter which will bring up
the matter details, court details etc. Is this possible?

Here is how I think my tables would be (with the primary key placed at the
top). UFN (Unique File Number) is given to each matter, this is how we
reference our files.

tbl_client_data

Client ID
Title
Initials
First Name
Surname
Date of Birth
NI Number
Contact No.
Alt Contact No.
E-mail Add
Address
City/Town
Region
Postcode


tbl_matter_data

UFN
Client ID
Case Code
File Number
Fee Earner
Matter
Notes


tbl_at_police

UFN
DSCC Ref
ISJ No.
PS Name
Date at PS
PS Outcome
Bail Conditions


tbl_at_court

UFN
Court ID
Case Title
Case Number
Outcome
Bail Conditions
Legal Aid No.


tbl_appearance

UFN
Next Date
Next Time
Location
Remand Status


tbl_court _details

Court ID
Court Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cju_details

CJU ID
CJU Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cps_details

CPS ID
CPD Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_results

UFN
File Finished
Date File Closed
Archive Number
Sentence
Archived


Do you think this is a good way to go? Any advice and guidance will be much
appreciated.

Sorry for the lengthy post,
Andy.

"andycambo" u53471@uwe wrote in message news:99753837ba707@uwe...
I’m looking to create, in my view, a complex database in Access 2007 and
would like your advice and views on my design plan.

First I will explain what the database will be trying to achieve. It is
going to be a database for a solicitors. At the moment we have regular
clients and new clients all of the time.


It looks OK to me Andy although I never have spaces in object or field
names. Also I'd question the suitability of Access to store such sensitive
data securely.

Regards,
Keith.
www.keithwilby.co.uk

Hi,

it's very hard to tell if you're going the right way or not. Splitting
the information in several tables with primary keys is already a good start.
To start a new project I always make a thorough analysis first, and then
call a meeting with the users to discuss the analysis and the way the
application will work. To help me with the analysis I use the program
"Enterprise architect" from Sparx systems. It is a cheap, but very good UML
analyse tool you can download from the net.
When they agree, I go on designing the table structure. To help you there,
just lookup the papers about referential database structures and
normalisation on the internet.
I then start building the front end. I always design the application module
by module, for instance: customer info module, court module and so on. This
way the users can already start testing module 1 while I'm building module 2.
Then the whole app is debugged and tested.
Meanwhile I write the technical and user manual.
After the final tests, the application is installed and a maintenance plan
drawn up.

Hope this gives you a picture of how to start a new project.
--
Kind regards
Noëlla


"andycambo" wrote:

I’m looking to create, in my view, a complex database in Access 2007 and
would like your advice and views on my design plan.

First I will explain what the database will be trying to achieve. It is
going to be a database for a solicitors. At the moment we have regular
clients and new clients all of the time.

The current database stores the client’s details, the matter, court details,
archive details etc in one table. Each time a client has a new matter their
details are entered again in a different record and the new matter is opened.
It also uses Access 2000. It isn’t the most user-friendly interface and
therefore I am looking to create a new database with a new, user-friendly
feel and look to it.

My idea for the new database would be to have a Client ID for each client and
then through this open the matter (thus saving typing all the clients
information again). Client’s may have several matters at the same time and
once the matter is finished it is given an archive number. The look of the
database would be - the clients details (name etc) on the main view and then
in a sub form, the clients matters (which unfinished files at the top). Then
from the sub form you can access each individual matter which will bring up
the matter details, court details etc. Is this possible?

Here is how I think my tables would be (with the primary key placed at the
top). UFN (Unique File Number) is given to each matter, this is how we
reference our files.

tbl_client_data

Client ID
Title
Initials
First Name
Surname
Date of Birth
NI Number
Contact No.
Alt Contact No.
E-mail Add
Address
City/Town
Region
Postcode


tbl_matter_data

UFN
Client ID
Case Code
File Number
Fee Earner
Matter
Notes


tbl_at_police

UFN
DSCC Ref
ISJ No.
PS Name
Date at PS
PS Outcome
Bail Conditions


tbl_at_court

UFN
Court ID
Case Title
Case Number
Outcome
Bail Conditions
Legal Aid No.


tbl_appearance

UFN
Next Date
Next Time
Location
Remand Status


tbl_court _details

Court ID
Court Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cju_details

CJU ID
CJU Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cps_details

CPS ID
CPD Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_results

UFN
File Finished
Date File Closed
Archive Number
Sentence
Archived


Do you think this is a good way to go? Any advice and guidance will be much
appreciated.

Sorry for the lengthy post,
Andy.

Thanks for your reply Keith, I agree that Access most probably the ideal
solution to storing sensitive information but the program is available to use
and solicitors pockets are tight so there isn't scope on other programs.

NG wrote:
Hi,
.....

To help me with the analysis I use the program
"Enterprise architect" from Sparx systems. It is a cheap, but very good UML
analyse tool you can download from the net.

Thanks for the heads-up on this program. I will have a look at this and see
if it will be of use to me.

Glad to hear I'm not too far off the track. I've been programming Java for 3
years so I know little bits about databases (second, third form etc) but
never designed and created one. This is going to be a challenge.

Thanks again for the advice,
Andy.

IMHO "secure software" is an oxymoron, which would mean that "secure
software" is little different than Access. The wrong people (someone in the
IT department, a smart hacker, a government employee with "authority" )
always have a way to get in. The only secure data is that which is not
stored.

When they get into the really heavy stuff, the only "barrier" relied on is
lack of any interface or electronic connection through the perimeter.

Andy,

Having designed something similar to this I have a few suggestions...


tblClientProfile

cpClientID (PK:Autonumber)
cpInternalClientID
cpTitle
cpInitials
cpFirstName
cpSurname
cpDate of Birth
cpNINumber (Not sure what this is and if it should stay in this tables)
cpHomePhone
cpCellPhone
cpOfficePhone
cpeMailAddress
cpAddress1
cpAddress2
cpCityTown
cpRegion
cpPostalCode
cpAttorneyID (FK) -You're going to need a table. Does more then one
attorney get assinged per Client?


tblMatters

mMattersID (PK:Autonumber)
mUFNID (PK)
mClientID (FK)
mCaseCode
mFileNumber
mFeeEarner
mMatter
mNotes
mClosed


tblAtPolice

apMattersID (FK)
apDSCCReference
apISJNumber
apPSName
apDateAtPS
apOutcome
apBailConditions


tblAtCourt

acAtCourtID (PK:Autonumber)
acMattersID (FK)
acCourtID (FK)
acCaseTitle
acCaseNumber
acOutcome
acBailConditions
acLegalAidNumber


tblAppearance

aAtCourtID (FK)
aNextDate
aNextTime
aLocation
aRemandStatus


tblCourt

cCourtID (PK:Autonumber)
cCourt
cAddressDX
cCityTown
cRegion
cPostalCode
cPhoneNumber
cFaxNumber
cCourtTypeID (FK:CJU, CPS)


tblCourtType

ctCourtTypeID (PK:Autonumber)
ctCourtType


tblResults

rResultsID (PK)
rMattersID (FK)
rFileFinished
rDateFileClosed
rArchiveNumber
rSentence


There is probably a wee bit more clean-up but this should help. You are
trying to create seperate tables for EVERYTHING when actually what you need
to combine some tables, which I did in one case. To figure out your
Business Model what you need to a tract a Dummy Case and wirte down what
information you want from that case. (Like a Table of Contents in a book.)
Then approach your tables.

I also took away all wildcard characters and spaces... only causes extra
typing and issues when coding.

One reason I preface fields names with the first letter of the proper name
of the table so that I don't end up with Reserved Words as my field names.
You need to be careful with field names as you don't want to use Reserved
Words, see...

http://allenbrowne.com/Ap****ueBadWord.html


Your main form should probably be Client Matters as this will be the one
most accessed. To answer your questions, all items you brought up are
doable as long as your tables are set-up properly.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"andycambo" u53471@uwe wrote in message news:99753837ba707@uwe...
I'm looking to create, in my view, a complex database in Access 2007 and
would like your advice and views on my design plan.

First I will explain what the database will be trying to achieve. It is
going to be a database for a solicitors. At the moment we have regular
clients and new clients all of the time.

The current database stores the client's details, the matter, court
details,
archive details etc in one table. Each time a client has a new matter
their
details are entered again in a different record and the new matter is
opened.
It also uses Access 2000. It isn't the most user-friendly interface and
therefore I am looking to create a new database with a new, user-friendly
feel and look to it.

My idea for the new database would be to have a Client ID for each client
and
then through this open the matter (thus saving typing all the clients
information again). Client's may have several matters at the same time
and
once the matter is finished it is given an archive number. The look of
the
database would be - the clients details (name etc) on the main view and
then
in a sub form, the clients matters (which unfinished files at the top).
Then
from the sub form you can access each individual matter which will bring
up
the matter details, court details etc. Is this possible?

Here is how I think my tables would be (with the primary key placed at the
top). UFN (Unique File Number) is given to each matter, this is how we
reference our files.

tbl_client_data

Client ID
Title
Initials
First Name
Surname
Date of Birth
NI Number
Contact No.
Alt Contact No.
E-mail Add
Address
City/Town
Region
Postcode


tbl_matter_data

UFN
Client ID
Case Code
File Number
Fee Earner
Matter
Notes


tbl_at_police

UFN
DSCC Ref
ISJ No.
PS Name
Date at PS
PS Outcome
Bail Conditions


tbl_at_court

UFN
Court ID
Case Title
Case Number
Outcome
Bail Conditions
Legal Aid No.


tbl_appearance

UFN
Next Date
Next Time
Location
Remand Status


tbl_court _details

Court ID
Court Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cju_details

CJU ID
CJU Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cps_details

CPS ID
CPD Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_results

UFN
File Finished
Date File Closed
Archive Number
Sentence
Archived


Do you think this is a good way to go? Any advice and guidance will be
much
appreciated.

Sorry for the lengthy post,
Andy.

I like how you named each field...I have never thought about that, but it
makes very good sense.

Del

"Gina Whipp" wrote:

Andy,

Having designed something similar to this I have a few suggestions...


tblClientProfile

cpClientID (PK:Autonumber)
cpInternalClientID
cpTitle
cpInitials
cpFirstName
cpSurname
cpDate of Birth
cpNINumber (Not sure what this is and if it should stay in this tables)
cpHomePhone
cpCellPhone
cpOfficePhone
cpeMailAddress
cpAddress1
cpAddress2
cpCityTown
cpRegion
cpPostalCode
cpAttorneyID (FK) -You're going to need a table. Does more then one
attorney get assinged per Client?


tblMatters

mMattersID (PK:Autonumber)
mUFNID (PK)
mClientID (FK)
mCaseCode
mFileNumber
mFeeEarner
mMatter
mNotes
mClosed


tblAtPolice

apMattersID (FK)
apDSCCReference
apISJNumber
apPSName
apDateAtPS
apOutcome
apBailConditions


tblAtCourt

acAtCourtID (PK:Autonumber)
acMattersID (FK)
acCourtID (FK)
acCaseTitle
acCaseNumber
acOutcome
acBailConditions
acLegalAidNumber


tblAppearance

aAtCourtID (FK)
aNextDate
aNextTime
aLocation
aRemandStatus


tblCourt

cCourtID (PK:Autonumber)
cCourt
cAddressDX
cCityTown
cRegion
cPostalCode
cPhoneNumber
cFaxNumber
cCourtTypeID (FK:CJU, CPS)


tblCourtType

ctCourtTypeID (PK:Autonumber)
ctCourtType


tblResults

rResultsID (PK)
rMattersID (FK)
rFileFinished
rDateFileClosed
rArchiveNumber
rSentence


There is probably a wee bit more clean-up but this should help. You are
trying to create seperate tables for EVERYTHING when actually what you need
to combine some tables, which I did in one case. To figure out your
Business Model what you need to a tract a Dummy Case and wirte down what
information you want from that case. (Like a Table of Contents in a book.)
Then approach your tables.

I also took away all wildcard characters and spaces... only causes extra
typing and issues when coding.

One reason I preface fields names with the first letter of the proper name
of the table so that I don't end up with Reserved Words as my field names.
You need to be careful with field names as you don't want to use Reserved
Words, see...

http://allenbrowne.com/Ap****ueBadWord.html


Your main form should probably be Client Matters as this will be the one
most accessed. To answer your questions, all items you brought up are
doable as long as your tables are set-up properly.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"andycambo" u53471@uwe wrote in message news:99753837ba707@uwe...
I'm looking to create, in my view, a complex database in Access 2007 and
would like your advice and views on my design plan.

First I will explain what the database will be trying to achieve. It is
going to be a database for a solicitors. At the moment we have regular
clients and new clients all of the time.

The current database stores the client's details, the matter, court
details,
archive details etc in one table. Each time a client has a new matter
their
details are entered again in a different record and the new matter is
opened.
It also uses Access 2000. It isn't the most user-friendly interface and
therefore I am looking to create a new database with a new, user-friendly
feel and look to it.

My idea for the new database would be to have a Client ID for each client
and
then through this open the matter (thus saving typing all the clients
information again). Client's may have several matters at the same time
and
once the matter is finished it is given an archive number. The look of
the
database would be - the clients details (name etc) on the main view and
then
in a sub form, the clients matters (which unfinished files at the top).
Then
from the sub form you can access each individual matter which will bring
up
the matter details, court details etc. Is this possible?

Here is how I think my tables would be (with the primary key placed at the
top). UFN (Unique File Number) is given to each matter, this is how we
reference our files.

tbl_client_data

Client ID
Title
Initials
First Name
Surname
Date of Birth
NI Number
Contact No.
Alt Contact No.
E-mail Add
Address
City/Town
Region
Postcode


tbl_matter_data

UFN
Client ID
Case Code
File Number
Fee Earner
Matter
Notes


tbl_at_police

UFN
DSCC Ref
ISJ No.
PS Name
Date at PS
PS Outcome
Bail Conditions


tbl_at_court

UFN
Court ID
Case Title
Case Number
Outcome
Bail Conditions
Legal Aid No.


tbl_appearance

UFN
Next Date
Next Time
Location
Remand Status


tbl_court _details

Court ID
Court Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cju_details

CJU ID
CJU Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cps_details

CPS ID
CPD Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_results

UFN
File Finished
Date File Closed
Archive Number
Sentence
Archived


Do you think this is a good way to go? Any advice and guidance will be
much
appreciated.

Sorry for the lengthy post,
Andy.

Del,

Thanks...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"DUNNER7" wrote in message
...
I like how you named each field...I have never thought about that, but it
makes very good sense.

Del

"Gina Whipp" wrote:

Andy,

Having designed something similar to this I have a few suggestions...


tblClientProfile

cpClientID (PK:Autonumber)
cpInternalClientID
cpTitle
cpInitials
cpFirstName
cpSurname
cpDate of Birth
cpNINumber (Not sure what this is and if it should stay in this tables)
cpHomePhone
cpCellPhone
cpOfficePhone
cpeMailAddress
cpAddress1
cpAddress2
cpCityTown
cpRegion
cpPostalCode
cpAttorneyID (FK) -You're going to need a table. Does more then one
attorney get assinged per Client?


tblMatters

mMattersID (PK:Autonumber)
mUFNID (PK)
mClientID (FK)
mCaseCode
mFileNumber
mFeeEarner
mMatter
mNotes
mClosed


tblAtPolice

apMattersID (FK)
apDSCCReference
apISJNumber
apPSName
apDateAtPS
apOutcome
apBailConditions


tblAtCourt

acAtCourtID (PK:Autonumber)
acMattersID (FK)
acCourtID (FK)
acCaseTitle
acCaseNumber
acOutcome
acBailConditions
acLegalAidNumber


tblAppearance

aAtCourtID (FK)
aNextDate
aNextTime
aLocation
aRemandStatus


tblCourt

cCourtID (PK:Autonumber)
cCourt
cAddressDX
cCityTown
cRegion
cPostalCode
cPhoneNumber
cFaxNumber
cCourtTypeID (FK:CJU, CPS)


tblCourtType

ctCourtTypeID (PK:Autonumber)
ctCourtType


tblResults

rResultsID (PK)
rMattersID (FK)
rFileFinished
rDateFileClosed
rArchiveNumber
rSentence


There is probably a wee bit more clean-up but this should help. You are
trying to create seperate tables for EVERYTHING when actually what you
need
to combine some tables, which I did in one case. To figure out your
Business Model what you need to a tract a Dummy Case and wirte down what
information you want from that case. (Like a Table of Contents in a
book.)
Then approach your tables.

I also took away all wildcard characters and spaces... only causes extra
typing and issues when coding.

One reason I preface fields names with the first letter of the proper
name
of the table so that I don't end up with Reserved Words as my field
names.
You need to be careful with field names as you don't want to use Reserved
Words, see...

http://allenbrowne.com/Ap****ueBadWord.html


Your main form should probably be Client Matters as this will be the one
most accessed. To answer your questions, all items you brought up are
doable as long as your tables are set-up properly.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"andycambo" u53471@uwe wrote in message news:99753837ba707@uwe...
I'm looking to create, in my view, a complex database in Access 2007
and
would like your advice and views on my design plan.

First I will explain what the database will be trying to achieve. It
is
going to be a database for a solicitors. At the moment we have regular
clients and new clients all of the time.

The current database stores the client's details, the matter, court
details,
archive details etc in one table. Each time a client has a new matter
their
details are entered again in a different record and the new matter is
opened.
It also uses Access 2000. It isn't the most user-friendly interface
and
therefore I am looking to create a new database with a new,
user-friendly
feel and look to it.

My idea for the new database would be to have a Client ID for each
client
and
then through this open the matter (thus saving typing all the clients
information again). Client's may have several matters at the same time
and
once the matter is finished it is given an archive number. The look of
the
database would be - the clients details (name etc) on the main view and
then
in a sub form, the clients matters (which unfinished files at the top).
Then
from the sub form you can access each individual matter which will
bring
up
the matter details, court details etc. Is this possible?

Here is how I think my tables would be (with the primary key placed at
the
top). UFN (Unique File Number) is given to each matter, this is how we
reference our files.

tbl_client_data

Client ID
Title
Initials
First Name
Surname
Date of Birth
NI Number
Contact No.
Alt Contact No.
E-mail Add
Address
City/Town
Region
Postcode


tbl_matter_data

UFN
Client ID
Case Code
File Number
Fee Earner
Matter
Notes


tbl_at_police

UFN
DSCC Ref
ISJ No.
PS Name
Date at PS
PS Outcome
Bail Conditions


tbl_at_court

UFN
Court ID
Case Title
Case Number
Outcome
Bail Conditions
Legal Aid No.


tbl_appearance

UFN
Next Date
Next Time
Location
Remand Status


tbl_court _details

Court ID
Court Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cju_details

CJU ID
CJU Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_cps_details

CPS ID
CPD Name
Address/DX
City/Town
Region
Postcode
Contact No.
Fax No.


tbl_results

UFN
File Finished
Date File Closed
Archive Number
Sentence
Archived


Do you think this is a good way to go? Any advice and guidance will be
much
appreciated.

Sorry for the lengthy post,
Andy.

Gina Whipp wrote:
Andy,

Having designed something similar to this I have a few suggestions...

Thanks for all your replies so far.

Thanks Gina for your reply. I’ve been through my tables again and re-
organised them. Your tables were a lot of help so thank-you.

Here are my latest tables:

tblClientProfile

cpClientID (PK:Autonumber)
cpInternalClientID
cpTitle
cpInitials
cpFirstName
cpSurname
cpDateOfBirth
cpNINumber
cpHomePhone
cpMobilePhone
cpeMailAddress
cpAddress1
cpAddress2
cpCityTown
cpRegion
cpPostalCode
cpFeeEarnerID (FK)


tblMatters

mMatterID (PK:Autonumber)
mUFNID (PK)
mClientID(FK)
mFileNumber
mCaseCode
mNotes
mClosed
mMatter


tblAtPolice

apMatterID (FK)
apDSCCReference
apISJNumber
apPSName
apDateAtPS
apOutcome
apBailConditions


tblAtCourt

acMatterID (FK)
acCourtID (FK)
acCaseTitle
acCaseNumber
acOutcome
acBailConditions
acLegalAidNumber


tblAppearance

aMatterID (FK)
aNextDate
aNextTime
aLocation
aRemandStatus


tblCourt

cCourtID (PK:Autonumber)
cCourtName
cAddress/DX
cCity/Town
cRegion
cPostcode
cContact No.
cFax No.
cCourtTypeID


tblCourtType

ctCourtTypeID (PK:Autonumber)
ctCourtType


tblResults

rResultsID (PK:Autonumber)
rMatterID (FK)
rFileFinished
rDateFileClosed
rArchiveNumber
rSentence


tblFeeEarners

fFeeEarnerID(PK:Autonumber)
fFeeEarnerName
fFeeEarnerNumber


I’ve made a few changes from what you suggested. One thing I have changed is
on the AtCourt table. On the table you suggested that I should use a
AtCourtID, I’m not sure what the use of this key would be. I’ve also changed
the key in the Appearance table to MatterID rather than AtCourtID. I’ll
explain why I have done this so maybe you could tell me if I’m right or wrong
to do this.

What sometimes happens is, we will first see the client at a police station
and advise them. A file will then be opened on the database. The client can
then be bailed several times for the matter (hence the appearance table being
changed). If the client is charged then the matter will then go onto court,
which is the reason I thought that the AtCourtID would be pretty useless in
this case, however sometimes a case can go straight to court.

Would a AtCourtID be any use?

Thanks
Andy.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1

Andy,

From what you posted you were correct in changing the tblAtCourt. My ideas
were only meant to assist you not *tell* you!

In tblCourt you still want to get rid the */* and the spaces and the *.*
Remember, you are only causing yourself extra typing. Other than that all
looks good!

tblCourt

cCourtID (PK:Autonumber)
cCourtName
cAddress/DX
cCity/Town
cRegion
cPostcode
cContact No.
cFax No.
cCourtTypeID


You're welcome...
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"andycambo via AccessMonster.com" u53471@uwe wrote in message
news:997fed83525bf@uwe...
Gina Whipp wrote:
Andy,

Having designed something similar to this I have a few suggestions...

Thanks for all your replies so far.

Thanks Gina for your reply. I've been through my tables again and re-
organised them. Your tables were a lot of help so thank-you.

Here are my latest tables:

tblClientProfile

cpClientID (PK:Autonumber)
cpInternalClientID
cpTitle
cpInitials
cpFirstName
cpSurname
cpDateOfBirth
cpNINumber
cpHomePhone
cpMobilePhone
cpeMailAddress
cpAddress1
cpAddress2
cpCityTown
cpRegion
cpPostalCode
cpFeeEarnerID (FK)


tblMatters

mMatterID (PK:Autonumber)
mUFNID (PK)
mClientID(FK)
mFileNumber
mCaseCode
mNotes
mClosed
mMatter


tblAtPolice

apMatterID (FK)
apDSCCReference
apISJNumber
apPSName
apDateAtPS
apOutcome
apBailConditions


tblAtCourt

acMatterID (FK)
acCourtID (FK)
acCaseTitle
acCaseNumber
acOutcome
acBailConditions
acLegalAidNumber


tblAppearance

aMatterID (FK)
aNextDate
aNextTime
aLocation
aRemandStatus


tblCourt

cCourtID (PK:Autonumber)
cCourtName
cAddress/DX
cCity/Town
cRegion
cPostcode
cContact No.
cFax No.
cCourtTypeID


tblCourtType

ctCourtTypeID (PK:Autonumber)
ctCourtType


tblResults

rResultsID (PK:Autonumber)
rMatterID (FK)
rFileFinished
rDateFileClosed
rArchiveNumber
rSentence


tblFeeEarners

fFeeEarnerID(PK:Autonumber)
fFeeEarnerName
fFeeEarnerNumber


I've made a few changes from what you suggested. One thing I have changed
is
on the AtCourt table. On the table you suggested that I should use a
AtCourtID, I'm not sure what the use of this key would be. I've also
changed
the key in the Appearance table to MatterID rather than AtCourtID. I'll
explain why I have done this so maybe you could tell me if I'm right or
wrong
to do this.

What sometimes happens is, we will first see the client at a police
station
and advise them. A file will then be opened on the database. The client
can
then be bailed several times for the matter (hence the appearance table
being
changed). If the client is charged then the matter will then go onto
court,
which is the reason I thought that the AtCourtID would be pretty useless
in
this case, however sometimes a case can go straight to court.

Would a AtCourtID be any use?

Thanks
Andy.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200907/1