SQLTeam.com | Weblogs | Forums

Small Database model Suggestion

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)

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

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

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

please see i added more details !!!

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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

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

Yes, Great. this is very helpful. Thank you again.