Is this a good database design?

Question

I am planning to make a railway reservation project... I am maintaining following tables:

trainTable
(trainId,trainName,trainFrom,trainTo,trainDate,trainNoOfBoogies)...PK(trainId)

Boogie
(trainId,boogieId,boogieName,boogieNoOfseats)...CompositeKey(trainId,boogieId)...

Seats
(trainId,boogieId,seatId,seatStatus,seatType)...CompositeKey(trainId,boogieId,seatId)...

user
(userId,name...personal details)

userBooking
(userId,trainId,boogieId,seatId)...

Is this good design?

Answer 1

It could work, but there is room for improvement - depends what you are using it for and how you want to optimize it:

• Be consistent in naming your tables. You shouldn't name one table trainTable, another Boogie and a third one Seats. Either always have the "table", or never; either always use plural, or never. Try trains, boogies, seats.
• You don't have to repeat the tablename in the columnname - when referring to train.id, it's clear enough. But this is up for discussion, some people do. But be consistent: also use username and not just name
• Do you need an extra field traintable.trainNoOfBoogies? You can get this information by just COUNTING all boogies with that given trainID.
• Same thing for seats: do you need a Boogie.boogieNoOfSeats? You can just SELECT COUNT(*) from seats where boogieID = wantedId`.
• The seats table is linked to a boogie AND to a train? You can probably just link a seat to a boogie. The boogie itself will link to a train. If you put the link in more than once, you risk inconsistencies (but then again, you'd have to post the specs for me to see what you want...)

Proposal:

trains
(ID,name,from,to,date) PK(ID)

boogies
(ID,trainId,name,) CompositeKey(trainId,boogieId)...

seats
(ID, trainID, boogieID, status, type ) CompositeKey(trainId,boogieId,seatId)

users
(ID, name, ... personal details) PK (ID) 

userBookings
(ID, userId, trainId, boogieId, seatId) CompositeKey(trainId,boogieId,seatId)

If you want more tips, post a bit more information: what do you want to store, what do you want to do with it? What are example queries programmers will write against the database?