-
Notifications
You must be signed in to change notification settings - Fork 6
/
db.dbml
executable file
·128 lines (112 loc) · 2.2 KB
/
db.dbml
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
Table passengers as Pass {
id int [pk, increment]
first_name varchar
last_name varchar
id_no int
email varchar
phone_number varchar
password varchar
created_at datetime
updated_at datetime
}
Enum role_names {
driver
fleet_manager
helpdesk
snm_manager // service and maintenance
admin
}
Table roles {
id int [pk, increment]
name role_names
}
Table employees as Emp {
id int [pk, increment]
first_name varchar
last_name varchar
id_no int
email varchar
phone_number varchar
employee_id varchar
password varchar
roles_id int
created_at datetime
updated_at datetime
}
Ref: Emp.roles_id < roles.id // one-to-many
Table locations as Loc {
id int [pk, increment]
name varchar
lat float // latitude
lng float // longitude
}
Table routes as Rt {
id int [pk, increment]
name varchar
loc1_id int [ref: > Loc.id]
loc2_id int [ref: > Loc.id]
}
Table buses {
id int [pk, increment]
reg_no varchar
make varchar
yom int // year of manufacture
capacity int
routes int [ref: > Rt.id]
}
Table journeys {
id int [pk, increment]
bus_id int [ref: > buses.id]
route_id int [ref: > Rt.id]
fare int
departure datetime
}
Table tickets {
id int [pk, increment]
passenger_id int [ref: > Pass.id]
bus_id int [ref: > buses.id]
seat_number int
payment_id int [ref: > payments.id]
from int [ref: > Loc.id]
to int [ref: > Loc.id]
valid_until datetime
}
Ref: bookings.ticket_id - tickets.id
Table bookings {
id int [pk, increment]
passenger_id int [ref: > Pass.id]
bus_id int [ref: > buses.id]
from int [ref: > Loc.id]
to int [ref: > Loc.id]
ticket_id int
cancelled boolean [default: false]
created_at datetime
updated_at datetime
}
Table payments {
id int [pk, increment]
passenger int [ref: > Pass.id]
amount float
mpesa_id varchar
time datetime
}
Table couriers {
id int [pk, increment]
journey_id int [ref: > journey.id]
sender_name varchar
sender_phone varchar
recipient_name varchar
recipient_phone varchar
packages_id int [ref: > packages.id]
}
Table packages {
id int [pk, increment]
type package_types
weight float
description text
}
Enum package_types {
fragile
perishable
bulky
}