Relationship for a table that may or may not relate to another table

I was wondering if someone could offer some assistance. I'm designing a database and I was wondering how this should be handled. My scenario involves a vehicles table storing information about a fleet of vehicles (e.g make, model, current mileage etc.) a maintenance table to store information about any maintenance carried out on a vehicle. And finally an operating costs table, this will store any costs relating to a vehicle (e.g Fuel, Servicing, Repairs etc.).

My problem is some of the records in maintenance need to link to a maintenance record and some don't. E.g Servicing is an operating cost and would be recorded on the maintenance log however fuel is an operating cost by wouldn't be recorded on the maintenance log.

What would the best way to handle this?

Here are the tables at the moment, however this only allows an operating costs to relate to a maintenance record:

Vehicles (Vehicle开发者_运维知识库ID, Make, Model, Mileage, MOT Due)

Maintenance (MaintenanceID, VehicleID fk, Description)

OperatingCost (OperatingCostID, MaintenanceID fk, Cost)

Thanks in advance.

J

---

If I've understood you correctly it seems like you want TWO tables for OperatingCost with OperatingCostID being the primary key in both of them. Depending on the type of cost you would populate one or both of these tables:

OperatingCostMaintenance (OperatingCostID, MaintenanceID)

OperatingCost (OperatingCostID, Cost)

---

With your current schema, you can relate a maintenance record to a cost record with a join, exactly as you would if you were going the other way:

select * from OperatingCost where MaintenanceID = ?

Does every bit of maintenance have a cost? In that case, you could treat the Maintenance table as an optional detail table on OperatingCost, and use the same ID for both. Maintenance's ID column would be both a primary key and a foreign key referencing OperatingCost.