Database railway reservation system

[shehryar]

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!

[timothybard]

What are all the primary keys you are using? There may be a conflict in your use of primary keys.

Secondly, can each train go on only one journey? If not, then I would recommend using the following design:

<station table>
stationid
station_name

<Journey table>
journeyid
trainid
stationid

<train table>
trainid
seat_type
seatno

Please note that I removed journeyid from the train table and added trainid to the journey table. This will allow each train to be associated with more than one journey.

Lastly, this is the design I would use to track seats:

<station table>
stationid
station_name

<Journey table>
journeyid
trainid
stationid

<train table>
trainid

<seats table>
seatid
trainid
seat_type

<seat availability>
seatid
date/time occupied
date/time available

For the last table, if there is no record in that table for a particular seat, then the seat is available and was never occupied. If the most recent record in that table for any particular seat has a null value for available, then the seat is occupied. If the most recent record in that table for any particular seat is not null, then the seat is available.

[shehryar]

well i got the seating problem solved, now i want to solve the journey part.. i understand you put trainid in journey.. but the train table can contain the journeyid.. journey is a set of stations.. like for example:

train id 001
journeyno 1

journey = 1 station = 1
journey = 1 station = 2
journey = 2 station 5
journey = 2 station 6
journey = 3 station 5
journey = 3 station 7 and so on

the problem with my design is that in the journey table i have two columns as follow

<journey>
journeyno
stationid

now i need both of them to act as a primary key, this issue is solved but when i create a relation ship with train table to journey it gives error

the relation im building is
<traintable.journeyno> to <journeytable.journeyno>
but it wont allow me unless i create relationship with both the columns of journey.. but i dont need stationid in train table when i have the journeyno there.. the design you suggested can apply this rule easily but my question is why i need trainid in the journey table?

[kronass]

in this case you need to add journey station table
<journey_stations>
journey_id (fk)
station_id (fk)

use the same schema that timothybard provided but in <Journey table> remove station_ID and add <journey_stations>
in result you will get

<station table>
stationid (pk)
station_name

<Journey table>
journeyid (pk)
trainid (fk)

<journey stations> --> many to many relation
journey_id (fk)
station_id (fk)

<train table>
trainid (pk)

<seats table>
seatid (pk)
trainid (fk)
seat_type

<seat availability>
seatid (fk)
date/time occupied
date/time available

[shehryar]

Great!!! i think this will do.. leme try and ill post back if problem solved but i think it is.. thanks so much..

[shehryar]

ok got one question

suppose there is a train2 following the same journey as train1 then according to the design journey_id in journey table is a primary key.. it wont allow me..

<Journey table>
journeyid (pk)
trainid (fk)

<journey stations> --> many to many relation
journey_id (fk)
station_id (fk)

[kronass]

yes that is right it will not allow you
because every train must have it's unique journey id even if the journey path is the same
so if you have other journey for other train you have to create the journey id and then specify the stations for the new journey
even if the path was the same.
if you want the same journey to be specified to more then one train then you should do the following

first remove trainid from Journey table and create many to many relation between train and journey
as the following

<Journey table>
journeyid (pk)

<train_journeys>
train_journeysID (pk) preferred but not must
journey_id (fk)
train_id (fk)

so if you want to add same journey to other trains you can use the second method or you can stick with the first one both will work the thing that will decide which suits better is the requirements.

regards