Are relationships correct?

Can anyone help me out in checking these relationships?

  • Each "processo" has an unique ID but what connects all is the "Claim ID" (Primary Key)
  • Each "processo" can have multiple "Passageiros"
  • Each "processo" can have only one "Companhia" (of many)
  • Each "processo" can have only one "Judicial" info
  • Each "Judicial" can have only one "Tribunais" (of many)

"Judicial", "Tribunais" are not usually filled right from the start, user fills them as he gets the info.

Should I, instead of adding "Claim ID" (primary key from "Processos) in "Passageiros" and in "judicial", add the foreign keys to "Processos"?

Thanks in advance.

NOTE: I've reduced the database tree view to keys

Instead of schema designs ,that could lead us the wrong way, in plain english without using any technical terms explain please the story you want to capture. Using examples like "if Neymar got in accident he has to go to a court case and this and that"

1 Like

We need to organize claims from clients. There is a "Processo" (Process) with the exclusive Claim number/reference from one or more "passageiros" (passengers). It is against one "Companhia" (Company). Further more, when Claim goes through, the State/Court gives several informations for that Process only (reference number, court fees, etc, that goes into "Judicial") and it assigns one specific Court ("Tribunais") to solve the matter.

Was I clear? :slight_smile:
There are more fields into each table, off course, but I don't think they are relevant.

You are still describing tables and columns. Make it clear as if you are explaining it to a person that does not know anything technical

Trying again:
We are representing people who made a complaint against a Company (Companhias). They come, say what happened, and we start the Process (Processo) and assign a specific reference to the Claim. When things need to go the Judicial way, we submit the Claim and receive specific info/data for that case, like fees, dates, etc including the name of the assigned Court (Tribunal).

how many people can be on the complaint?

One and more than one (no limit) :slight_smile:

do you have categories of complaints or all just plaint text.
please list out types of complaints?

The type of complaints are in a dropdown list not associated to a table. Just custom values that are predefined on the frontend

probably best to have them in db. what if tomorrow there is a new complaint category? You will change front end just for that?

Anyways you mention Passageiros, are people that make complaint always passengers? Could someone report against the company but was not a passenger?

No, just passengers.
Ok, that's a good point. I'll add a table for the type of complaint :slight_smile:

How is ClaimID populated? Random number or alpha numeric?

Just number. It's given to us from a department above us, it's not sequential and it's the number for what the Process is known.

I'm sorry, now I saw that you asked me to list the type of complaints, right?
Delay, Cancelation, Detour

what is a Judicial vs a tribunal, are those some sorts of courts in your country?
Which one is higher?

here is a start, I am using sample data for explanation

use sqlteam

create table dbo.Passageiros(PassageiroId int identity(1,1), 
firstname nvarchar(150), lastname nvarchar(150))

insert into Passageiros
select 'Juan', 'Fejuad' union
select 'Neymar', 'Chora bebê' union
select 'Escobar', 'Accidente' 

create table dbo.Companhia(CompanhiaId int identity(1,1), 
NomeDaCompanhia nvarchar(150))
insert into Companhia
select 'Robo Taxi' union
select 'No Red Lights'  union
select 'Fejuad'

create table processo(processoId int  identity(1,1), ClaimID int not null, 
CompanhiaId int, datadeemissão datetime, notes nvarchar(max))
select '72223', 1, getdate(), 'Driver was lost' union
select '72224', 2, getdate(), 'Drive was too fast' 

create table processoPassageiros(processoId int, PassageiroId int)
select processoId, p.PassageiroId
  from processo
  cross apply (select * From Passageiros) p --All passengers on one claim
  where ClaimID = 72223

create table judicial(judicialid int  identity(1,1), Judicialname nvarchar(50))
insert into judicial
select 'Jardins' union
select 'Aclimação'

create table processojudicial(judicialid int, processoId int)
insert into processojudicial
select 1, 1  --Jardins, 72223

create table tribunal(tribunalid int  identity(1,1), tribunalname nvarchar(50))
insert into tribunal
select 'Jardins' union
select 'Aclimação'

create table tribunalprocess(tribunalid int, processoId int)
select processoId, t.tribunalid
  from processo
  cross apply (select * From tribunal where tribunalname = 'Jardins') t --case 72223 went to Jardins court
  where ClaimID = 72223

  drop table Passageiros
  drop table Companhia
  drop table processoPassageiros
  drop table processo
1 Like

Court (Tribunal) stores court names from different parts of the country. Judicial is just the name of the table where we add judicial related info like deadlines, fees, if it was contested or not, if we won or lost..

What if a case moves from one court to another. Doyou neee to keep history of case?

It's very rare, till now it was just because of an error submiting the claim, but it could happen! It started on one and changed to another one. Now that you mentioned it, could be nice keeping the track of that... But it is not mandatory since it's very rare