Database Design Idea

A

andycambo

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.
 
K

Keith Wilby

andycambo said:
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.
Click to expand...

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
 
N

NG

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.
 
A

andycambo

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.
Hi,
.....
Click to expand...
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.
Click to expand...

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.
 
F

Fred

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.
 
G

Gina Whipp

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
 
D

DUNNER7

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

Del
 
A

andycambo via AccessMonster.com

Gina said:
Andy,

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

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.
 
G

Gina Whipp

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
Click to expand...


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
 
A

andycambo via AccessMonster.com

http://i630.photobucket.com/albums/uu29/andycambo/relationships.jpg

Picture of relationships ^^^

I have manged to get a little bit of work done today. I've come to the point
were I have to create the relationships between the tables. I'm hoping my
tables are okay so it will just be a case of choosing the right relationships.


I'm not totally sure what I'm doing the right thing here though. I've read
my book a couple of tmes but it isn't totally clear, to me, what I should do.
I don't know what join type to use as there are 3 choices.

1 Only include rows where the joined fields from both tables are equal.

2. Include ALL records from 'tblMatters' and only those records from
'tblAppearance' where the joined fields are equal.

3 Include ALL records from 'tblAppearance' and only those records from
'tblMatters' where the joined fields are equal.

So, going from the picture from above. How to I relate the tblMatters to
tblResults, tblAppearance, tblAtPolice, tblAtCourt and tblClientProfile
(which I've just realised I've missed the field off). What Join type am I to
use?

Just to clear things up. 1 client can have several matters but each matter
will only have one appearance, result and court or police station record.

Thanks in advance,
Andy.
 
G

Gina Whipp

Andy,

1. Do not worry about the Join Type. Simply check...

Enforce Refrential Integrity
AND
Cascade Update Related Fields

2. To make this as easy as I can think of... Join the little yellow key
(PK) to the table with the same name/same field name in the adjoining
table(FK).

Example: tblClientProfile-cpClientID to tblMatters-mClientID

Keep doing that until you have created all relationships.

3. Remove tblResults_1 located all the way to the right. You can have more
then one join on a single table, so no need to put in two same tables.

4. Reading material which may be easier to understand then the book...

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--
Gina Whipp

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

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

andycambo via AccessMonster.com

Gina said:
Andy,

1. Do not worry about the Join Type. Simply check...
Click to expand...

Thanks once again for your reply.

I was doing what you said but I kept getting an error message. What I didn't
realise, until I read a few articles, was the Foreign Key needed to be a long
number. In my case it wasn't so I was getting repeated error messages. I've
now sorted that and here is my relationship table.

http://i630.photobucket.com/albums/uu29/andycambo/relationships_v2.jpg

I don't think I've missed anything off this.

Thanks for the links, those articles will be handy material.
Andy.
 
J

John W. Vinson

Cascade Update Related Fields
Click to expand...

I'd use this with caution, Gina. It is never necessary if you're creating a
relationship from an Autonumber primary key to a Long Integer child table,
since an autonumber field is never updateable; if you're joining on some sort
of natural key, you should still be very careful about using cascade updates,
since it will take effect only when you *edit* the value of a main table
field, and it will cause all related records to be changed, which may not be
the result you want.
 
G

Gina Whipp

John,

Thanks for the tidbit. I have never had a problem but then I rarely use
Autonumber and normally I want my related records updated when editing the
main table. BUT you bring a good point I am passing on advice without
taking into consider that everyone doesn't program like me...

--
Gina Whipp

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

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

John W. Vinson

John,

Thanks for the tidbit. I have never had a problem but then I rarely use
Autonumber and normally I want my related records updated when editing the
main table. BUT you bring a good point I am passing on advice without
taking into consider that everyone doesn't program like me...
Click to expand...

Do you (routinely) edit the value *of a primary key field*? OK, I'll trust you
- you know what you're doing! - but it's something I'd NEVER let my users do.

Of course cascading updates applies only to editing that field; you can edit
other fields in a parent table without CU getting into the mix.
 
G

Gina Whipp

John,

In one case I have no choice because the Client INSISTS they have the
ability to do! (I am refering to once Client in particular) and of course,
he pays when he *messes* the database up which is a routine with him bit
that is another story. Then there was the case where data that was brought
in form another source with matchind PK's to the existing data and I had to
run some update queries to change the PK's so I could get it into the table.
(I told them let's try not to do that again.

But other then that NO, NEVER, have you lost your mind... Okay I go
overboard. I just got in the habit of doing it that because of the above
scenarios. And since doing it that way never *hurt* anything I never
stopped. Of course in my latest adventure I set the PK as Autonumber and
gave them a *make-believe* PK because they wanted to edit it and I'm seeing
how that goes... The don't have a Purchase Order number until AFTER the PO
gets approved for ordering.



--
Gina Whipp

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database Design/Change Help Requested 2
function like vlookup in infopath 0
Basic Layout and Design Help 3
String Manipulation Query 2
Sales Offer Summary Database 10
Database design/ tables design 1
New Database 9
new database. need help with tables and relationships please? 1

Top