Looking for coders' input on vehicle record storage

[kishy]

I know a reasonable amount about SQL database structure and enough about PHP to put together a crude solution to my problem, which could of course be built upon later, but I'm appealing to any coders who frequent GH for your thoughts on how to approach this.

The "problem" I am trying to solve: the lack of simple yet powerful methods to 100% electronically store: vehicle service records, upcoming maintenance schedule, fuel economy and odometer logs, photos associated with service records, and receipts associated with service records.

There's a catch: ideally the solution will be accessible from anywhere, e.g. web-based, stored on hosting that is within my control (not an online service to store the info, but rather my OWN service that is hosted wherever I see fit and can be picked up and moved if need be).

The system should have no real limitation to number of vehicles supported (would be a lifetime system, to store old records indefinitely). Photos and receipts should remain in the format that they were originally, and be stored in a directory structure alongside the database to allow access from outside of the system (e.g. PHP Gallery accessing same photos).

I started writing out tables and I do think I can come up with something, but problems I run into involve the storage of photos and receipts. If I am going to be storing what basically amounts to a hyperlink to a given image or receipt in a field, then I am limited to the number of fields for the number of photos or receipts that can be associated with a given maintenance item. For example, when I replaced my lower intake manifold gaskets, there were several photos taken throughout the process and receipts from at least two retailers. A simple oil change will have two receipts (I buy my filters in bulk from RockAuto, but the oil from a local store), and usually no photos with the possible exception of the odometer.

Aside from storing all associated files in an archive and then linking to said archive (one file, easy, solved), is there an easy way around the "number of files" dilemma? Since direct outside access to the files is preferred, this is not optimal, but disk space grows cheaper by the day so it's not the end of the world.

