moz-demog-api/dbschema.puml at api-prod · therakius/moz-demog-api · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
@startuml moz_database
skinparam {
    Nodesep 200
    Ranksep 200
    Linetype ortho
}

skinparam entity {
    BackgroundColor LightBlue
    FontSize 14
    ArrowThickness 1
}

enum user_roles {
    ADMIN
    CLIENT
}

enum apk_status{
    ACTIVE
    REVOKED
    EXPIRED
}


entity year {
    + Id: serial [pk]
    --
    + year: integer
    + president: text
    + data_state: text
}

entity country_data {
    + Id: serial [pk]
    --
    + total_area_in_sqkm: integer
    + capital_city: text
    + official_language: text
    + independence_date: date
}

entity provinces {
   + id: serial [pk]
   --
   + province_name: text
   + area_in_sqkm: integer
   + population_density: integer
   --
   + year_id: integer [FK] references year(id)
}

entity population_per_thousand{
    + id: serial [pk]
    --
    + per_thousand_total: numeric(7,1)
    + per_thousand_male: numeric(7,1)
    + per_thousand_female: numeric (7,1)
    --
    + province_id: integer [FK] references provinces(id)
}

entity population_percentual_structure {
    + id: serial [pk]
    + percentual_total: numeric (4, 1)
    + percentual_male numeric (4, 1)
    + percentual_female: numeric (4, 1)
    --
    + province_id: integer [FK] references provinces(id)
}


entity country_pop_indicators {
    + id: serial [pk]
    --
    + total_population: numeric(7, 1)
    + male_population: numeric(7, 1)
    + female_population: numeric(6, 1)
    + urban_percentual: numeric (3,1)
    + sex_ratio: numeric(3,1)
    + gross_mortality_rate: numeric (4, 1)
    + gross_birth_rate: numeric (3, 1)
    + growth_rate: numeric (4, 1)
    + median_age: numeric (3, 1)
    --
    + year_id: integer [FK] references year(id)
}

entity dependency_rate {
    + year_id: integer [PK] references year(id)
    --
    + total: numeric (3,1)
    + young: numeric (3,1)
}

entity life_expectancy_at_birth {
    + year_id: integer [PK] references year(id)
    --
    + average_life_expectancy: numeric (3,1)
    + male_life_expectancy: numeric (3,1)
    + female_life_expectancy: numeric (3,1)
}

entity infant_mortality {
    + year_id: integer [PK] referenceces year(id)
    --
    + average_infant_mortality: numeric (3, 1)
    + male_infant_mortality: numeric (3, 1)
    + female_infant_mortality: numeric (3, 1)
}

entity use_user {
    + user_id: integer [PK]
    --
    + user_name: text
    + user_email: text [unique]
    + user_password_hash: text
    + user_role: user_roles
    + user_created_at: timestamp
    + user_modified_at: timestamp
    + user_created_by: text
    + user_modified_by: text
    + user_is_active: boolean
}


entity apk_api_keys {
    + apk_id: integer [pk]
    ---
    + apk_key_hash: text
    + apk_name: text
    + apk_created_at: timestamptz
    + apk_status: apk_status
    + apk_modified_at: timestamptz
    + apk_end_date: date
    + apk_last_used: timestamptz
    --
    + user_id : int [FK] referenceces use_user(user_id)
}

entity l_logs {
    + l_id: int [PK]
    ---
    + l_created_at: timestamp
    + l_request_method : varchar(10)
    + l_url: text
    + l_endpoint: text
    + l_request_query: text
    + l_response: text
    + l_status_code: int
    --
    + l_apk_id: int [FK] referenceces apk_api_keys(apk_id)
}

entity user_reset_tokens {
    + urt_id: integer [pk]
    --
    + urt_hash: text
    + urt_created_at : timestamptz
    + urt_expires_at : timestamptz
    + urt_is_used : integer
    --
    + user_id: int [FK] references use_user(user_id) on delete cascade
}

'relashionships:
year --{ provinces
provinces --{ population_per_thousand
provinces --{ population_percentual_structure
year -- life_expectancy_at_birth
year --{ country_pop_indicators
year -- dependency_rate
year -- infant_mortality
use_user ---{ apk_api_keys
apk_api_keys ---{ l_logs
use_user ---{user_reset_tokens

@enduml