I am trying to figure out the best database schema for the following. I will be using Postgres along with Nodejs unless there is something better suited to this task.
I apologize if the answer is obvious. I am new to all of this.
I have a list of train-id’s and a list of intermodal containers. I need to be able to query by intermodal container to check which train-id it is on, and also to query by train-id to get a list of all intermodal containers on this train. I would also like to be able to query historical information.
The issue I have is the train-id’s repeat once per month so I can’t use them as a primary key, or query just based on train-id as it would return containers on the train-id from previous months as well.
The best that I have come up with so far is to create a composite key consisting of the date of departure and the train-id and query based on this to get the list of containers. The day of departure is already included in the train-id, however the month of departure is not. I’m not sure how to make this user friendly though as preferably the user would not have to specify the month of departure.
For querying by container-id I believe I could just limit the result to 1 to only get the most recent train it is on, or is there a better way of doing this?
There will be other details stored in this database as well such as ETA’s, car numbers, etc. However the above is what I’m having difficulty with currently.