There are software programs that accomplish pretty much all that I want to do (notably, CARCare, http://www.carcaresoftware.com/) except I'd have to use Remote Desktop to access the software away from home. This is itself not the end of the world either, but it means that "forever" I will need to have a Windows computer, and while I do not foresee that changing now, it could create an issue down the road if Windows 11 really irks me enough to change to *nix...

So, brainstorm? Any input is appreciated.

[rowdy]

I'd use Python and web2py to patch something together.  It already includes file upload capability that I am using for something similar.

As for storing photos - try to avoid, whatever solution you pick - storing all the photos in one directory.  The system I wrote currently has 96711 attached files of various types.  If they were all in one directory that would create overheads for the operating system and slow everything down.  Group them by date, perhaps, with one directory for each calendar month.  Store the (relative) pathname to the picture in the database.

[daerid]

For the love of god and all that is holy, please, please, please do NOT use PHP. We need less of that in the world, not more.

[kishy]

Well, between it being what I at least somewhat know and this only being for me (not a product to be released)...it seems like a good choice. As for Python, I wouldn't even know where to begin.

All photos would not be in one directory; they would be divided up by record they are associated with. I still don't know a way to support an indefinite number of photos with a particular record, though.

[kolonelkadat]

it really depends on if this is a personal setup for you and your cars or if it's intended as a public service with millions of users.

if its just for you, id be lazy as possible. pick whatever language youre most comfortable with. as long as it isnt python. At the end of the day, it's just for you. The performance differences wont matter for you and any shiny features certainly wont do you any good if you dont know how to use them.

to store images just have a text field and pack it with a || delimitered string of filenames. you could use a single | if you wanted of course,_{(or anything really)} i just like the look of the double pipe. either way, retrieval of images is as simple as string.split() or explode() or whatever your chosen language uses to split delimited strings into an array. adding a new image is just a matter of concatenation.

you could even have reciepts in the same record just delimit the reciepts and images with a more different delimiter. maybe ;;
if this is for a multi user service, id suggest getting a bit more experience before attempting something like this.

[Hak Foo]

I started writing out tables and I do think I can come up with something, but problems I run into involve the storage of photos and receipts. If I am going to be storing what basically amounts to a hyperlink to a given image or receipt in a field, then I am limited to the number of fields for the number of photos or receipts that can be associated with a given maintenance item. For example, when I replaced my lower intake manifold gaskets, there were several photos taken throughout the process and receipts from at least two retailers. A simple oil change will have two receipts (I buy my filters in bulk from RockAuto, but the oil from a local store), and usually no photos with the possible exception of the odometer.

Why not one of two options:

A table of "Maintenance Event ID -> Photo Filename" with no restrictions on how many entries you put in per Maintenance Event ID.  Optionally an extra column or two for an ID number to manipulate the entries one at a time, or a "file type" field to indicate if it's a reciept, a photo, a video, whatever.

Or in a cheesier manner, a naming convention for uploads that allows them to be picked up systematically.  For example
photos/Maintenance Event ID.Serial Number.jpg

Serial Number could be a timestamp, a counter, or a uniqid-- anything that is unlikely to result in collisions with the small number of files associated with any one Maintenance Event ID.

Then just do - this being PHP mindset

$files=glob("/path/to/images/".$maintenanceeventid.".*.jpg");

and the $files array will contain the paths of all the associated files.

[Oobly]

If you want to build a fairly large scale web-based solution or if you want to practise building scalable web sites, I suggest either LAMP (Linux, Apache, MySQL, PHP) or Ruby-on-Rails. Each has its pros and cons. PHP is rather klunky and will take longer to code for, but it's powerful (large feature set). RoR will take some time to learn if you are not familiar with it already, but it's quicker to code. It abstracts a lot, even database access (using ActiveRecord or DataMapper).

If it's just for personal use, use whatever language you are comfortable with to build it as an application and carry it with you on an external SSD or thumb drive. You can get 128GB ones now for reasonable prices.

In SQL you can create a separate table for all single-to-many relationships (maintenance item to photos, maintenance item to receipts, etc) and use the maintenance item ID to group them and link them to the relevant maintenance item when you retrieve them from the DB as Hak Foo said. Something like:

CREATE TABLE Photos
(
PhotoID int NOT NULL AUTO INCREMENT,
MaintenanceItemID int NOT NULL,
PhotoPath varchar(255),
PRIMARY KEY (PhotoID),
FOREIGN KEY (MaintenanceItemID) REFERENCES MaintenanceTable(MaintenanceItemID)
);

Obviously this will require the MaintenanceTable to exist first with MaintenanceItemID as its primary key.

Delimited fields are feasible, but can be a hassle to maintain and debug. If the images are small enough you can store them as VarBinary data in SQL so you don't need to maintain a separate file store. Not great for performance, though.

[kishy]

Some good ideas flowing in here...

Firstly, this will (if I go ahead with building it) be web-based on a LAMP server, yes. It will not be public-facing, however, or I'll have to figure out security and have to prevent attack vectors via the data entry forms...

It will be single user, but designed to allow insertion of as many vehicles as I may own in my life

I never even thought of having a table to associate (image name) with (maintenance item id). That's great! A photo will only ever apply to one record, but a record might have many associated photos.

Receipts can probably be broken down similarly, so there would be a vehicle, one or more maintenance items associated with the vehicle id, photos associated with one or more maintenance items, one or more parts associated with each maintenance item, and receipts associated with the parts...if I wanted to go completely nuts about it could break down the vendor for the receipts and store warranty info too (using front end code to display, based on warranty length and purchase date, if a given part is still in warranty).

The multiple file association thing was my big hangup on this; I think I can make this thing happen now that I know some workable solutions. Thanks folks!

If anyone has further suggestions of course I'd love to hear them.

[swill]

it really depends on if this is a personal setup for you and your cars or if it's intended as a public service with millions of users.

if its just for you, id be lazy as possible. pick whatever language youre most comfortable with. as long as it isnt python. At the end of the day, it's just for you. The performance differences wont matter for you and any shiny features certainly wont do you any good if you dont know how to use them.

to store images just have a text field and pack it with a || delimitered string of filenames. you could use a single | if you wanted of course,_{(or anything really)} i just like the look of the double pipe. either way, retrieval of images is as simple as string.split() or explode() or whatever your chosen language uses to split delimited strings into an array. adding a new image is just a matter of concatenation.

you could even have reciepts in the same record just delimit the reciepts and images with a more different delimiter. maybe ;;
if this is for a multi user service, id suggest getting a bit more experience before attempting something like this.

Edit: I was a bit harsh with this comment, so I am revising.

I do not agree with most of the suggestions in this comment though.

His advice to use whatever language you are most comfortable with is valid and useful advice.

[swill]

Unless you REALLY think out the structure of your DB, an SQL solution may get really tricky to extent and maintain as you add features.  I am not saying not to use one, just want to open your thinking to other ideas.

Since every vehicles details/history are going to be different, it may make sense to use a no-SQL/object style database.  You could create a single object record per vehicle and store the different types of detail in sub objects.  As you add features, this type of structure will eliminate the need for DB migrations. Your code simply checks for the existence of the different sub objects your app supports.

MongoDB is a solid option in this space. You can even try it out for free on mongolab.com or mongohq.com to see if it fits with your vision.

One thing to keep in mind is search ability. It is possible, but not trivial to build searches based on sub objects nested deep in the object structure. You probably will have a simple top level query to pull up a vehicle though, so this is probably not an issue. Consider using a phone number or email or owner or the like in the top level object to easily grab all the vehicles owned by a specific person.

Food for thought anyway...

[riotonthebay]

it really depends on if this is a personal setup for you and your cars or if it's intended as a public service with millions of users.

if its just for you, id be lazy as possible. pick whatever language youre most comfortable with. as long as it isnt python. At the end of the day, it's just for you. The performance differences wont matter for you and any shiny features certainly wont do you any good if you dont know how to use them.

to store images just have a text field and pack it with a || delimitered string of filenames. you could use a single | if you wanted of course,_{(or anything really)} i just like the look of the double pipe. either way, retrieval of images is as simple as string.split() or explode() or whatever your chosen language uses to split delimited strings into an array. adding a new image is just a matter of concatenation.

you could even have reciepts in the same record just delimit the reciepts and images with a more different delimiter. maybe ;;
if this is for a multi user service, id suggest getting a bit more experience before attempting something like this.

I am amazed by how often you give HORRIBLE advice. It's shocking really.

I'd like to back swill up on this one. That is horrible advice, indeed.

[riotonthebay]

Swill's advice is good.

...stored on hosting that is within my control (not an online service to store the info, but rather my OWN service that is hosted wherever I see fit and can be picked up and moved if need be).

You should not be wary of storing your data in the cloud. Let other people manage the hardware. If you want a system that is robust and fault-tolerant, the solution isn't to have your data sitting next to you, but to replicate and backup data.

[kishy]

Unless you REALLY think out the structure of your DB, an SQL solution may get really tricky to extent and maintain as you add features.  I am not saying not to use one, just want to open your thinking to other ideas.

Since every vehicles details/history are going to be different, it may make sense to use a no-SQL/object style database.  You could create a single object record per vehicle and store the different types of detail in sub objects.  As you add features, this type of structure will eliminate the need for DB migrations. Your code simply checks for the existence of the different sub objects your app supports.

MongoDB is a solid option in this space. You can even try it out for free on mongolab.com or mongohq.com to see if it fits with your vision.

One thing to keep in mind is search ability. It is possible, but not trivial to build searches based on sub objects nested deep in the object structure. You probably will have a simple top level query to pull up a vehicle though, so this is probably not an issue. Consider using a phone number or email or owner or the like in the top level object to easily grab all the vehicles owned by a specific person.

Food for thought anyway...

Interesting; I've never really given much consideration to other DB types. SQL is what I have (rudimentary) experience with, between a couple college courses and working at a software company that uses SQL DBs very heavily.

Looking into MongoDB and the NoSQL concept overall, I can appreciate the flexibility that seems to be inherent with them. That being said, I don't necessarily feel that it would be of any benefit for my application...of course, the importance of designing the schema correctly from the beginning must not be understated with SQL.

Swill's advice is good.

...stored on hosting that is within my control (not an online service to store the info, but rather my OWN service that is hosted wherever I see fit and can be picked up and moved if need be).

You should not be wary of storing your data in the cloud. Let other people manage the hardware. If you want a system that is robust and fault-tolerant, the solution isn't to have your data sitting next to you, but to replicate and backup data.

I'm alright with having it hosted somewhere else, but what I mean is the infrastructure has to be accessible to me - if I want to pull down a copy of the DB in a format that can be slapped right back onto any other DB server of the same type, then I absolutely must be able to do that. In my current idea, yes, this would be hosted from my house, but being PHP and MySQL-based, it could easily be picked up and dropped on just about any web host with pretty much no difficulty.

What I was referring to is the existence of tools to log vehicle maintenance, but they don't give you any back end access - just front end. While they are established and functional, they don't give me the kind of control I want to have.

[riotonthebay]

Given that, any cloud data store worth anything should fit the bill. You would have full access to the data, console access to the database, and the ability to back it up or migrate it as you see fit.

[swill]

When it comes down to it, you should use what you are most comfortable with.  I only wanted to bring up the no-SQL style DB to give you another approach which had not been suggested and to potentially give you more ideas for how to solve the problem.

[rowdy]

Some good ideas flowing in here...

Firstly, this will (if I go ahead with building it) be web-based on a LAMP server, yes. It will not be public-facing, however, or I'll have to figure out security and have to prevent attack vectors via the data entry forms...

It will be single user, but designed to allow insertion of as many vehicles as I may own in my life

I never even thought of having a table to associate (image name) with (maintenance item id). That's great! A photo will only ever apply to one record, but a record might have many associated photos.

Receipts can probably be broken down similarly, so there would be a vehicle, one or more maintenance items associated with the vehicle id, photos associated with one or more maintenance items, one or more parts associated with each maintenance item, and receipts associated with the parts...if I wanted to go completely nuts about it could break down the vendor for the receipts and store warranty info too (using front end code to display, based on warranty length and purchase date, if a given part is still in warranty).

The multiple file association thing was my big hangup on this; I think I can make this thing happen now that I know some workable solutions. Thanks folks!

If anyone has further suggestions of course I'd love to hear them.

If you have one table for vehicles, one for photos, and one for cross-reference, you could associate the same photo with multiple vehicles.

E.g. a tow bar, sound system or other removable accessory.

[Oobly]

Some good ideas flowing in here...

Firstly, this will (if I go ahead with building it) be web-based on a LAMP server, yes. It will not be public-facing, however, or I'll have to figure out security and have to prevent attack vectors via the data entry forms...

It will be single user, but designed to allow insertion of as many vehicles as I may own in my life

I never even thought of having a table to associate (image name) with (maintenance item id). That's great! A photo will only ever apply to one record, but a record might have many associated photos.

Receipts can probably be broken down similarly, so there would be a vehicle, one or more maintenance items associated with the vehicle id, photos associated with one or more maintenance items, one or more parts associated with each maintenance item, and receipts associated with the parts...if I wanted to go completely nuts about it could break down the vendor for the receipts and store warranty info too (using front end code to display, based on warranty length and purchase date, if a given part is still in warranty).

The multiple file association thing was my big hangup on this; I think I can make this thing happen now that I know some workable solutions. Thanks folks!

If anyone has further suggestions of course I'd love to hear them.

If you have one table for vehicles, one for photos, and one for cross-reference, you could associate the same photo with multiple vehicles.

E.g. a tow bar, sound system or other removable accessory.

A table for photos is redundant unless you are adding a description, etc. With the simple linking table the photos are referenced by URLs so you can already link a photo to multiple maintenance items or vehicles.

[daerid]

When it comes down to it, you should use what you are most comfortable with.  I only wanted to bring up the no-SQL style DB to give you another approach which had not been suggested and to potentially give you more ideas for how to solve the problem.

Except for PHP.

[kishy]

When it comes down to it, you should use what you are most comfortable with.  I only wanted to bring up the no-SQL style DB to give you another approach which had not been suggested and to potentially give you more ideas for how to solve the problem.

Except for PHP.

Can you substantiate this in some way to justify why you're suggesting I learn a new language to code a hobby project that will never in my life generate income as I absolutely despise the idea of writing software as a job?

If your reasons are valid then some consideration will be given to what you're saying. I appreciate the input but I can't take you seriously if you don't say what the issue is here. You've had two chances to and haven't even hinted at it.

[swill]

When it comes down to it, you should use what you are most comfortable with.  I only wanted to bring up the no-SQL style DB to give you another approach which had not been suggested and to potentially give you more ideas for how to solve the problem.

Except for PHP.

Can you substantiate this in some way to justify why you're suggesting I learn a new language to code a hobby project that will never in my life generate income as I absolutely despise the idea of writing software as a job?

If your reasons are valid then some consideration will be given to what you're saying. I appreciate the input but I can't take you seriously if you don't say what the issue is here. You've had two chances to and haven't even hinted at it.

There is just a lot of hate against PHP out there in the world.  Many people feel that it breeds bad coding practices because so many people can code in it without really knowing what they are doing.  You can write PHP in many ways; scripting, OOP, with different syntaxes, etc, so you can end up with a real mish mash of code.  It is very true that an inexperienced coder can make a real mess of a PHP application.  However, an inexperienced coder can also get something working with PHP, so you have to appreciate it for that. 

I can code in about 10-15 different languages.  I spent years with PHP.  It is not a 'bad' language, but it can absolutely produce some brutal code.  I still work in PHP when I have to work with Drupal or WordPress, but I avoid it otherwise.

For small to medium sized projects I usually end up using Python and bottlepy because I can get things built so quickly.  Python is my favorite language to work in because it is just a fun language.  Simple and powerful...

If you are going to learn a language for this, learn JavaScript.  It will give you everything you need for a client side language and it is also a great server side language now that node.js is here to stay.  That single language will give you more coverage than any other language for this type of project and it performs better than anything else people will suggest. 

Everyone is going to have an opinion of some sort.  Try to take the positive opinions and learn from them and just ignore the negative opinions.  People who have an axe to grind will find a soap box in every square...

Edit:  Here are some interesting stats: http://langpop.com/
PHP used to be much higher than it is now, but it still sits in the top 3 most used languages in the world...