Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nonwitty
Starting Member
1 Post |
 Posted - 2011-05-28 : 20:39:02
|
I'm currently doing an assignment for my course in database design and was looking for some advice on assigning keys in PSQL. I'm looking to create a form which will fetch travel, train and driver information from the databse. Here is the initial ER design: [url]http://dl.dropbox.com/u/23601298/IBIS-ERD.pdf[/url]It's a design for a small railways company's database. I'm not entirely sure how I should deal with the "Trains" table. I was thinking about creating an Train ID column for the primary key to link to the "Carriage" table. I know this would essentially "lock" the carriages to a certain train but this is a very simplified exercise to begin with. I'm also uncertain how to implement the "seating_capacity"/"standing_capacity" columns since they have to be calculated from the other table.Another problem is the "Driver" table. The driver has to be assigned to a train but they can drive many different trains, services and runs during the day. Should I create the foreign key to the "Run" table instead?I'm sorry if this is a bit long winded but I'm getting very frustrated with this  . Thank you for helping.These are the Assumptions:Each whole route is served by only one service(though a route may be wholly contained within another).For the purposes of simplicity, there are no differences in service for weekdays, weekends and holidays. You may choose to extend or change thisThere are no inconsistencies or break during the timetable days – the services are entirely regular. You may choose to extend or change thisA route is made up of a number of stops, in a particular sequence.Trains are either enhanced for use by those with disabilities or not – there are no degrees of enhancementA train is defined as a single engine pulling a specified number of carriagesA run is a single, specific instance of a service running along a routeEach driver may be able to drive multiple runs and services in a day, and may drive multiple trains to do so.Each train is assigned a single driver |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-05-30 : 09:49:48
|
| For the Train/Carriage issue, you probably want a table like:Journey--------JourneyIDTrainIDCarriageIDScheduleTime...I personally find Database Diagrams more helpful than ER Diagrams. |
 |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-05-30 : 09:58:36
|
quote:
Originally posted by denis_the_thief
For the Train/Carriage issue, you probably want a table like:Journey--------JourneyIDTrainIDCarriageIDScheduleTime...I personally find Database Diagrams more helpful than ER Diagrams.
No, that's not right. How about:Journies--------JourneyIDRouteIDScheduledTimeActualTimeJourneyTrains-------------JourneyIDTrainIDJourneyCarriages----------------JourneyIDCarriageID |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-05-30 : 10:06:48
|
quote:
Originally posted by denis_the_thief
quote:
Originally posted by denis_the_thief
For the Train/Carriage issue, you probably want a table like:Journey--------JourneyIDTrainIDCarriageIDScheduleTime...I personally find Database Diagrams more helpful than ER Diagrams.
No, that's not right. How about:Runs--------RunIDRouteIDScheduledTimeActualTimeRunTrains-------------RunIDTrainIDRunCarriages----------------RunIDCarriageID
|
|
|
|
|
|
|
|
|
Help needed with implementing an ER diagram!