?
Solved

relationship table

Posted on 2002-04-18
5
Medium Priority
?
291 Views
Last Modified: 2012-08-14
hi everyone new member

I'm cleaning up a database built before me and have a table called
discovery the table looks like this


    (primary key)   case
                    plaintiff deposition
                    vehicle inspection
                    plaintiff expert(1)
                    plaintiff expert(2)
                    plaintiff expert(3)
                    plaintiff expert(4)
                    co-def 1
                    co-def1-expert(1)
                    co-def1-expert(2)
                    co-def1-expert(3)
                    co-def-expert(4)
                    and so on to co-def 4
                    dcc expert (1)
                    dcc expert(2)
                    dcc expert(3)
                    dcc expert(4)
                    dcc rogs on cod
                    dcc rogs on cod due
                    plaintiff expert report due
                    plaintiff expert report served
                    co-def med expert report due
                    and so on

looking at discovery relationship with case info table it has a one-to-one relationship so that means i should add it to case info but in the table sometimes thier are zero to two co-def and if thier is a co-def maybe they have just one expert is their any way to build the tables to fix this problem?
0
Comment
Question by:james121
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 6951127
I need more info.

Try changing the join type to type 2.

Thanks

Jell
0
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 6951249
Okay we need to denormalize this data.  The way I see it, you need three more separate  tables:

CoDefendent (Actually this should be just defendent.)
(CodefNumber Int,
 Case Text,
 Name Text)
Composite Primary key on CodefNumber and case

Plaintiff
(PalintiffNumber int,
 Case text,
 Name text)
Composite Primary key on PlainiffNumber and case

Expert
(ExpertNumber Integer,
 Case Text,
 Name Text,
 CoDefNumber Integer (Allow Nulls),
 PlantiffNumber Integer (Allow Nulls),
 ReportServed DateTime,
 ReportDue DateTime)
Composite Primary key on ExpertNumberNumber and case

In each of these tables multiples are allowed per case. Now in your case table you get rid of Paintiff, Expert and Codefendent related fields.  And a query with a simple join will return all the info you need.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 6951252
Sorry I meant to say "we need to normalize this data", it's already denormalized. =)  Anyway the recomendations are good. . .
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7119828
for james121

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: spcmnspff  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7134582
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question