Small Database model Suggestion

dimaeuroasia · May 12, 2020, 12:00pm

Can you please suggest a database model (any number of tables/objects) for a Toll Road system to control:
A) Trips: Start Location, End Location, Start, End, Cost
B) Monthly Statement per Driver
C) Monthly Summary Statement for the company
D) Identify busiest section(s)

harishgg1 · May 12, 2020, 1:16pm

All depends on how and what you want to do with YOUR data

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Trips: Start Location, End Location, Start, End, Cost
-- table for this

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Driver Details
-- table for this

Monthly Statement per Driver
-- stored procedure for this

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Company details
-- table for this

Monthly Summary Statement for the company
-- stored procedure for this

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Busiest Sections
-- Table for this MAYBE

Identify busiest section(s)
-- stored procedure for this
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
identify Primary Key Foreign Key Relation Ships
betweeen

-- Look Up Tables ( Master Tables )
-- Transaction Tables .. Detail Tables ..

example ...

LookUp Tables
DriverId name
1 sam
2 Pam

detail transaction table
routeid driverid tripdate
1 1 2020-09-10
2 1 2020-09-12
1 2 2020-10-15

pk fk
pk = LookUp Table Driverid
fk =detail transaction table DriverId

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

dimaeuroasia · May 12, 2020, 1:18pm

Thank you so much for your suggestion. Great feed back. Much appreciated.

harishgg1 · May 12, 2020, 1:20pm

please see i added more details !!!

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
identify Primary Key Foreign Key Relation Ships
betweeen