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"
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?
There are more fields into each table, off course, but I don't think they are relevant.
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).
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
go
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
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..
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
