Stock Databases

[Ian J.]

As part of an ongoing project that I started entirely for myself, I've been prototyping a database system for storing my rolling stock. It's now getting to the point where I will be looking at moving it from MS Access to a better platform, in this case a C# based interface writing to either an MS Access database file or a SQL Server database.

 

As I think further about it, I do wonder if anyone else here uses a database for their stock, or if they have ever thought of doing so, what information would they like to see stored? There are obvious things like manufacturer's model number, details on scale, gauge, price, retailer, etc. But I might have missed things out as I have only designed the prototype for my needs, so as I might one day make the program available to others I would like to have sensible suggestions for features and functionality that modellers might want.

[Kenton]

... a C# based interface writing to either an MS Access database file or a SQL Server database.

 

... I might one day make the program available to others I would like to have sensible suggestions for features and functionality that modellers might want.

Not so much a suggestion for data fields as a suggestion for platform.

If there is an intention to make more widely available (and actually even if not) I would suggest a better DBMS platform. MySQL is a freely available OpenSource database that is far superior to the Microsoft branded and restricted products. Interfaces are available for most programming software. It is the DBMS of choice for Apache web based systems. If you make a "product" available only for SQLServer who is going to afford the licence?

 

BTW for small and medium sized DB the MySQL product is far more superior in both speed and more importantly the typical Microsoft bloatware features.

[Ian J.]

As my day job involves working around Microsoft products, I already have access to their Visual Studio development environment, which is why I'm using that. The SQL Server DB is available as a free desktop version (it doesn't have any of the development and maintenance elements) so for installation on Microsoft-based PCs it's a no brainer for me to stick with it for now.

 

However...

 

I have always kept in the back of my mind that for best distributability, a Java based app would be better, onto a platform independent DB - in which case MySQL is really the only option.

 

For now though I am working with both what I've got and what I know. I can't program in Java to save my life, so that would have to be learnt before I'd even consider tackling a fully cross platform application.

[russellwar]

May I ask what you have up until now?

 

I did it a few years back and it was something I wanted to do properly at some point.

 

However all I had was about 10 different fields per item of stock - so I am curious as why you need to expand.

 

i am a bit of a novice with databases though!

 

When i did it I was wondering if an Excel sheet would be enough - but I think I am missing something

[Stubby47]

Ian,

 

You need to consider kit and scratch built stock too - so build date, builder, construction material, motor/gearbox, etc.

For locos: DCC chip, CV settings, Functions

You might also consider reference links to prototype information (eg photo of 3440 in 1965 in a specific livery)

Insurance / current value as well as purchase price

Security mark (what / where)

Photo of each item (or link to photo archive)

 

And there are probably more when I think of them.

 

Stu

[Ian J.]

What I've done to date is for most people's needs way over engineered but has been very much for what I've felt I've wanted which is to go beyond just a simple set of records for say, insurance purposes.

 

Not only am I keeping a basic record of the stock number, manufacturer, price, etc, but I'm also keeping details of prototype classes with some level of their numerous and various variations (though much of the prototype data is incomplete at the moment).

 

Also, beyond the basic model data is stuff like what couplings are in use, wether a model has been detailed or not, what DCC decoder is in it (if any), wether it has been weathered, etc. Beyond that I've also been working on a 'fixed rakes' system, that allows me to build up sets of coaches into sensible sets (sometimes based on prototypes like with the Southern's sets, other times just freelance ones for my convenience). Multiple units are covered by their individual vehicles, rather than by set, but that needs work as I feel most modellers would want to record the entire unit as a single item, rather than by its individual vehicles.

 

I am looking to be able to allow stock in different scales/gauges and from different geographical locations, so that, in theory at least it should be able to handle any item of stock from any country worldwide. This means also having a currency system, together with exchange rates recorded at time of purchase (to cover items bought from overseas).

 

Reporting is another area that needs attention, mainly in that I'm not entirely sure what I want reported, let alone what other people might want.

 

