HowardGWR Posted February 11, 2017 Share Posted February 11, 2017 Howard IIRC, database fields can have either a 1:1 or 1: many relationship? If so, your photo is the unique identifier but you have to have a system of saying top left, middle etc. Clearly locos, liveries, locations etc all have 1 to many but it'd be a gargantuan task to build those fields. I'd guesstimate that there's been well over 100k of locos alone operate in the uk and I wouldn't like to try and guess how many carriages or wagons... It'd be interested to know how BR handled its asset database as that is pretty similar to what we're talking about. In my spreadsheet, I think I'd recognised that difficulty so rather than identify specifically, I'd left it slightly vague i.e. You input either the plate number or page plus description. Given I was writing for my own use, I'd reasoned I was most likely to search for a loco or class and then use he photo/page reference to be able to fin it quickly within the book. Not a true database in the proper sense but more a guide to find something quickly David David Sorry for a late reply but I was 'away' on other matters. You seem to have dealt with this very pragmatically. In between your posting and this one, I see you are dealing with the thorny question of dates (well, format issues anyway). An entity like a photo could have numerous dates associated with it (date taken, date published and so on). I once had a colleague who opined that we should no longer speak of databases, but date-bases!! By the way, to answer your first question, fields should not be related to one another, it's the entities to which they belong that may be related to other entities, and those relationships can be one to many, or many to many. A photo can only be taken by one photographer, but a photographer can take many photos; So the name of the photographer could be an attribute of the photo in your photo database. You might wish to establish a database of photographers, if you wanted to store information about them, but in a Soole photo record, you would not wish to record his birthdate because you would have to record that in every blessed photo by Bill Soole. I get very boring on these matters. I think your initiative is a very welcome one and thanks to Miss_Prism for facilitating its publication. Link to post Share on other sites More sharing options...
MarkAustin Posted February 12, 2017 Share Posted February 12, 2017 Dates are a right pain in the a*** in Excel. It stores dates as the number of days from (IIRC) 1st January 1900 (or thereabouts). That's why date fields if viewed as general appear as a number. It recognises some date formats but not others, and renders any date before its start date as text. This means anything before 1900 is stored as text and cannot be sorted. I strongly recommend using the computing trick of storing dates in the yyyy/mm/dd format as a text field. This not only sorts correctly as text but allows storing dates when only the year or year and month is known, and having them correctly sorted. (It's also a good trick for naming dated files, e.g minutes, as they sort in the correct order in Windows explorer). Mark A Link to post Share on other sites More sharing options...
martinT Posted February 12, 2017 Share Posted February 12, 2017 Can I raise the possibility of duplication of effort. I've started indexing 'The Early Years of Western Region Steam: An Album of P M Alexander's Photographs' (compiled by P Whitehouse, Wild Swan 1983). It's going to take some time - & I'd hate either to waste someone else's time or have my time wasted. Please can everyone announce what books or other publications they intend to do. Martin I've finished this book - subject to checking & will submit the file to MissP tomorrow. I have stuck with the date format that I described in post 84 ie: 1951-Aug-25 or 1951-Aug of 1951- or 1951 est. as endorsed by Mark Austin because there doesn't seem to be any other way of formating variable date accuracies, (ie full date, month, year, or estimated year) that will allow Excel to sort properly. I'm not happy with the suggested kludge of making uncertain dates to be 1st Jan. I'll now move on to 'The Great Western Scene' (Maurice Earley, OPC,1970) Martin Link to post Share on other sites More sharing options...
RMweb Gold 57xx Posted February 12, 2017 RMweb Gold Share Posted February 12, 2017 You would also need to suggest standards on how the data is to be formatted ('56xx' or 'Class 56xx', 'Truro' or 'Truro Platform 2', etc.) - this will reduce your time in adding entries to the spreadsheet. Just having a quick scan through and noticed that there is currently a mix e.g. 28xx and 2800, also 4575 and "4575 Class". Personally I think it's better to drop the xx format (despite my username! ) as it will look better with the likes of 2021, 4575 classes etc. As the column is called Class, perhaps no need to add "Class" after the number or name of class? Link to post Share on other sites More sharing options...
RMweb Gold The Fatadder Posted February 12, 2017 Author RMweb Gold Share Posted February 12, 2017 Just having a quick scan through and noticed that there is currently a mix e.g. 28xx and 2800, also 4575 and "4575 Class". Personally I think it's better to drop the xx format (despite my username! ) as it will look better with the likes of 2021, 4575 classes etc. As the column is called Class, perhaps no need to add "Class" after the number or name of class?I think you are right, though perhaps the solution is to do a periodic sweep through the list replacing as appropriate. (At least to sort out the xx references Link to post Share on other sites More sharing options...
Miss Prism Posted February 12, 2017 Share Posted February 12, 2017 As the column is called Class, perhaps no need to add "Class" after the number or name of class? Agreed. I'm personally not bothered about '2800' v '28xx' notation, and I think we can cope with either. I expect there will be some fun and games when it comes to the more obscure saddle/pannier tanks, and we need to have some flexibility to allow for a spread of contributors' knowledge and finesse, e.g. 'Buffalo' is as good as '1076', and 'Castle' is as good as '4073'. Link to post Share on other sites More sharing options...
Miss Prism Posted February 12, 2017 Share Posted February 12, 2017 Thankyou Martin and Mark. I am now convinced the date should be input as text, i.e. what Excel calls 'Text' in the 'Number' panel. I feel we can leave it up to contributors whether they prefer to use 'yyyy/mm/dd' or 'yyyy/month/dd'. Both are sufficiently sortable. I will amend the guidance on the server files accordingly. Btw 1: Are there any objections to putting the 'Notes' column as a new final column, i.e. after 'Tender type' and 'Colour pic?' columns? Btw 2: Rich - got you Great Western Steam in the West Country input - thanks. Link to post Share on other sites More sharing options...
RMweb Gold 57xx Posted February 12, 2017 RMweb Gold Share Posted February 12, 2017 Agreed. I'm personally not bothered about '2800' v '28xx' notation, and I think we can cope with either. I expect there will be some fun and games when it comes to the more obscure saddle/pannier tanks, and we need to have some flexibility to allow for a spread of contributors' knowledge and finesse, e.g. 'Buffalo' is as good as '1076', and 'Castle' is as good as '4073'. It just makes filtering easier if you don't have to remember to select both 28xx and 2800. Not an insurmountable problem, just more user friendly. It will also make any sorts better as currently 2800 will not sort next to 28xx. Thankyou Martin and Mark. I am now convinced the date should be input as text, i.e. what Excel calls 'General' in the 'Number' panel. I feel we can leave it up to contributors whether they prefer to use 'yyyy/mm/dd' or 'yyyy/month/dd'. Both are sufficiently sortable. I will amend the guidance on the server files accordingly. I'd agree that using the ISO international Date Format is better (it technically uses a hyphen - instead of / but the important bit is the year/month/day order). You will however need to format the column as Text as General is not text, it will auto-format based on what is typed in. E.g. if you type in 1934 it will take that as a number but 1934/07 would be treated as text and appear differently when sorted (numbered rows first, then text rows appear further down e.g. 0-9, then A-Z). You cant spot the differences already there - Sort the Class column A-Z. All the numbers (2800 etc) appear right justified, all the text values are left justified and come after the numbers in the sort. Link to post Share on other sites More sharing options...
RMweb Gold 57xx Posted February 12, 2017 RMweb Gold Share Posted February 12, 2017 Example of how sorting works: This is sorted A-Z on the Class column, note left and right justification on the entries and how "1361 Class" comes after 8750. I also threw in a quick formula column to pull out the year so if someone wants to list all pics by year, they don't have to (potentially) put 365 ticks in the filter. Link to post Share on other sites More sharing options...
Miss Prism Posted February 12, 2017 Share Posted February 12, 2017 I have deleted instances of 'Class' in the entries under the 'Class' column. The use of 'class' is however probably desirable in the 'Other locos' column, e.g. if the other loco is a '3150 class', this is better than '3150', because the latter could be read as the actual loco numbered 3150. On the 'xx' notation, this is also useful in the 'Other locos' column, e.g. '5243 42xx' is better than '5243 4200' because the latter could be read as two other locos, one being the loco numbered 4200. Link to post Share on other sites More sharing options...
jonny777 Posted February 12, 2017 Share Posted February 12, 2017 I hate to be pedantic, but poor spelling of place names annoys me. There is no 'a' in Clevedon, and it has two 'e's. Link to post Share on other sites More sharing options...
martinT Posted February 12, 2017 Share Posted February 12, 2017 Just having a quick scan through and noticed that there is currently a mix e.g. 28xx and 2800, also 4575 and "4575 Class". Personally I think it's better to drop the xx format (despite my username! ) as it will look better with the likes of 2021, 4575 classes etc. As the column is called Class, perhaps no need to add "Class" after the number or name of class? Agreed - I've just removed the xx format in my first list. Having said that I've used the obvious names where they are available: King, Castle, Star, Saint, County, Hall, Mod Hall, Grange, Manor, Bulldog, Dukedog, Dean Goods - the reason being that at some stage names such as Stella, Barnum, River, etc are, I think, going to be more recognisable that some number - so we are going to be stuck with a mixture of names and numbers. There's going to be a problem when someone (me, I fear) comes to the first 4-4-0 County photo! I've described ex-ROD 2-8-0s as class ROD, company GWR. I've now used the Company field as that of the pre-grouping company - so that all ex Cambrian locos (say) can be listed. However (inconsistency alert) I've used the post-grouping class description! Eg, I have a photo of 4-4-2T 67442 at Oswestry (with a GW auto-coach) - I've described this as: 'C14, GCR' however as I understand these foreign matters 'C14' is the LNER/BR classification, whereas the GCR's was '9L' Martin Link to post Share on other sites More sharing options...
Miss Prism Posted February 12, 2017 Share Posted February 12, 2017 I am currently calling a temporary moratorium on submitting an updated version of the master server file, because Ric (57xx) has kindly agreed to redo the current sheet dates into 'text' format using some clever formulae. (Until that formatting is resolved, it would not be helpful to send in updates of the master file.) Therefore, please use the blank sheet for new input: http://www.gwr.org.uk/notes/loco-picture-database-blank.xls In any case, I will need to impose a more rigorous approach to change control. I feel the blank sheet method of input will necessarily be the only permissible form in the future for new contributions, otherwise there is a danger of contributors working on different issues of the master file, which will give me horrendous amalgamation problems. I am considering what method might be suitable for corrections to the current master file entries. Thanks for your patience. Link to post Share on other sites More sharing options...
brianusa Posted February 12, 2017 Share Posted February 12, 2017 A work of love; just as well as over the last five pages, the same names are prominent but where are the other potential contributors. Is there enough interest for such an enterprise? Brian. Link to post Share on other sites More sharing options...
Miss Prism Posted February 12, 2017 Share Posted February 12, 2017 ...but where are the other potential contributors Standby your books - notice of widening of goalposts This evening I had an unexpected but challenging surprise in my inbox. After spotting the gwr.org page, an enthusiastic GWS member has offered a database (a .WDB file) of 32000 GWR photographs. The fields in the database are somewhat different to that being proposed here. I do not yet have details of the book coverage, but the coverage is obviously extensive. Subject to your views and those of the GWS member, I am now pursuing a simultaneous double strategy: - The conversion of the .WDB file to an .xls or .xlsx file, and putting that file in the public domain, in order to give visibility of what books have been covered. (I understand a .WDB to Excel conversion is 'straightforward', but I may be seeking to pick better brains than mine to assist in that conversion.) - The adaptation of the column headings in the current gwr.org .xls file to reflect the additional fields in the .WDB database; this will give the soonest possible notice to everyone here of the extra fields envisaged, and facilitate the eventual amalgamation of the WDB data into the master server file. I am making this announcement as soon as possible, which hopefully will or could save a lot of work. I will keep everyone informed of developments. Link to post Share on other sites More sharing options...
RMweb Gold Stubby47 Posted February 28, 2017 RMweb Gold Share Posted February 28, 2017 Can I ask for some clarification ? The Middleton Press books concentrate on a line, so include images from a wide range of dates. If this is a GWR database, should I include GWR locos in BR days ? Link to post Share on other sites More sharing options...
chrisf Posted February 28, 2017 Share Posted February 28, 2017 The Middleton Press books concentrate on a line, so include images from a wide range of dates. Beware of the errors! Chris Link to post Share on other sites More sharing options...
RMweb Gold Stubby47 Posted February 28, 2017 RMweb Gold Share Posted February 28, 2017 Agreed Chris, but a pic dated 1956 with a loco in BR livery is, even to my untrained eye, not a 'GWR' photo. But, it might be considered relevant as the location is GWR & the loco is ex-GWR. Photos of Class 66 locos would probably not be needed, except of course for the background infrastructure... Link to post Share on other sites More sharing options...
HowardGWR Posted February 28, 2017 Share Posted February 28, 2017 As long as the photo has the date appended, I see little problem. Apologies for what might be seen as the obvious answer. Link to post Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.