I am having a problem with a database that I am trying to create for a railway reservation system. some part is as follow :
<station table>
stationid
station_name
<Journey table>
journeyid
stationid
<train table>
trainid
journeyid
seat_type
seatno
now the problem is that i need a candidate type key for journey table i cant use a primary key in that table for journeyid because i want both the column together as unique. The problem after assigning the keys the table works fine, but when i create a relation of train table with the journey table it gives unique contraint error. also can anyone suggest me a better design.
Another thing I cant solve is how should i check the system for seat availability? any idea would be fine please thanks!