My DB is designed around 4 groups of tables all linked to an initial main table. It is a DB for a Law office. The 4 groups are the cateogories of cases the firm handles- State criminal cases, state civil cases, federal criminal cases and federal civil cases. The initial main table is general client info- name, address, phone etc. Within each of the 4 groups will be rables for the specific types of cases and the details for each. For example, there will be tables for auto accidents, breach of contracts, medical malpractice etc in the state civil group. there will also be tables for related info such as defendants, injuries, doctors, insurance, etc. The firm will never have more than 400 clients so the total number of records will never be that large. However, there will be more than 100 tables in some of the 4 cateogories and close to 200 tables in one of the 4. Im wondering if this too many tables for 1 db. Can a db have too many tables? Would I be better off making 4 db's -one for each group and linking them? I have seen threads on linking db's though I really dont how to do it. I would really appreciate any advice and suggestions.
too many tables?
- Thread starter Graybeard
- Start date
M
Mike375
Guest
The data base I use on Access 95 has just under 400 tables and as far as I can see it causes no problems.
I also have some small DBs that are extracts from the main data base and this is so the size is small enough to email the .mdb file. The functioning of the small DBs is no different to when the same stuff is run within the main data base.
Mike
I also have some small DBs that are extracts from the main data base and this is so the size is small enough to email the .mdb file. The functioning of the small DBs is no different to when the same stuff is run within the main data base.
Mike
Mile-O
Back once again...
- Local time
- Today, 12:36
- Joined
- Dec 10, 2002
- Messages
- 11,316
You'll probably be able to whittle that down exponentially with a good bit of normlising of the data. From the way you've described it you are creating tables for specifics (i.e. defendants, injuries, doctors, insurance) when you should be thinking in classes of data.
Gray,
I've had a great evening drinking beer and shooting pool, and I've
decided to post on a topic that I know nothing about.
Even with your specs, that's not too many tables for a database.
Any database can hanle that. But, if you design it with that many
tables handling all of the different case types, etc. YOU won't be
able to "traverse" the data in a rational manner.
The database software can handle it, but you can't!
For instance, if you wanted a report, you would have to specifically
join (what, 30 - 40 tables!). Maybe some other report might be only
10 - 20 tables.
I know nothing about the legal profession, so take this with a grain
of salt, but you "simply" have to organize your information so that
you can retrieve/maintain it in an orderly fashion.
Your work seems to revolve around cases. I'm lost already. People
are Plaintiffs and Defendants at the same time. Can one case "melt"
into another? I'm gonna just take a stab, but it will be better than
hundreds of tables.
Fortunately, this advice is free.
Your app revolves around cases:
tblCase
=======
CaseID
CaseNumber - "(Roll your own", if it needs to be pretty)
FileDate (Has to start sometime)
ReconcileDate (Has to end sometime)
Verdict - FK (Told you I don't know)
CaseTypeMajor - FK (Federal, Civil, ...)
CaseTypeMinor - FK (Accident, ...)
CoprorationID - FK
Plaintiff - FK (to tblpeople)
ProcuterCounselID - FK (to Prosecuters)
Defendant - FK (to tblpeople)
DefendantCounselID - FK (to Defense)
OtherStuff
tblVerdicts
-----------
VerdictID
Verdict (Guilty, Innocent, Hung)
tblCaseTypeMajor
================
tblCaseTypeMajorID
CaseTypeMajor (Federal, Civil)
tblCaseTypeMinor
===========
tblCaseTypeMinorID
CaseType - (Accident, Auto, Contract ...)
tblCorporations
===============
CorporationID
CorporatonName (Individual, SomeCompany, SomeOtherCompany, ...)
tblPeople
=========
PersonID - AutoNumber
CorporationID - FK (Individual, SomeCompany, SomeOtherCompany, ...)
PersonInfo
tblProsecutors
==============
ProsecutorID
ProsecutorName
OK, I'm done! Sorry that I didn't finish.
Gray, there are no easy answers. I just spent a half/hour on this.
Once I realized that you can have many prosecuters/defendants, I'm
toast! This is a pretty deep subject, (BUT, don't do hundreds of tables).
I'm not even the Design person here, (Hi Pat!). But you don't need
hundreds of tables here. Prolly 'bout 33 1/2.
Sure wish I knew your app better, what's really important is to traverse
the data, knowing only the "drill-down" aspect".
Federal - Civil - Automobile - Ford - Explosion (Get status, defendants, etc.)
Without knowing the "UNDERLYING TABLE STRUCTURE"!!!!!!!!!!!!!!
Basically, Stewart was right, you can condense some of your tables into
"categories". Someone who doesn't know the application should be able to
traverse a set of "search combos" and meaningfully do some research.
You don't need hundreds of table, just a select few.
Well, that's my post for the day, maybe should have restricted it to something
that I had a clue about, but ...
btw,
The beer/pool thing worked out OK!
Wayne
I've had a great evening drinking beer and shooting pool, and I've
decided to post on a topic that I know nothing about.
Even with your specs, that's not too many tables for a database.
Any database can hanle that. But, if you design it with that many
tables handling all of the different case types, etc. YOU won't be
able to "traverse" the data in a rational manner.
The database software can handle it, but you can't!
For instance, if you wanted a report, you would have to specifically
join (what, 30 - 40 tables!). Maybe some other report might be only
10 - 20 tables.
I know nothing about the legal profession, so take this with a grain
of salt, but you "simply" have to organize your information so that
you can retrieve/maintain it in an orderly fashion.
Your work seems to revolve around cases. I'm lost already. People
are Plaintiffs and Defendants at the same time. Can one case "melt"
into another? I'm gonna just take a stab, but it will be better than
hundreds of tables.
Fortunately, this advice is free.
Your app revolves around cases:
tblCase
=======
CaseID
CaseNumber - "(Roll your own", if it needs to be pretty)
FileDate (Has to start sometime)
ReconcileDate (Has to end sometime)
Verdict - FK (Told you I don't know)
CaseTypeMajor - FK (Federal, Civil, ...)
CaseTypeMinor - FK (Accident, ...)
CoprorationID - FK
Plaintiff - FK (to tblpeople)
ProcuterCounselID - FK (to Prosecuters)
Defendant - FK (to tblpeople)
DefendantCounselID - FK (to Defense)
OtherStuff
tblVerdicts
-----------
VerdictID
Verdict (Guilty, Innocent, Hung)
tblCaseTypeMajor
================
tblCaseTypeMajorID
CaseTypeMajor (Federal, Civil)
tblCaseTypeMinor
===========
tblCaseTypeMinorID
CaseType - (Accident, Auto, Contract ...)
tblCorporations
===============
CorporationID
CorporatonName (Individual, SomeCompany, SomeOtherCompany, ...)
tblPeople
=========
PersonID - AutoNumber
CorporationID - FK (Individual, SomeCompany, SomeOtherCompany, ...)
PersonInfo
tblProsecutors
==============
ProsecutorID
ProsecutorName
OK, I'm done! Sorry that I didn't finish.
Gray, there are no easy answers. I just spent a half/hour on this.
Once I realized that you can have many prosecuters/defendants, I'm
toast! This is a pretty deep subject, (BUT, don't do hundreds of tables).
I'm not even the Design person here, (Hi Pat!). But you don't need
hundreds of tables here. Prolly 'bout 33 1/2.
Sure wish I knew your app better, what's really important is to traverse
the data, knowing only the "drill-down" aspect".
Federal - Civil - Automobile - Ford - Explosion (Get status, defendants, etc.)
Without knowing the "UNDERLYING TABLE STRUCTURE"!!!!!!!!!!!!!!
Basically, Stewart was right, you can condense some of your tables into
"categories". Someone who doesn't know the application should be able to
traverse a set of "search combos" and meaningfully do some research.
You don't need hundreds of table, just a select few.
Well, that's my post for the day, maybe should have restricted it to something
that I had a clue about, but ...
btw,
The beer/pool thing worked out OK!
Wayne
M
Mike375
Guest
Graybeard
Wayne makes a very good point.
While I have close to 400 tables most of the tables are either copies of other tables, tables for drop down lists, tables from insurance companies for premium rate calculators, tables that are relate to financial accounts etc. In other words, several data bases in the one .mdb file.
My guess is that you have so many tables that potentially "relate" to each other because of a lack of familiarity with separating records via queries or macros or VB.
You may also being trying to make "perfect" tables that have no blank fields.
I could be very wrong but I get a feeling that what you currently have would be similar to the insurance person having a different table for each type of insurance benefit or policy.......as opposed to having all the policy beneft details in one table......and being able to call them up in a given category via a query.
Mike
Wayne makes a very good point.
While I have close to 400 tables most of the tables are either copies of other tables, tables for drop down lists, tables from insurance companies for premium rate calculators, tables that are relate to financial accounts etc. In other words, several data bases in the one .mdb file.
My guess is that you have so many tables that potentially "relate" to each other because of a lack of familiarity with separating records via queries or macros or VB.
You may also being trying to make "perfect" tables that have no blank fields.
I could be very wrong but I get a feeling that what you currently have would be similar to the insurance person having a different table for each type of insurance benefit or policy.......as opposed to having all the policy beneft details in one table......and being able to call them up in a given category via a query.
Mike
Thanks everyone. You are absolutely correct. One of the major reasons I have so many tables is because I tried to avoid blank fields. For example, I made a seperate main table for Corporate Defendants so I wound't have have blank fields for all the entries for date of birth, soc security no, spouse, etc.I suppose ther really isnt any reason I can'y use just one table and put a Corporate name field in my main Clientinfo file. I have a lot of tables like that. Thet are only seperate to avoid blank fields. The second major reason is I tried to limit the size of the tables. I have a lot of tables with similar fields that could be combined but if I did I would have one table with 30 fields instead of 2 tables with 15 fields. If table size and blank fields are not an issue I can cut down the number of my tables by more than half , maybe 2/3. Are they concerns?
- Local time
- Today, 06:36
- Joined
- Feb 28, 2001
- Messages
- 30,054
Greybeard, I'll ask you to bear with me 'cause I'm from Louisiana, where we still have remnants of the Napoleonic code on the books. Where judges don't have to follow precedent, where it can often happen that one person can be convicted of taking a bribe that another person is acquited of making.
But, hey, we've got the best food festivals in the world and beer is cheap.
Back to your problem...
You need to re-visit your analysis. First and foremost, you only have two kinds of case. Billable and pro bono.
Fed vs. state doesn't change the results. That's just an address for the court room. Fed vs. state, at most, changes the reference numbers of the applicable laws under which the action occurs.
You'd think that Civil vs. Criminal makes a difference. In one case you've got guilty/not guilty. In the other case, you've got liable/not liable/fractionally liable. And ALL of them can be dismissed, declared mis-trials, settled without verdict, etc. But the truth is, that result is still representable from one single table where you can look up a code to see what that result really means.
Step away from your design and look for commonalities. Then look for qualifiers that distinguish from the commonalities. Try this design in your head for a while:
tblBaseCase
long (autonumber, prime key) CaseNum - your firm's case number
integer (and foreign key) CaseType - used in a lookup table
long (and foreign key) Manager - one person in a firm is usually assigned as the responsible party regardless of who works it
date WhenAccepted - first time this case was placed on your books.
tblStaff
long (autonumber, prime key) StaffNum- code for every staff member
some text fields with person's name, address, phone, etc.
tblCaseWork
long (fk) CaseNum
long (fk) StaffNum
date WorkedOn
real Hours
Use tblCaseWork to record the time your staff spends. If a person works on a case, you record the hours here.
tblBillRates
long (fk) StaffNum
date RateStarts
date RateEnds
real BillPerHour
A junction between the casework table and the bill rate table (with attention to the date a billing rate applied) will let you generate your bills.
OK, now let's talk about your other tables. What do you need to know?
Well, how about interviews? You probably DON'T want to keep every frimpin' word they say in the db, but you could assign a file folder to interview and include a hyperlink to the file holding same. Interviews, being free-form, tend to be indescribable in fixed-format ways anyhow. You could have a list of interviews.
tlbInterview
long (pk, autonumber) - interview number
long (fk) CaseNum
text fields for person interviewed
code field for "this is good interview", "this is indifferent interview", "don't ever let this turkey on the stand", etc.
What about the claims/lecal charges associated with a case?
tblCaseDtls
long (fk) CaseNum
integer ChgClm - you enter this as Claim #1 or Charge #1
text LawRef - reference to the law. Like, in So. Cal, 187PC (Murder). This can be as complex a legal reference as you wish.
text PopularName - if the LawRef was CA 187PC, this would be "Murder"
integer PenaltyType - codes you can look up from an (implied here) penalty table, where some entries are civil penalties - judgements in cash, dation in paement (sorry if I spelled the Latin wrong). Others are criminal - county jail, state pen, federal slammer, free injections, etc.
OK, what about civil variants for the above? Torts usually claim injury under a particular law, too. So you'll need a law reference there too. And a popular name. Penalty types I've already addressed.
OK, results...
For each charge you will eventually need a verdict/result. This can be (from an implied tblResults-type table) Guilty, Liable in full, Non Guilty, Not Liable, etc. Where your locale allows guilty of lesser included charges, you have to include the lesser charges in the original charge table. Where your locale allows fractional liability, you have to allow for a number. Where you have sentence ranges, you might have two numbers. For civil fractional liability, you might need only one number - the percentage.
I'm blue-skying here, but the idea is that you need to look at these different aspects of a case not for their differences but for their similarities.
You would use a bunch of lookup tables to define possible results, possible penalties, etc.
OK, let's complicate matters... One case, multiple clients.
tblClients
long (pk, autonumber) ClientNum
name, address, phone, DOB, how much money we can soak him for ... forgive me, I digress...
tblCliCase
long (fk) CaseNum - for this case, ...
long (fk) ClientNum - this client was ...
integer (fk) ClmChg - charged with or sued for ....
notes
This is a junction table that can be extended to include verdicts if you wish.
In other words, it might just be an extension of the original results table. It says, we got Joe Schmo off but Bill Shill got thirty years, etc.
Let's look at motions filed
tblCaseMotions
long (fk AND part of PK) CaseNum
integer (part of PK with CaseNum) MotNum - which motion this is
text describing motion
code/text describing result of motion.
I'll suggest that you try to design this on paper while looking at similarities, not differences. Don't worry that sometimes you have blanks. That's a GOOD thing. What you DON'T want to have is no place to put something. THAT is when you've got tsuris. (Yiddish for "agony")
I don't see 400 tables as meaningful. I agree with WayneRyan. The more tables you've got, the worse off you are in trying to make sense of it all.
I see lots of lookup tables where the differences between civil and criminal or between state and federal simply imply different code numbers on the case in one of its attribute fields. Where different penalties are just different line items in a lookup table. Where each client has a single client number, where each case has a unique case number, where each staff member/partner has a unique ID code, etc.
I also see that where you see differences, you MIGHT want to step back a bit and either (a) realize it is a quibble or (b) realize that the item causing your heartburn is so unique that it might NEVER be fully described in any other way but with a big hompin' memo field anyway. But tables with special notes can be sparsely defined. I.e. not ever case will necessarily have one of those fields.
But, hey, we've got the best food festivals in the world and beer is cheap.
Back to your problem...
You need to re-visit your analysis. First and foremost, you only have two kinds of case. Billable and pro bono.
Fed vs. state doesn't change the results. That's just an address for the court room. Fed vs. state, at most, changes the reference numbers of the applicable laws under which the action occurs.
You'd think that Civil vs. Criminal makes a difference. In one case you've got guilty/not guilty. In the other case, you've got liable/not liable/fractionally liable. And ALL of them can be dismissed, declared mis-trials, settled without verdict, etc. But the truth is, that result is still representable from one single table where you can look up a code to see what that result really means.
Step away from your design and look for commonalities. Then look for qualifiers that distinguish from the commonalities. Try this design in your head for a while:
tblBaseCase
long (autonumber, prime key) CaseNum - your firm's case number
integer (and foreign key) CaseType - used in a lookup table
long (and foreign key) Manager - one person in a firm is usually assigned as the responsible party regardless of who works it
date WhenAccepted - first time this case was placed on your books.
tblStaff
long (autonumber, prime key) StaffNum- code for every staff member
some text fields with person's name, address, phone, etc.
tblCaseWork
long (fk) CaseNum
long (fk) StaffNum
date WorkedOn
real Hours
Use tblCaseWork to record the time your staff spends. If a person works on a case, you record the hours here.
tblBillRates
long (fk) StaffNum
date RateStarts
date RateEnds
real BillPerHour
A junction between the casework table and the bill rate table (with attention to the date a billing rate applied) will let you generate your bills.
OK, now let's talk about your other tables. What do you need to know?
Well, how about interviews? You probably DON'T want to keep every frimpin' word they say in the db, but you could assign a file folder to interview and include a hyperlink to the file holding same. Interviews, being free-form, tend to be indescribable in fixed-format ways anyhow. You could have a list of interviews.
tlbInterview
long (pk, autonumber) - interview number
long (fk) CaseNum
text fields for person interviewed
code field for "this is good interview", "this is indifferent interview", "don't ever let this turkey on the stand", etc.
What about the claims/lecal charges associated with a case?
tblCaseDtls
long (fk) CaseNum
integer ChgClm - you enter this as Claim #1 or Charge #1
text LawRef - reference to the law. Like, in So. Cal, 187PC (Murder). This can be as complex a legal reference as you wish.
text PopularName - if the LawRef was CA 187PC, this would be "Murder"
integer PenaltyType - codes you can look up from an (implied here) penalty table, where some entries are civil penalties - judgements in cash, dation in paement (sorry if I spelled the Latin wrong). Others are criminal - county jail, state pen, federal slammer, free injections, etc.
OK, what about civil variants for the above? Torts usually claim injury under a particular law, too. So you'll need a law reference there too. And a popular name. Penalty types I've already addressed.
OK, results...
For each charge you will eventually need a verdict/result. This can be (from an implied tblResults-type table) Guilty, Liable in full, Non Guilty, Not Liable, etc. Where your locale allows guilty of lesser included charges, you have to include the lesser charges in the original charge table. Where your locale allows fractional liability, you have to allow for a number. Where you have sentence ranges, you might have two numbers. For civil fractional liability, you might need only one number - the percentage.
I'm blue-skying here, but the idea is that you need to look at these different aspects of a case not for their differences but for their similarities.
You would use a bunch of lookup tables to define possible results, possible penalties, etc.
OK, let's complicate matters... One case, multiple clients.
tblClients
long (pk, autonumber) ClientNum
name, address, phone, DOB, how much money we can soak him for ... forgive me, I digress...
tblCliCase
long (fk) CaseNum - for this case, ...
long (fk) ClientNum - this client was ...
integer (fk) ClmChg - charged with or sued for ....
notes
This is a junction table that can be extended to include verdicts if you wish.
In other words, it might just be an extension of the original results table. It says, we got Joe Schmo off but Bill Shill got thirty years, etc.
Let's look at motions filed
tblCaseMotions
long (fk AND part of PK) CaseNum
integer (part of PK with CaseNum) MotNum - which motion this is
text describing motion
code/text describing result of motion.
I'll suggest that you try to design this on paper while looking at similarities, not differences. Don't worry that sometimes you have blanks. That's a GOOD thing. What you DON'T want to have is no place to put something. THAT is when you've got tsuris. (Yiddish for "agony")
I don't see 400 tables as meaningful. I agree with WayneRyan. The more tables you've got, the worse off you are in trying to make sense of it all.
I see lots of lookup tables where the differences between civil and criminal or between state and federal simply imply different code numbers on the case in one of its attribute fields. Where different penalties are just different line items in a lookup table. Where each client has a single client number, where each case has a unique case number, where each staff member/partner has a unique ID code, etc.
I also see that where you see differences, you MIGHT want to step back a bit and either (a) realize it is a quibble or (b) realize that the item causing your heartburn is so unique that it might NEVER be fully described in any other way but with a big hompin' memo field anyway. But tables with special notes can be sparsely defined. I.e. not ever case will necessarily have one of those fields.
