what tables to create

[mecca]

Hi I am trying to create the tables for a road toll company and I'm not sure how to go about it. The tables I have come up with so far are:

Customers Table: Customer_ID, Cust_FName, Cust_LName, Address 1, Address 2, Town/City, County, Cust_Account No., Cust_Sort Code, Cust_Branch

Vehicle Table: Reg_Number, Customer_ID, Class_ID, Make, Model, Colour

Vehicle Type Table: Class_ID, Vehicle Type, Toll_ID, Toll_Cost

Toll Table: Toll_ID, Toll_Name

Crossing Table: Crossing_ID, Toll_ID, Reg_Number, Date, Time

Do I need to incorporate more tables etc? The amount of the toll depends on the vehicle type for example, whether it is a car, a motorbike or a bus. The toll is also different on the different types of road. Customers may have more than one vehicle registered to them etc and a monthly bill detailing the amount will be sent to the customer.

Was wondering if was on the right track or if I have omitted specific pieces of information?

[TheShabz]

After first glance, I have a couple questions.
1. why is Toll_ID in the vehicle type table?
2. what is the difference between customer_ID and Cust_Account No? I dont see Account No used anywhere.

Also, I'm not a fan of spaces and special characters (!@#$%^&*?/) in my field names. it makes writing queries a bit more laborious.

[mecca]

Cust_Account No is the bank account number of the customer - maybe I should chagne that.

Toll_ID is in vehicle type because the toll amount for each type of vehicle is different depending on the toll they are on, for example, the amount on a city toll would be different than the amount on a rural toll.

[TheShabz]

Dont you take care off the type of road in your crossing table? you have toll id and reg number in that table already.

[mecca]

The crossing table deals with the date and time of the vehicle going through a toll crossing. I thought I would need to determine the different type of roads and how much the class of vehicle would cost on that road, for example:

A car on a rural road would be $2
A car on a city road would be $3

Is that wrong?

[TheShabz]

I'm under the assumption that "Rural" would have a Toll_ID as would "City". Also, thinking about it, if the road type changes the cost, then you cannot have Toll_Cost in the vehicle ID.

I would create another table TollCost that would support a Many to Many relationship between Vehicle Type and Toll

TollCostID (PK, autonum)
Vehicle Type (FK)
Toll ID (FK)
cost

now each vehicle type on each road type would have its cost laid out. So in your crossing table, you would refer to the RegNum, and the TollCostID instead of Toll ID, along with the date and stuff.

catch my drift?

[mecca]

I think so. So instead the tables would be:

Customers Table: CustID, FName, LName, Add 1, Add 2, town/city, county, bankacc no, Sort Code, Branch

Vehicle Table: Reg Number, ClassID, Make, Model, Colour

Crossing Table: CrossingID, TollID, Reg Number, Date, Time

Toll table: TollID, TollName

TollCost Table: TollCostID, TollID, vehicleTypeID, Cost

VehicleType Table: vehicleTypeID, VehicleType

[TheShabz]

Crossing Table would have TollCostID instead of TollID that way you can include the cost in.

[mecca]

thanks for that. All of the tables connect now in a one to many realtionship. I just need to check whether it will give me redundant data or not.

Again thanks for all the help

[TheShabz]

Yep. Keep me posted if there are any issues. If it all works out, go ahead and mark the thread solved so others can use it as well.