Further work to do relates to being able to create 'layouts' and 'timetables' (both ultimately being done graphically, hence the need to move away from MS Access) for a model railway, so that the PC could work out from the available stock what would be most suitable to run a particular train, in essence for creating a sequence for running, and also have the computer be able to work out how to work round a 'failure' and still keep the 'services' running. But that's somewhat in the future at the moment.

[Kenton]

As my day job involves working around Microsoft products, I already have access to their Visual Studio development environment, which is why I'm using that. The SQL Server DB is available as a free desktop version (it doesn't have any of the development and maintenance elements) so for installation on Microsoft-based PCs it's a no brainer for me to stick with it for now.

 

However...

 

I have always kept in the back of my mind that for best distributability, a Java based app would be better, onto a platform independent DB - in which case MySQL is really the only option.

 

For now though I am working with both what I've got and what I know. I can't program in Java to save my life, so that would have to be learnt before I'd even consider tackling a fully cross platform application.

 

Reasons understood. As long as you are careful in use of some of the more peculiar MS Access commansds it should be reasonably portable anyway.

 

I would avoid Java like the plague - especially if a web based distro - it is about the most distrusted language out there. If you are competent with C .. why change?

 

As a suggestion for the database - if not already thought of

 

- how about a book, photo & web link reference table(s) A nice place to store all those bookmarks and reference links to the internet resource (and others) for prototype photos, model builds (kits) or detailing articles, etc?

 

- also a list of prototype numbering, livery changes, diagram history, any regional operation and abnormalities?

[Ian J.]

Ian,

 

You need to consider kit and scratch built stock too - so build date, builder, construction material, motor/gearbox, etc.

For locos: DCC chip, CV settings, Functions

You might also consider reference links to prototype information (eg photo of 3440 in 1965 in a specific livery)

Insurance / current value as well as purchase price

Security mark (what / where)

Photo of each item (or link to photo archive)

 

And there are probably more when I think of them.

 

Stu

 

Full DCC info isn't there at present, but I was hoping to be able to store a JMRI file (for backup purposes) against each powered vehicle's record for cover CV settings. Whether that could then be built on at a later date to enter values directly to allow programming I'm not sure.

 

In the latest version of MS Access (2007) it's possible to put photos in easily for a record, and it's something I'd like to put into a 'full' version too.

 

Good points re current values and insurance values. I've only put one value in at the moment which is purchase price but those two should really be there as well, perhaps with some kind of 'depreciation/appreciation' calculations that could be accessed if necessary.

[Ian J.]

...If you are competent with C .. why change?

I'm learning C# at the moment, I've never learnt C or C++, and anyway they are a bit too long in the tooth now and, as I understand it, have more than a few limitations that make them more prone to hacks and exploitations without very careful programming. It has to be said that modern programming IDEs and languages are helping to make reliable and robust code easier to write.

 

- how about a book, photo & web link reference table(s) A nice place to store all those bookmarks and reference links to the internet resource and others) for prototype photos, model builds (kits) or detailing articles, etc?

I'm not quite sure whether that would be for an individual model, or maybe it should just be an additional module of data separate from the main model data? Perhaps both. But as below with the prototype info being held, much of an actual vehicle's extended data could be put there. I'll need to think of what a straightforward common format for such extra data could be.

 

- also a list of prototype numbering, livery changes, diagram history, any regional operation and abnormalities?

This is getting covered by the class tables that I have been working on though such extended data as livery changes and regional operation is scant at the moment, but this is the kind of stuff that I need to know.

[eastwestdivide]

Whatever else you do, future-proof the data and structure so that it's not tied to any platform or software.

For example, I've seen databases that made use of text colour to denote an attribute, which of course was lost on any kind of export of the data.

[Adrian]

Not so much a suggestion for data fields as a suggestion for platform.

 

BTW for small and medium sized DB the MySQL product is far more superior in both speed and more importantly the typical Microsoft bloatware features.

 

