I am designing a db for our Law office. I want to have tables for different types of Civil Cases-ie Divorces, Deeds, Contracts, Personal Injury, Auto Accident, Slip and Fall, Product Liability, etc. The divorce, Deeds, tables are easy but I am having difficulty deciding on the personal injury tables. Heres my dilemna. Can I get away with one or two Personal Injury tables even though there will be blank data fields. For example, I will need a field for the name of the product in a Producrs Liability field. This field will be blank in all other Personal Injury Cases. I know the rule is you don't do that but my alternative is 15-20 tables for every concievable Personal Injury Case- Auto Accidents, Slip and Falls, Fraud, Prouduct Liability, Accounting, Attorney, Dental and Medical Malpractice, etc. This is in addition to other civil case tables, criminal case tables and federal case tables. Anyone have a suggestion?
Table Design
- Thread starter Gilrucht
- Start date
- Local time
- Today, 15:48
- Joined
- Feb 28, 2001
- Messages
- 30,566
Look up the Access Help topic "OUTER JOIN" as part of this exercise. Also look up the NZ function as a way to handle null fields as the result of an OUTER JOIN that had no joiners.
The way to minimize space in tables is complex but Access has all the tools you need to do it right.
What you need to do is split your tables according to common parts and non-common parts. You have a case table. You have a case number as the prime key and the driving force to keep the parts together. Now you put the case-generic stuff in the case table. Case number, date accepted, etc.
You will have TWO kinds of relationships here. Both are in the one-to-many category but for different reasons.
You can have more than one client, so you have a one-to-many client table that gives each client a record. The record is marked with the case number table. This is an example of the one-to-many relationship where you can have many records in a "child" table that match up to a single "parent" table. If you have a single client, it is OK to have a single client record. Just 'cause it is one-to-many, you don't have to take "many" literally. "Many" can also mean "could be more than one" - not "will be more than one."
The OTHER kind of one-to-many is that "many" could mean NONE. Which is where the OUTER JOIN comes into play. Build supplemental tables based on the case number as the link. Doesn't matter whether multiple details can be associated with the case. When you build a JOIN query using the case number table and one of these case-specific detail tables, the OUTER JOIN will return the parent record whether the child record exists or not. Then you have to use the NZ function to control what you see when you are talking about a type of case other than the one described in that particular supplemental table. For instance, if it is a slip-and-fall, you would not expect to see anything from the product liability supplement.
Now, why does this help you? If you do this so that everything fits into a single query BUT the query joins to some things that might be non-existant, the OUTER JOIN returns the partly unmatched record and the NZ function protects against the nulls that will be returned for unmatched records. Which means that when the query is not open, the unmatched items take up zero space. They don't exist except when this query is open.
Now, to control what is in each type of supplemental table, you need a data entry form specific to each table. But using the master case-number data as the linking point, you should be able to bring together what you need to see. How you display it will be up to you, but the starting point is to know how to gather the data for display.
Note that if this approach would exceed 255 fields in the ultimate query, you will have to take a totally different approach. No query, either as an end in itself or as an underlying contributor to another queyr, can exceed 255 fields.
The way to minimize space in tables is complex but Access has all the tools you need to do it right.
What you need to do is split your tables according to common parts and non-common parts. You have a case table. You have a case number as the prime key and the driving force to keep the parts together. Now you put the case-generic stuff in the case table. Case number, date accepted, etc.
You will have TWO kinds of relationships here. Both are in the one-to-many category but for different reasons.
You can have more than one client, so you have a one-to-many client table that gives each client a record. The record is marked with the case number table. This is an example of the one-to-many relationship where you can have many records in a "child" table that match up to a single "parent" table. If you have a single client, it is OK to have a single client record. Just 'cause it is one-to-many, you don't have to take "many" literally. "Many" can also mean "could be more than one" - not "will be more than one."
The OTHER kind of one-to-many is that "many" could mean NONE. Which is where the OUTER JOIN comes into play. Build supplemental tables based on the case number as the link. Doesn't matter whether multiple details can be associated with the case. When you build a JOIN query using the case number table and one of these case-specific detail tables, the OUTER JOIN will return the parent record whether the child record exists or not. Then you have to use the NZ function to control what you see when you are talking about a type of case other than the one described in that particular supplemental table. For instance, if it is a slip-and-fall, you would not expect to see anything from the product liability supplement.
Now, why does this help you? If you do this so that everything fits into a single query BUT the query joins to some things that might be non-existant, the OUTER JOIN returns the partly unmatched record and the NZ function protects against the nulls that will be returned for unmatched records. Which means that when the query is not open, the unmatched items take up zero space. They don't exist except when this query is open.
Now, to control what is in each type of supplemental table, you need a data entry form specific to each table. But using the master case-number data as the linking point, you should be able to bring together what you need to see. How you display it will be up to you, but the starting point is to know how to gather the data for display.
Note that if this approach would exceed 255 fields in the ultimate query, you will have to take a totally different approach. No query, either as an end in itself or as an underlying contributor to another queyr, can exceed 255 fields.
- Local time
- Today, 15:48
- Joined
- Feb 28, 2001
- Messages
- 30,566
I'll add another thought...
In the past I have had cases similar to yours when dealing with physical process sensors. Building energy management and oil/gas pipeline projects, among others. A LONG time ago. We at first said, "Oh darn! there are so many different aspects of these controls and sensors and observables... we need so many different things to track them all." But we allowed ourselves to reconsider structures more loosely so that we were able to identify parts that were common even though they had totally different names for the two items.
I understand that product liability and personal injury cases have totally different elements. Yet there is another way to view this, involving inversion of your storage scheme. Think of the way Access actually stores object properties...
Suppose, for example, you had your case table and ONE attributes table. BUT one of the attribute fields was a definition/type code for the attribute.
So you could have a record that said,
case 1245, "Client", "Joe Blow"
case 1245, "Client", "Mike Schmoe"
case 1245, "Client", "Jane Doe"
case 1245, "Witness", "Ima Nosygirl"
case 1245, "Judge", "Roy Bean"
case 1245, "Section", "A"
In the master record for case 1245, you would have a code for case type. In a case type table, you would have a code that corresponded to case types. Like, case type "SF" is slip-and-fall, case type PL is "product liability", case type "TGM" is tort gold-mine, etc. Then you could have a table of attribute codes that corresponded to things that could be present for each type of case, where the case type code is a foreign key to the case-type table and the individual records with that case-type are things you might see as atrributes of the case.
You could create one table for each type of thing you can store. For instance, a "currency" attributes table. A "text" attributes table. A "LONG" attributes table. A "dates" table. (You might consider that sometimes, a narrative or MEMO field should be in a separate table because having memo fields can sometimes screw you up.) In this case, you have a second code in the "attributes list" table that showed the data type of the attribute, which would let you find the correct attributes table.
Link them all with case number, an attribute code, and the value in the indicated format. So three fields each: Case, Code, Value.
The queries for this get a bit ugly, but with judiciously chosen (pardon the minor pun) queries, you can find what you need with sub-reports for any section that normally needs to be listed because of many-to-one attributes. By splitting the fields up as sub-reports, you can qualify the lists based on their sources. No one source table would exceed the limits. I.e. - by splitting the tables only as far as attribute type, you assure that the total query record doesn't exceed 255 fields or 2048 bytes, either of which would kill the query.
This is an advanced concept, but it is workable if you invert your logic just a little bit.
When searching for clients, you would search the TextAttributes table for attribute type-code "Client". When searching for product names, you would search the TextAttributes table for attribute type-code "Product Name". Obviously, there is a lot of set-up to this. The beauty is that if you take on a new type of case, all you do is add entries to the attributes table.
OK, how would you report this stuff? Several ways come to mind. In the ultimate report, you could even do UNION queries where you have, say, ...
SELECT CaseNum, AttribName, CStr( AttribValue ) FROM LongAttribs
UNION
SELECT CaseNum, AttribName, Format( AttribValue, "ShortDate" ) FROM DateAttribs
UNION
SELECT CaseNum, AttribName, AttribValue FROM TextAttribs
UNION....
Look up UNION queries to see this in action.
Now, if you REALLY want to get fancy, include a priority for grouping purposes, making all attribute tables have a fourth field. Then when doing the report, you can sort by priority first and attribute name SECOND, so that way you can force your client names to be at the top even if you have attributes with names starting with A or B.
In the past I have had cases similar to yours when dealing with physical process sensors. Building energy management and oil/gas pipeline projects, among others. A LONG time ago. We at first said, "Oh darn! there are so many different aspects of these controls and sensors and observables... we need so many different things to track them all." But we allowed ourselves to reconsider structures more loosely so that we were able to identify parts that were common even though they had totally different names for the two items.
I understand that product liability and personal injury cases have totally different elements. Yet there is another way to view this, involving inversion of your storage scheme. Think of the way Access actually stores object properties...
Suppose, for example, you had your case table and ONE attributes table. BUT one of the attribute fields was a definition/type code for the attribute.
So you could have a record that said,
case 1245, "Client", "Joe Blow"
case 1245, "Client", "Mike Schmoe"
case 1245, "Client", "Jane Doe"
case 1245, "Witness", "Ima Nosygirl"
case 1245, "Judge", "Roy Bean"
case 1245, "Section", "A"
In the master record for case 1245, you would have a code for case type. In a case type table, you would have a code that corresponded to case types. Like, case type "SF" is slip-and-fall, case type PL is "product liability", case type "TGM" is tort gold-mine, etc. Then you could have a table of attribute codes that corresponded to things that could be present for each type of case, where the case type code is a foreign key to the case-type table and the individual records with that case-type are things you might see as atrributes of the case.
You could create one table for each type of thing you can store. For instance, a "currency" attributes table. A "text" attributes table. A "LONG" attributes table. A "dates" table. (You might consider that sometimes, a narrative or MEMO field should be in a separate table because having memo fields can sometimes screw you up.) In this case, you have a second code in the "attributes list" table that showed the data type of the attribute, which would let you find the correct attributes table.
Link them all with case number, an attribute code, and the value in the indicated format. So three fields each: Case, Code, Value.
The queries for this get a bit ugly, but with judiciously chosen (pardon the minor pun) queries, you can find what you need with sub-reports for any section that normally needs to be listed because of many-to-one attributes. By splitting the fields up as sub-reports, you can qualify the lists based on their sources. No one source table would exceed the limits. I.e. - by splitting the tables only as far as attribute type, you assure that the total query record doesn't exceed 255 fields or 2048 bytes, either of which would kill the query.
This is an advanced concept, but it is workable if you invert your logic just a little bit.
When searching for clients, you would search the TextAttributes table for attribute type-code "Client". When searching for product names, you would search the TextAttributes table for attribute type-code "Product Name". Obviously, there is a lot of set-up to this. The beauty is that if you take on a new type of case, all you do is add entries to the attributes table.
OK, how would you report this stuff? Several ways come to mind. In the ultimate report, you could even do UNION queries where you have, say, ...
SELECT CaseNum, AttribName, CStr( AttribValue ) FROM LongAttribs
UNION
SELECT CaseNum, AttribName, Format( AttribValue, "ShortDate" ) FROM DateAttribs
UNION
SELECT CaseNum, AttribName, AttribValue FROM TextAttribs
UNION....
Look up UNION queries to see this in action.
Now, if you REALLY want to get fancy, include a priority for grouping purposes, making all attribute tables have a fourth field. Then when doing the report, you can sort by priority first and attribute name SECOND, so that way you can force your client names to be at the top even if you have attributes with names starting with A or B.
Doc,
I love it(and your sense of humor) It sounds fancy as heck and I'd love to be able to do it but I'm just a little ole newbee who is just trying to make a db for my law office. I want to make it right and i want it to be as professional as possible. Thats why I;m taking the time to read the boards and ask questions . I certainly appreciate the help but I;m afraid I'm going to have to pass on that suggestion. It is wayyyyyyyy over my head. Heck I have a city,state,zip lookup table with every zipcode in the US stored in it and I am having trouble figuring out how to use it. Thats how inexperienced I am. Thank you for your time though.
I love it(and your sense of humor) It sounds fancy as heck and I'd love to be able to do it but I'm just a little ole newbee who is just trying to make a db for my law office. I want to make it right and i want it to be as professional as possible. Thats why I;m taking the time to read the boards and ask questions . I certainly appreciate the help but I;m afraid I'm going to have to pass on that suggestion. It is wayyyyyyyy over my head. Heck I have a city,state,zip lookup table with every zipcode in the US stored in it and I am having trouble figuring out how to use it. Thats how inexperienced I am. Thank you for your time though.
