Database Design for a Train Booking System

[@DatabaseStar]

Bonus PDF: This ERD, descriptions of each table and column with sample data, and SQL scripts to create all of the tables on your own database: www.databasestar.com/dbdesign/?

[@miinishbat]

The diagram looks amazing! I am developing a train travel webapp, I did it in a different way but your diagram helps me improve my system, I have already taken into consideration that the user can buy several tickets at the same time

[@DatabaseStar]

That's good to hear you've found this video useful and have used it to improve your own design!

[@rbhambriiit]

Good stuff. I am learning system design from your playlist. Thanks!

[@DatabaseStar]

Great to hear!

[@marinomartin9920]

Great diagram and walkthrough, Ben! One thing to note: You'll want to be sure to capture "Origin" and "Destination" or a directional equivalent on the Journey table, as the station stops along the NY > BOS journey may not be the exact same sequence as the BOS > NY journey in the reverse order.

[@DatabaseStar]

Thanks! That's a great point, something to definitely consider and to probably update the diagram to allow.

[@luke27456]

Great stuff. Thanks for the awesome content! I'd be interested to see how you solve for the seating capacity being adjusted by an overlapping route. For example a route from station A -> B -> C. How would you account for a booking of the route from station B -> C? There should be one less seat available from station A -> B -> C, right?

[@DatabaseStar]

You're welcome! That's a good question. I'm not sure. It may be best to break the routes into station by stations, and when a booking is made for a start and end station, then it's stored as occupied for those stations but not for other stations.

[@anishashaji4133]

hi Ben , the diagram is amazing. So simple and clear. Could you please add performance tuning for SQLs in future?

[@DatabaseStar]

Thanks! Sure, I do have some videos on my channel already about SQL performance and I’ll create more in the future.

[@attarannaufal4308]

thanks, im developing train booking ticket right now. Student exercise

[@DatabaseStar]

Glad it has helped!

[@labiribiri1901]

Amazing video but i believe you made a mistake. DepartureTime is not the same as Arrival time so you're suffering integrity issues. Also, there is alot of normal form violations which makes this table insufficient. If im wrong or overlooking something please let me know, thanks.

[@DatabaseStar]

Thanks for the feedback! Yes you’re right, this design assumes arrival and departure are at the same time, but in reality they aren’t, so we could add another column. What are the other normal form violations that you see?

[@trentcox9239]

these are fantastic. any chance of providing a design with auditable tables? im a chronic sufferer of 'YAGNI' and lose sleep and time mostly over features noone asked for but that little voice in my head says is a good idea. case in point, when i see this design i think it could be further enhanced by somehow allowing for the ability to have an audit trail of sorts to backtrack through booking status changes, payment/refund changes, etc. trying to find good and scaleable solutions to auditing that doesn't include an expensive third party are few and far between sadly....definately lookin forward to that on this channel. keep up the solid work these are awesome

[@DatabaseStar]

Thanks! Yes I do have a video on audit tables coming up soon. I had another comment recently about it (perhaps it was you as well) so I'll create it soon. I think this design could be enhanced with audit tables as you have said - it's a good point.

[@trentcox9239]

@@DatabaseStar oh dang that was me. completely forgot about that. alright ill stop nagging for an audit table video now

[@DatabaseStar]

No problem! Thanks for giving me more info about what you need anyway.

[@MehediHassan-pn5uc]

Request to make a Db design for udemy like LMS platform! Thank you for your awesome contents 🖤

[@shobhitmittal77]

yes please, that would be great.. There, we can include features like Quiz - MCQs, % completion of the course, locking the new videos, etc.

[@MehediHassan-pn5uc]

@@shobhitmittal77 Yes

[@DatabaseStar]

Thanks for the request! Sure I can make a video about designing a database for a LMS.

[@MehediHassan-pn5uc]

@@DatabaseStar thanks for taking my request into your consideration... looking forward to it 🖤

[@iammahabub]

Thank you so much for this video!

[@DatabaseStar]

You're welcome!

[@nitheshms6515]

This video is really amazing, Would like to know more on how we can enhance this if seat_id is selected by passanger and not assigned internally by management app

[@DatabaseStar]

Thanks! Yeah you may have to add seat numbers or seat id to a carriage and have the application run a query to show available seat numbers.

[@claudiu1901]

Great video! I was just wondering to what extent is the database normalized? Is it FN3?

[@DatabaseStar]

Thanks! Yes it should be in third normal form.

[@Htm-lh7ij]

tnx man, is there any way to download this pdf?

[@DatabaseStar]

Yes, you can get the PDF here: www.databasestar.com/dbdesign/

[@YacineBenjedidia-wm6pw]

Very interesting exercise thanks

[@DatabaseStar]

Glad you enjoyed it

[@SabaMahamed-wx1zm]

Can please help or make project train database access

[@DatabaseStar]

What do you mean? This video is about a train database.

[@SabaMahamed-wx1zm]

@DatabaseStar full project database access please 🙏

[@DatabaseStar]

I still don’t understand what you mean. Have you watched this video you’re commenting on?

[@SabaMahamed-wx1zm]

Yes

[@gauravsrivastava17]

Great

[@DatabaseStar]

Thanks!