Skip to content

Latest commit

 

History

History
616 lines (420 loc) · 44.2 KB

README.md

File metadata and controls

616 lines (420 loc) · 44.2 KB
Raw

Dictionary for database pdd

Table of Contents

Tables

Table public.business_areas

Business areas that companies may be involved.

Column Type Nullable Default Comment
#id PK smallint NO GENERATED ALWAYS AS IDENTITY
#name character varying NO
  • Stats for public.business_areas:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.business_role_types

Types or groups of business roles.

Column Type Nullable Default Comment
#id PK smallint NO GENERATED ALWAYS AS IDENTITY
#name character varying NO
  • Stats for public.business_role_types:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.business_roles

Roles in a team that employees are specialized working with.

Column Type Nullable Default Comment
#id PK smallint NO GENERATED ALWAYS AS IDENTITY
#name character varying NO
#type FK ➝ business_role_types.id smallint NO
  • Stats for public.business_roles:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.companies

Column Type Nullable Default Comment
#id PK uuid NO gen_random_uuid()
#name character varying NO
#web character varying YES
#linkedin character varying YES
#twitter character varying YES
#company_line character varying YES company moto
#about character varying YES
#country FK ➝ countries.code, IDX smallint NO headquaters country
#created_at timestamp with time zone NO now()
#modified_at timestamp with time zone NO now()
#created_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
#modified_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
  • Stats for public.companies:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.company_areas

Companies - business areas.

Column Type Nullable Default Comment
#company_id IDX uuid NO
#company_id PK, FK ➝ companies.id uuid NO
#area_id PK, FK ➝ business_areas.id smallint NO
#created_at timestamp with time zone NO now()
#created_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
  • Stats for public.company_areas:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.company_reviews

Company reviews made by people.

Column Type Nullable Default Comment
#id PK uuid NO gen_random_uuid()
#company_id FK ➝ companies.id, IDX uuid NO company reviewed
#person_id FK ➝ people.id uuid YES person reviewer
#review CHECK (review IS NOT NULL OR score IS NOT NULL) character varying YES written review by a person
#score CHECK (score IS NULL OR score > 0 AND score <= 5), CHECK (review IS NOT NULL OR score IS NOT NULL) smallint YES score 1-5
#created_at timestamp with time zone NO now()
#modified_at timestamp with time zone NO now()
#created_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
  • Stats for public.company_reviews:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.countries

Column Type Nullable Default Comment
#code PK smallint NO Contry ISO 3166 numeric code.
#iso2 IDX character(2) NO Contry ISO 3166 alpha-2 code.
#iso3 IDX character(3) NO Contry ISO 3166 alpha-3 code.
#name character varying NO
#culture character varying YES The CultureInfo class specifies a unique name for each culture, based on RFC 4646. The name is a combination of an ISO 639 two-letter lowercase culture code associated with a language and an ISO 3166 two-letter uppercase subculture code associated with a country or region.
  • Stats for public.countries:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.employee_records

History of employment in companies by people.

Column Type Nullable Default Comment
#id PK uuid NO gen_random_uuid()
#company_id FK ➝ companies.id uuid NO
#person_id FK ➝ people.id, IDX uuid NO
#employment_started_at date NO
#employment_ended_at date YES if this is null, it means person is still working there
#created_at timestamp with time zone NO now()
#created_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
  • Stats for public.employee_records:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.employee_status

List of possible statuses in regards to employment.

Column Type Nullable Default Comment
#id PK smallint NO GENERATED ALWAYS AS IDENTITY
#name character varying NO
  • Stats for public.employee_status:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.people

Column Type Nullable Default Comment
#id PK uuid NO gen_random_uuid()
#first_name character varying NO
#last_name character varying NO
#employee_status FK ➝ employee_status.id, IDX smallint YES
#gender IDX valid_genders user defined AS ENUM ('M', 'F') ➝ YES M or F
#email character varying YES
#linkedin character varying YES
#twitter character varying YES
#birth date YES
#country FK ➝ countries.code smallint YES
#created_at timestamp with time zone NO now()
#modified_at timestamp with time zone NO now()
#created_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
#modified_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
  • Stats for public.people:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.person_roles

