Mixing cases and other non-case records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The system I'm trying to develop is designed, first, to store legal case
information in a db, and then be able to analyze that data and use it to make
recommendations/reports/etc.

However, it's not JUST cases that I need to collect the data about. In
addition, there are other issues that aren't technically "cases", but which
might be termed "issues" which, nonetheless, need to find themselves into the
db.

My vision for the system is that adding an "issue" would be pretty much
equivalent to adding a subset of the information required for entering a
case. So, where a case might require 25 fields to be be "added" to the
database, an "issue" might require 15 of those same 25 fields, but NOT
require the other 10 things.

Does that sound like an acceptable thing to do: have a case database that
also accepts "issues" (given the explanation I've given) but groups them in
the same main table such that you can search both groups together at once?

If it IS reasonable, then my second question is this: Cases have a unique
case number which could be used as the primary key. Issues, otoh, don't have
a "case number" at all since they aren't cases. If it's ok to have the
issues and cases kinda mixed in together, would I need a seperate column
(autonumber) to use for a primary key, or could I use the "case number"
column and somehow assign a random or sequential autonumber in those records
where no case number was inputted?

Thanks in advance,
cw
 
I would recommend two seperate tables, one for cases and the other for
issues. The only challenge with this design will be, as you said, searching
both tables, but htis is certainly possible without too much trouble.

If an issues eventually became a case, you could append the record from one
table to the other. If you wanted to retain the issue number value when it's
moved over, you wouldn't be able to update the autonumber column, so you'd
either have to not use autonumber in the cases table or create an issue
number column in the cases table into which you put the original value.

I hope this helps.
Barry
 
Thanks for the reply. I think I follow what you are saying. Being pretty
new with Access, and rdms as a whole, I get a little worried when I can't
comfortably conceptualize the ramifications of design choices. Right now,
I'm not sure how my queries would look if I wanted to search two tables for
records that matched the same criteria. I'm sure it's not a big problem
though.

Thanks again,
CW
 
To perform a two table search, you would create a union query that stacked
the records from both tables on top of each other and perform the search
against it.

Barry
 
Cheese_whiz said:
where a case might require 25 fields to be be "added" to the
database, an "issue" might require 15 of those same 25 fields, but NOT
require the other 10 things.
Click to expand...

Consider subclassing i.e. where you have two 'subclassed' tables, one
each for issues and cases respectively, plus a 'superclass' table for
the fields/columns common to both.

For Access/Jet SQL and standard SQL examples, see:

http://groups.google.com/group/microsoft.public.access.tablesdbdesign/msg/493623a65b79ace8
http://groups.google.com/group/comp.databases.theory/msg/2f2e1ff43b17682c

Jamie.

--
 
Thanks for the replies. Looks like I have a couple of more things to look
into. Funny how that works!

CW
 
Back
Top