New Locomotive Database

[richard]

This project is making quiet progress and I have some initial specs written out. I may be able to start development in December.

The plan is to have an online database of all LNER locomotives. The more I thought about this, I decided it should be very flexible. We need to store things like numbers, shed allocations, classes, names, rebuilds, etc. However there are few consistencies between classes as to what we may want to store. Therefore I have chosen a database of "tags". A tag will have a date and a small amount of information - eg. a number change, a livery change, an allocation. There will also be a locomotive database which has some very basic information about each engine. As numbers are not consistent and do change, internal identifiers will mark which locomotive a tag refers to. These identifiers would be invisible to a user, and only visible to editors for debugging purposes.

Yes I have chosen a relational database model. I considered an RDF type scheme - this is surprisingly close to my tag concept, but I decided RDF could not hold the data efficiently. PHP will be used for the web interface.

There is a huge amount of data to enter, and I know there have been some volunteers already. More will be welcome You need good reliable sources of data. Data entry reliability and spelling are important. Editors will be able to add and edit entries in the tags database. I'll also add a closed board on these forums for the editors.

My database design has been chosen to be very flexible. In reality only a small number of queries will be coded as web pages. Eg. "query all locomotives that fit these criteria", "all information about this locomotive", "all locomotives at this shed on this date". On a regular basis (probably weekly), I will create an automatic process that dumps selections of the database data into static pages that will be linked from the locomotive pages. These will replace the current lists of named locomotives.

Current Database Design

I currently have four tables planned. Two are 'auxiliary' tables. More auxiliary tables can be added as needed.

Class
This lists all legitimate class names for the database. It will also list the URL to the relevant page for this class. This will allow all class names in the web front end to be consistent. Eg. avoid typos and formatting differences.

Shed
This would list each shed code, shed, and start/end dates. I'm not too well versed with shed codes - is there anything else I need?

Locomotives
This will list the Build Date, Works No., Manufacturer (Works or Company), and internal identifier. Note we do not need things like name, number, withdrawal date, etc. because these will all be in the tags database.

Tags
Okay I probably need a new name for this database
This is where the real information is stored.

Each tag will store: Internal Locomotive Identifier, Date, Tag Type, Event/Observation, Information, and Comments.
All tags are dated, but the Event/Observation field will determine if the tag describes an observation or an event. Eg. a shed allocation could be an observation (X was at Y in 1948), or an event (X was allocated to Z in 1949).

The Information field would depend on the tag type - eg. it could hold a shed code, a new class, a name, etc.
As a future extension, it could include the local filename of a photograph or drawing. Ie. the database could be easily expanded to include photos.

The Tag Type determines what this tag refers to. This will be enumerated but the enumerations will be hidden from the user (basically there will be a list of possible presets). My current list is as follows:

Class
Livery
Name
Number
Owner
Shed Allocation
Tender No.
Boiler No.
Tender Type
Boiler Type
Repair
Rebuild
Withdrawn
Scrapped

More tag types can be added as needed.

Thoughts, comments? I might have been a bit technical but I have high hopes. I have chosen what I believe to be a powerful underlying data structure.


Richard

[52D]

I have the complete LNER alphabetical shed code list as used post 1923 you need to link this with the LMS based Alpha-numeric codes as per post 1948 period.
I also have a data base for allocations for Alnmouth, Berwick, Tweedmouth and sub sheds in the area from 1923 onwards.
I suggest you start the datadase by doing the following;
1. Describe the grouping in 1923 and explain how the LNER numbering scheme worked. I have a post that explains it .
2. Describe the Whyte wheel notation system adopted by the LNER and also the pre grouping names for classes before they were given an LNER classification.
3. For many years i have wanted an easy to follow system for LNER locos showing date of build, date of allocation to a particular shed, date of transfer to a new shed, dates of shopping, naming and numbering and finally date of withdrawl/place of scrapping.
I hope this helps you a little, I will be a volunteer to help you but will be overseas till 14th Jan.

[richard]

Thanks for the offer. Te way things are looking, it is unlikely I'll be ready for data entry before 14th January. I was thinking I might start before Christmas, but I'll also be away from around the 27th for about ten days.

Shed codes are something I don't know very much about, so I'd definitely be interested in the lists/databases that you have.

Are the allocations in electronic form? If so, we could probably massage them so I can "auto load" them into the database (although we'd probably have to cross reference them with some basic locomotive information - that could take some time).

#1: It sounds like this might be better as an "article" page?

#2: Again, the Whyte notation might be better for an "article". The scheme I have outlined above could be used to include pre-LNER classes (as a tag) although this would be harder to guard against typos. I'll think about it.
Articles describing class naming methodologies would be better suited to the new "constituent company" section.

#3: Hopefully my planned database arrangement will help here. The basic "locomotive query" would give what you describe and more. Listing it for all locomotives of a class would be a bit big, so you would probably have to "drill down" through the data to get a page with it on.
The queries could be re-arranged eg. all locomotives for a class, all locomotives for a shed, even all locomotives with an overhaul in a specific year. These would be listed as numbers with hyperlinks. Click on the number and you get the full database list for that locomotive.


Richard

[Bedders]

You might want to get in touch with Kenton who runs the http://www.locosheds.co.uk site. Plenty of allocation details there to work on

[kudu]

Sorry to be late, but I've only just come across this thread.

First, I have a database of locos as at 1948 including their allocation, the latter taken from the Chris Banks book, so it is not a primary source. (I've attempted to add the LNER locos missing from this publication.)

Secondly, I'm a bit worried about using shed codes as a way of recording allocations (if this is what you have in mind). The point is, of course, that both codes and allocations are transient. A database using codes could therefore be cumbersome and confusing for users. I would therefore recommend using a list where the sheds, however recorded, are unchanged. I suggest the BR system is ill-suited for such a list because so many sheds would be missing, having closed or lost allocations before 1948. I would therefore suggest adapting the LNER codes, adding your own codes for Thornaby, for non-LNER sheds that had LNER locos in BR days, and for sheds closed or lost allocations before 1923. The key point is not to try and "keep up" with BR code changes when recording allocations.

Forgive me if I'm telling you the obvious here.

Kudu

[Geordie13]

Hello Is this project still active? Completed? I apologize if this is old news, I have just joined. Thanks.

[richard]

Sorry no. It was an idea I had but it would have required others to enter th bulk of the data and there was never really the enthusiasm.

[65447]

Whilst not necessarily complete in respect of all LNER locomotives www.brdatabase.info/ is a useful and partly comparable resource, whilst the Yeadon's Registers provide the information but are not readily searchable.