• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

relationship table

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
james121
Asked:
james121
1 Solution
 
James ElliottManaging DirectorCommented:
I need more info.

Try changing the join type to type 2.

Thanks

Jell
0
 
spcmnspffCommented:
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
 
spcmnspffCommented:
Sorry I meant to say "we need to normalize this data", it's already denormalized. =)  Anyway the recomendations are good. . .
0
 
nico5038Commented:
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
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now