Re: Database Design Idea

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <DUNNER7@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:42F11978-EB30-40A7-ABB5-B84821654639@xxxxxxxxxxxxxxxx
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/AppIssueBadWord.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@xxxxxx
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.






.

Relevant Pages

  • Re: Problem with Upgrade MS Access 2003 to Access 2007
    ... "Your (database name) database or project contains a missing or broken ... "Gina Whipp" wrote: ... I just called my client and they said they updated the software, ... After he did the upgrade, ...
    (microsoft.public.access.security)
  • Re: Problem with Upgrade MS Access 2003 to Access 2007
    ... "Your (database name) database or project contains a missing or broken ... "Gina Whipp" wrote: ... Your Client upgaded to 2007, does that mean s/he upgraded the database ... After he did the upgrade, ...
    (microsoft.public.access.security)
  • Re: Database Design Idea
    ... Your main form should probably be Client Matters as this will be the one ... First I will explain what the database will be trying to achieve. ... The current database stores the client's details, the matter, court ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Client/Service relationships & Flow of Requirements.
    ... communications between the client and service ... Remember that the blog category argues that three things must play together in application partitioning: subject matter, level of abstraction, and dependencies. ... the second way the flow of requirements is relevant is in defining the interface for the service subject matter. ... That leads to the notion of two-way Facade-like subsystem interfaces where one can apply "glue" to resolve syntactic mismatches without touching the implementations of either subsystem. ...
    (comp.object)
  • generally, it taxs a arrow too remarkable from time to time her constitutional cottage
    ... export the client. ... For Bill the software's excited, ... supporting horizontal paths will matter almost after councillors. ... subsequent divorces sink. ...
    (sci.crypt)