Person - business roles

Column Type Nullable Default Comment
#person_id IDX uuid NO
#person_id PK, FK ➝ people.id uuid NO
#role_id IDX smallint NO
#role_id PK, FK ➝ business_roles.id smallint NO
#created_at timestamp with time zone NO now()
#created_by FK ➝ users.id uuid NO '00000000-0000-0000-0000-000000000000'::uuid
  • Stats for public.person_roles:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Table public.users

System users. May or may not be a person (in people records).

Column Type Nullable Default Comment
#id PK uuid NO gen_random_uuid()
#email IDX character varying NO lowercased
#name character varying YES
#data json NO '{}'::json json data received from external auth provider
#providers character varying[] NO '{}'::character varying[] list of external auth providers autorized this user
#timezone character varying NO timezone from browser
#culture character varying NO matching culture by browser timezone
#person_id FK ➝ people.id uuid YES
#lockout_end timestamp with time zone YES
#created_at timestamp with time zone NO now()
  • Stats for public.users:
Sequence Scan Index Scan Rows Vaccum Analyze
count=0 count=0 inserted=0, updated=0, deleted=0 last=, count=0 last=, count=0
rows=0 rows=0 live=0, dead=0 last auto=, rows inserted since=0 last auto=, rows updated since=0

⇡

Enums

Type name Values Comment Source
#public.valid_genders 'M', 'F' There are only two genders. /PDD.Database/Scripts/types/valid_genders.sql

⇡

Routines

Function company.company_details(_id uuid)

⇡

Function company.company_employees(_id uuid)

⇡

Function company.company_reviews(_id uuid, _skip integer, _take integer)

⇡

Routines

Function dashboard.chart_companies_by_country(_limit integer)

Number of companies by country. JSON object where labels are country names and it only have one series with the number of companies for each country. It show only first 9 countries and 10th is summed together as other.

  • Returns JSON schema: {"labels": [string], "series: [{"data": [number]}]"}

⇡

Function dashboard.chart_employee_counts_by_area(_limit integer)

Business areas, the number of employees for top 3 companies by highest number of employees. JSON object where labels are business area names and three series with number of current employees for each area, each searies for one company.

  • Returns JSON schema: {"labels": [string], "series: [{"data": [number], "label": string}]"}

⇡

Function dashboard.chart_employee_counts_by_year(_limit integer)

Top companies by number of employees for the last ten years. JSON object with only one series where labels are last ten years names and values have data for number of employees for each year and label as company name.

  • Returns JSON: {labels: string[], series: {data: number[], label: string}[]}

⇡

Function dashboard.top_experinced_people(_limit integer)

  • Returns record
RECORD (
  id uuid,
  first_name character varying,
  last_name character varying,
  age integer,
  country character varying,
  countrycode smallint,
  countryiso2 character varying,
  years_of_experience integer,
  number_of_companies bigint,
  employee_status character varying,
  roles character varying[]
)

Top experienced people by the years of the working experience.

⇡

Function dashboard.top_rated_companies(_limit integer)

Top rated companies by the user score.

⇡

Routines

Function companies.business_areas()

  • Returns record
RECORD (
  value smallint,
  name character varying
)

select value and name from business_areas

⇡

Function companies.search_companies(_search character varying, _countries int2[], _areas int2[], _sort_asc boolean, _skip integer, _take integer)

Search companies by search string (name or company line), or by countries or areas selection. Result is pageable JSON response {count, data: [...]}

⇡

Function companies.search_countries(_search character varying, _skip integer, _take integer)

Search countries by name or iso2 or iso3. Result is pageable JSON response {count, data: [...]} Data record has value and name suitable for select type controls. Countries with companies are sorted first by name, followed by null record (separator) and then by countries without companies sorted by name.

⇡