Sample of database dictionary

database-dict-sample.md

Dictionary for database pdd

• Server: PostgreSQL localhost:5436, version 14.0
• Local time stamp: 2022-07-15T12:01:44.0784710+02:00
• Schema: public

Table of Contents

• Table public.business_areas
• Table public.business_roles
• Table public.companies
• Table public.company_areas
• Table public.company_reviews
• Table public.countries
• Table public.employee_records
• Table public.employee_status
• Table public.people
• Table public.person_roles
• Table public.users

Tables

Table public.business_areas

Business areas that companies may be invloved.

Column		Type	Nullable	Default	Comment
#id	PK	smallint	NO	auto increment
#name		character varying	NO
#name_normalized	IDX	character varying	NO		lowercased

⇡

Table public.business_roles

Roles in a team that employees are specialized working with.

Column		Type	Nullable	Default	Comment
#id	PK	smallint	NO	auto increment
#name		character varying	NO
#name_normalized	IDX	character varying	NO		lowercased

⇡

Table public.companies

Column		Type	Nullable	Default	Comment
#id	PK	bigint	NO	auto increment
#name		character varying	NO
#name_normalized	IDX	character varying	NO		lowercased, trigram index
#web		character varying	YES
#linkedin		character varying	YES
#tweeter		character varying	YES
#company_line		character varying	YES		company moto
#about		character varying	YES
#country	FK ➝ countries.code, IDX	smallint	YES		headquaters country
#created_at		timestamp with time zone	NO	now()
#modified_at		timestamp with time zone	NO	now()
#created_by	FK ➝ users.id	bigint	NO	1
#modified_by	FK ➝ users.id	bigint	NO	1

⇡

Table public.company_areas

Companies - business areas.

Column		Type	Nullable	Default	Comment
#company_id	PK, FK ➝ companies.id, IDX	bigint	NO
#area_id	PK, FK ➝ business_areas.id	smallint	NO
#created_at		timestamp with time zone	NO	now()
#created_by	FK ➝ users.id	bigint	NO	1

⇡

Table public.company_reviews

Company reviews made by people.

Column		Type	Nullable	Default	Comment
#id	PK	bigint	NO	auto increment
#company_id	FK ➝ companies.id, IDX	bigint	NO		company reviewed
#person_id	FK ➝ people.id	bigint	YES		person reviewer
#review		character varying	NO		written review by a person
#score	CHECK (score IS NULL OR score > 0 AND score <= 5)	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	bigint	NO	1

⇡

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
#name_normalized	IDX	character varying	NO		lowercased
#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.

⇡

Table public.employee_records

History of employment in companies by people.

Column		Type	Nullable	Default	Comment
#id	PK	bigint	NO	auto increment
#company_id	PK, FK ➝ companies.id	bigint	NO
#person_id	FK ➝ people.id, IDX	bigint	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	bigint	NO	1

⇡

Table public.employee_status

List of possible statuses in regards to employment.

Column		Type	Nullable	Default	Comment
#id	PK	smallint	NO	auto increment
#name		character varying	NO
#name_normalized	IDX	character varying	NO		lowercased

⇡

Table public.people

Column		Type	Nullable	Default	Comment
#id	PK	bigint	NO	auto increment
#first_name		character varying	NO
#last_name		character varying	NO
#name_normalized	IDX	character varying	NO		first_name + ' ' + last_name + '\n' last_name + ' ' + first_name all in lowercase to enable both searches (staring with first or last name), trigram index
#employee_status	FK ➝ employee_status.id, IDX	smallint	NO
#gender	IDX	USER-DEFINED	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	bigint	NO	1
#modified_by	FK ➝ users.id	bigint	NO	1

⇡

Table public.person_roles

Person - business roles

Column		Type	Nullable	Default	Comment
#person_id	PK, FK ➝ people.id, IDX	bigint	NO
#role_id	PK, FK ➝ business_roles.id, IDX	smallint	NO
#created_at		timestamp with time zone	NO	now()
#created_by	FK ➝ users.id	bigint	NO	1

⇡

Table public.users

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

Column		Type	Nullable	Default	Comment
#id	PK	bigint	NO	auto increment
#email	IDX	character varying	NO		lowercased
#name		character varying	YES
#data		json	NO	'{}'::json	json data received from external auth provider
#providers		ARRAY	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	bigint	YES
#lockout_end		timestamp with time zone	YES
#created_at		timestamp with time zone	NO	now()

⇡