I'd concur with the recommendation for MySQL. I've just started transferring some legacy systems at work from Access to MySQL and the speed difference was significant. Some of the queries are joining large tables but running the exact same query on the same datatables in MS Access it would take just over 5 1/2 minutes to complete whereas in MySQL they take just under 3 seconds for the first query and then less than half a second for subsequent queries.

 

As my day job involves working around Microsoft products, I already have access to their Visual Studio development environment, which is why I'm using that. The SQL Server DB is available as a free desktop version (it doesn't have any of the development and maintenance elements) so for installation on Microsoft-based PCs it's a no brainer for me to stick with it for now.

 

However...

 

I have always kept in the back of my mind that for best distributability, a Java based app would be better, onto a platform independent DB - in which case MySQL is really the only option.

 

For now though I am working with both what I've got and what I know. I can't program in Java to save my life, so that would have to be learnt before I'd even consider tackling a fully cross platform application.

 

MySQL have a developer environment called workbench which is free for download and might help. I've never really got on with Java so to throw another option in the ring I'd recommend Python which is also a cross platform language and has "batteries included" i.e. the necessary libraries are already included - including database connections. With MySQL you also have the option of making it web based as well.

 

and final suggestion from me, if you make it scale agnostic then it might be useful to some of the 12" to the ft. layouts around the country. Make it web "enable" and some of the perserved lines around the country could make good use of it.

 

Have fun!

[Ian J.]

I'd concur with the recommendation for MySQL. I've just started transferring some legacy systems at work from Access to MySQL and the speed difference was significant. Some of the queries are joining large tables but running the exact same query on the same datatables in MS Access it would take just over 5 1/2 minutes to complete whereas in MySQL they take just under 3 seconds for the first query and then less than half a second for subsequent queries.

 

 

 

MySQL have a developer environment called workbench which is free for download and might help. I've never really got on with Java so to throw another option in the ring I'd recommend Python which is also a cross platform language and has "batteries included" i.e. the necessary libraries are already included - including database connections. With MySQL you also have the option of making it web based as well.

 

and final suggestion from me, if you make it scale agnostic then it might be useful to some of the 12" to the ft. layouts around the country. Make it web "enable" and some of the perserved lines around the country could make good use of it.

 

Have fun!

Making it web-based was not really what I had in mind, intriguing though that is (there are added complications). I think there are already web-based systems out there that could cover most people's model requirements (TrackMyTrains.com is one, though with a U.S. modeller in mind) and would also provide online backup to their data, but of course at a price.

 

In theory I could add a 12" to 1' scale, with appropriate gauges, but it would be a little odd...

 

What I have thought of having is a web-access system for uploading and downloading the data for the prototype classes, and also possibly having an online downloadable set of known manufacturers models, a bit like an online version of Ramsey's, but of course I'd then be stepping on the toes of the Ramsey's catalogue to some degree, which I'm sure Pat Hammond would not be happy about.

 

Something I forgot to mention: the application would be a free application, though not open-source. I've nothing against the open-source community, but as some of the calculation code will be fairly complex and therefore of some value, I'm not at the moment inclined to give it away for free. I might feel different at a later date, especially if my novel writing gets anywhere.

[Ian J.]

Re choice of database

 

If I get my design right, one thing that in theory should be possible is for any server or local database system to be used, be it MS Access as a dumb file, or MySQL, SQL Server, Oracle, SAPDB, etc.

 

In the case of the server programs, the design of each's internal data handling stored procedures would be specific to each, but the way they're called from the app would basically be the same.

 

For MS Access it's different as all such procedures would be written into the application as an Access file can't process data on its own, only with an application (which is one of the reasons it's so bl**dy slow over networks, though purely locally it's not too bad).

[St. Simon]

Hi,

 

I keep a stock database in Microsoft excel, I do keep track of Wagons because I have so many!

 

Simon

[2512silverfox]

Just to give you experts a laugh, I am still using a Rapidfile database (Ashton Tate DOS) which I set up in the mid to early 90's and it still does just what I want even on an up to date machine. I have about 25 fields, since I keep both RTR and kit/scratch built stock.

 

If its not broke .....

[tebee]

I have a database -currently in access - of my stock. I stared it about 12 years ago as I was having difficulty keeping track of the 250 or so items I had - I now have around 1500 !.

 

Within the one database I have separate tables for locos , passenger and freight as the needs are different for each.

 

I wrote it with the idea of extending it to allow for freight train operation - I'm a US prototype modeller and I mean the in the US sense of operations i.e. routing cars from point A to point B. But, I've never got as far as writing this part - well it's only been 12 years......

 

At the moment I'm trying to decide how to go forward with it, I had a hard drive crash last year which cased me to lose the latest version of my data and I'm been thinking of changing the platform for some time.

 

I'm looking to move to an open source solution but have not decided on what yet. Base, the Open Office equivalent of Access dose not seem to be up to the task, Sunedio http://www.suneido.com/ looks promising but is another language to learn. The other alternative would be to do a web based solution using PHP and MySQL. This would probably mean running a local server - LAMP or windows based WAMP/XAMP - so would not be an easy solution for others to implement but does offer lots of flexibility and the possibility of running it on multiple machines.

 

Tom

[Classsix T]

Further work to do relates to being able to create 'layouts' and 'timetables' (both ultimately being done graphically, hence the need to move away from MS Access) for a model railway, so that the PC could work out from the available stock what would be most suitable to run a particular train, in essence for creating a sequence for running, and also have the computer be able to work out how to work round a 'failure' and still keep the 'services' running. But that's somewhat in the future at the moment.

 

Now you're talking. A modeller's TOPS machine in effect. I live in the stone age so use bits of card with loco number/train brakes/heat capability/depot, eg 37038 xb MR.

I'll be starting a new thread looking further down this avenue soon, but I'm afraid the pub beckons right now...

 

C6T.

[Kenton]

Re choice of database

 

If I get my design right, one thing that in theory should be possible is for any server or local database system to be used, be it MS Access as a dumb file, or MySQL, SQL Server, Oracle, SAPDB, etc.

Ah ! Famous last words of many a program developer ... I can hear it now ...It works perfectly with xyz database v1.0.1.234 (Beta)

Many have found to their cost that database integration is progressively more difficult the more "powerful" the DBMS. and as for any attempt at future proofing ...

Don't rule out simple text file systems. PC file access is now so fast and processing power so great that relatively small databases are just as easy to manage as a simple flat file based system.

[beast66606]

Stick to simple SQL, don't use stored procedures or triggers, make your client a fat one and you will be fine for this purpose - I use Delphi (Pascal based) for my front ends btw.

[Adrian]

I'm looking to move to an open source solution but have not decided on what yet. Base, the Open Office equivalent of Access dose not seem to be up to the task, Sunedio http://www.suneido.com/ looks promising but is another language to learn. The other alternative would be to do a web based solution using PHP and MySQL. This would probably mean running a local server - LAMP or windows based WAMP/XAMP - so would not be an easy solution for others to implement but does offer lots of flexibility and the possibility of running it on multiple machines.

SQLite is a popular choice for small databases and doesn't need a local server running. A full [LWX]AMP setup might be overkill for a simple stock control system. Many of the new scripting languages, at which point I'll mention Python again because I like it, have a simple web server included and most of them will talk to SQLite. It depends on how much you want to dive into the programming side.

[Penlan]

Keeping to the Topic Title - Stock Databases, then I use MSExcel, like St. Stephen.

 

This actually started in late 70's with an early database programe, then DB2, then some Shareware based spreadsheet and then about the same time Windows came in and the office Accontants decided to allow us to use PC's rather than the mainframes, we went for an early version of Excel, can't remember what that was called though, basically I'm old DOS man... at heart. We originally had WordPerfect in the office on PC's, then Gem which became Word, I've got loads of tee shirts....... At home we also now use OpenOffice.

 

So due to work, it was easier to use Excel for my own Stock database as well.

 

Currently there are 682 rolling stock items listed with 34 fields each (not all the fields are used on all the stock though).

 

Because of my familiarity with Excel, I also expanded a basic database used for the world wide records for the Austin Seven Clubs Association Register, there are currently 10,684 lines with 26 fields each (not all cars use all the fields though) - I had fun sorting out the data on that, removal of duplications etc from about 60 different Clubs submissions in different formats.

 

So relative to the topic title, Excel.

 

Penlan

[jamie92208]

I use a simple excell spreadsheet that provides all the functionality that I need. Fields that I have include

1) serial number (like a library accession number the next thing I buy gets the next available number.

2) Type of vehicle (eg open, van, cattle etc)

3) Sub type eg 5 pl

4) doors (side, end etc)

5) Status (0, it's on my wish list, 1 kit in box or project just started, 2 A bit further along, 3 a running chassis, 4 comlete but unpainted, 5 complete) That column is then averaged and my ambition is to keep the averabge above 4

6) value for insurance purposes

7) Owner (eg company or PO)

8) Running number

9) Manufacturer and serial number eg Slater's 7027 (useful for passing to sisters in law when christmas presents are needed.

10) Weighted or not

11) type of coupler

12) Train (a letter code as to which train it is intended for on the layout

13) remarks.

14) Storage tray

15) length

16) No of axles (important for coaching stock.

17) Load information.

(NB they are not quite in the above order but that's the order my old brain has remembered them in.

 

That sheet is for wagons, other sheets are for locos, coaches, road vehicles, electrical items. This then gioves me a check list when the layout goes out on the road. The values are collated on the first sheet to help with the insurance.

 

The status code is great as after an evenings modelling it's nice to move the average up a bit.

 

This was originally built in Quattro Pro but has now migrated to Excell and that seems to do all I need. I do like the idea of being able to add a link to a photo as that would help in the event of an insurance claim or when my dearly beloved is trying to mrealise it's value after I shuffle off this mortal coil.

 

Hope this is if some use.

 

Jamie

 

Fields 12 and 14 allow me to sort out the consist for each train that runs on the layout and also to find them when they

 

I

[Ian J.]

...

Hope this is if some use.

 

Jamie

...

It is almost surprising how many of the elements you've listed are already possible (or would be possible with a bit of adaptation) in what I've programmed in the prototype. Great minds think alike maybe...

 

As a classic database design, I've spent a fair bit of time working out the tables that contain prototype class data, and much of those are where the key information is held for how the stock could be used on a layout. It's a very different form to Excel sheets, but the granularity of such data is where the power for sequencing and timetabling comes in.

[jamie92208]

It is almost surprising how many of the elements you've listed are already possible (or would be possible with a bit of adaptation) in what I've programmed in the prototype. Great minds think alike maybe...

 

As a classic database design, I've spent a fair bit of time working out the tables that contain prototype class data, and much of those are where the key information is held for how the stock could be used on a layout. It's a very different form to Excel sheets, but the granularity of such data is where the power for sequencing and timetabling comes in.

 

I never set out for it to be used in that way. All I wanted was to keep track of what stock I had, when I bought it and the value. The status code is just a bit of fun at the end of a night's modelling. The other things like train and stock tray seemed like agood idea and then I thought of adding the extra sheets for such things as road vehicles, electrics etc. The Electrics allow me to keep a track of things for PA Testing that some exhibitions require as well as being a useful checklist when I take the layout out. The main thing is to have the data there in the first place. The accession numbers are JMG/Letter/number. The letter is C for coaches, L for Locos etc and wagons are just JMG/number. If you are interested I could send you the spreadsheet off site if you want to play with the data.

Jamie

[Ian J.]

...If you are interested I could send you the spreadsheet off site if you want to play with the data.

Jamie

Not for the moment, as I can see from your descriptions more or less how your data is working for you, but thanks anyway and will keep the offer in mind.