Need some smart database guys' help

[dmurray14]

Hey guys,

Hopefully someone has some experience in this. I am looking to design an inventory control system for a Nursery (farm of trees) that just does basic things like storing all the inventory, and allowing them to sort by type of tree, location, etc. They would like this to be customized to their farm. Originally, I was going to do this all in ColdFusion, but they need to be able to update out in the field. They will have laptops (or 1 laptop for now) to go out and inventory the trees. CF could be used with wifi, except the farm is rather large, and they have two of them, one a few miles away from the office. So, they will probably need to take inventory on the laptop and then come back to the office and connect it to the network to download the information to a server that all the computers will have access to. Only problem is, I'm not sure what the best way to go about doing this would be. Any ideas to what I could use as a base program? I was thinking Access, but it doesn't seem to be intuitive enough, and I couldn't figure out how to allow them to do a search and sort by a particular item. As I said before, I would like to use ColdFusion, because I am rather familiar with that, but they won't be able to access the network half the time. Any other ideas? Your help is greatly appreciated.

Thanks,
Dan

 

[techfuzz]

Search will be the deal breaker for you. You're going to have to use an enterprise caliber database to enable full-text indexing on fields in the database. MS SQL, Oracle, MySQL, etc... Access has a limited search ability, but it's not very robust and doesn't handle full-text indexing.

They could take the inventory by hand and then go back to the office and input the results. You could also tag all the trees with a barcoded tag and give them scanners to walk around and scan each tree's barcode. That may be too much work depending on the acreage.

techfuzz

 

[DT4K]

How about a PDA with some version of MS Access on it. It would be easier to carry around then a laptop. Write a program that allows them to enter the the inventory data in a local access db, then write a batch upload function that goes through all the information in the local db and inserts it into the main db in the office.

 

[StageLeft]

Costs are a concern here. If you have a lot of money for the project I would of course recommend sql server to you. If costs are a big concern you'll need to go with something cheaper like MySQL (free).

I'm sure that Access has the power to handle what you need in terms of its size restraints and processing speeds, but you will lack functionality with it, though from what I remember it's a lot more feature rich than MySQL (I've not touched access in some years and only played with mysql briefly).

 

[DT4K]

I don't see why searching in Access would be a problem.
I'm sure Access would work for your such a simple app like an inventory control

Select * from tbl where PlantName like "*clematis*"

 

[rh71]

You have the luxury of creating a web application here because of ColdFusion. I've created web-clipping apps before (Palm VIIx wireless) that hit a .html page containing a simple form. The processing of the form is done via .cfm pages (form action goes to whatever.cfm).

You could create a web app to do the inserting as well as querying of the data (Access db should be fine) all through the laptops hitting your CF server. If distance is a problem, you may want to look into handhelds that hit ISPs (palm.net or any smartphone that has browsing capability) that in turn hit your CF server. The power of CF is a browser away.

Man I wish I were still working with problems like these. CF was fun.

 

[DT4K]

I was only suggesting Access as the db. I fvcking hate writing UI's in Access, but if cost is an issue and you don't have VB and can't make it browser based because you don't have a connection to a server, you could write the PDA or laptop app completely in Access. Can you run IIS on a PDA? That would be pretty funny.

 

[AEnigmaWI]

The data you are working with doesn't sound all that complex.

I would think you could easily use Access to accomplish what you need. Depending on what they are wanting to do as far as reporting goes, I would think some well designed forms linked to queries that sorted by type or tree, location etc. would work fairly easily.

Access is relatively easy to implement as well, IMO much easier than a true client/server database application.

The cheapest thing would be (as someone mentioned above) hand written inventory, then enter at central location into database. Keeps the complexity of the tech infrastructure down, and is easier to maintain in the long run. Plus, anyone can do inventory with no training or need to loan out expensive hardware (laptops etc.) to be bumping around a nursery.

It is easy to do searching and sorting from various levels in Access, and I don't see why one would need the ability to do full text search if the table was designed properly to begin with.

~Stan

 

[LikeLinus]

Originally posted by: Shanti
I don't see why searching in Access would be a problem.
I'm sure Access would work for your such a simple app like an inventory control

Select * from tbl where PlantName like "*clematis*"

You've hit the nail on the head.

All you need is an ASP front end with an Access database. You can search from any table (even creating dropdowns in ASP to search via what category you want). It's nothing too hard and Access should work perfect in this situation. A good number of people already own it, all you need to do is build an ASP front end.

 

[DT4K]

Originally posted by: LikeLinus

Originally posted by: Shanti

I don't see why searching in Access would be a problem.

I'm sure Access would work for your such a simple app like an inventory control



Select * from tbl where PlantName like "*clematis*"

You've hit the nail on the head.



All you need is an ASP front end with an Access database. You can search from any table (even creating dropdowns in ASP to search via what category you want). It's nothing too hard and Access should work perfect in this situation. A good number of people already own it, all you need to do is build an ASP front end.

Or a cold fusion front end, or a VB front end, or a Java front end, etc., etc, etc.

 

[yllus]

I would go:

1) MySQL database
2) VB.NET application for the laptops, written to save data as an XML file

Then you just have to write a little XML -> MySQL input script and your problems are solved. Additionally write a MySQL -> XML script that can 'synchronize' current data in your DB onto each laptop before the user heads out into the field.

The above is in the case that you can't have an Internet connection out there in the field. If you can, write a central ASP/PHP/CF application that each laptop logs into and uses to view/update data.

 

[NuclearNed]

Try this - it's a free product from Microsoft, and works great:

MS SQL Server Desktop Engine

 

[techfuzz]

Originally posted by: NuclearNed
Try this - it's a free product from Microsoft, and works great:



MS SQL Server Desktop Engine

2 GB limit on database size, but if that isn't a problem it's a great alternative to its expensive cousin.

techfuzz

 

[tkdkid]

Originally posted by: techfuzz

Originally posted by: NuclearNed
Try this - it's a free product from Microsoft, and works great:



MS SQL Server Desktop Engine

2 GB limit on database size, but if that isn't a problem it's a great alternative to its expensive cousin.

techfuzz

Access also has a 2 GB limit on database size.

Personally, I think the biggest problem is the database synchronization. There may be some databases that handle synchronization automatically. I know that SQLServer can do it.

Since your application will be 'sometimes connected', all the web solutions are out. I suppose that the ideal solution would be to install mySQL server on the laptop and a central server. Write an application in VB, or VB .net, or Java, or whatever to manage the inventory. Then write a synchronization routine that the user can manually initiate when they're connected to the lan that uploads any new and changed data to the central database. It would also download any new/changed information to the laptop's database. You would have to decide what subset of data to send to the laptop, since you probably wouldn't want to have a full copy of the database on it.

The same solution will work with MSDE and SQLServer, since the laptop's database should never need to exceed 2 GB.

Since mySQL has a fairly large fanbase, you may be able to find a free synchronization utility out there somewhere.

Also you might be able to install whatever is required for coldfusion apps to run on the laptop, making it a CF server. Just a thought, I have no idea how to implement that.

 

[dmurray14]

Wow, I never expected this many responses, thanks so much!

Let me try and fill in some information I was missing and respond to some of the answers.

First of all, I would use Access if I could, but I just can't fiure out how to make it search correctly. For instance, I would like to let them be able to say show me all of the [tree names] and where they are. Or, show me all the trees in row X. I know exactly how to do this in ColdFusion, it's very easy, but in Access it's just not making sense to me.

Second, I could use Visual Basic if I needed to. I only have VB6, but I could probably get ahold of VB.NET if I needed to, and presuming that it isn't to different from programming in VB6, try to do it that way. I wouldn't really know exactly how to connect to the database, so hopefully there would be some tutorials if this is the best option.

As far as the size of the database, I couldn't begin to estimate. The farm is rather large (in my mind), covering something like 170 acres, but I don't think it's going to come close to 2GB.

Next, I have some experience with MySQL, and would be able to use that if that is the best option, but I wouldn't know how to make it work locally and then sync up with the main database. I guess I could figure this out with a little bit of work. The great thing about MySQL, as someone said, is that it is free. Money IS a big concern here, because although they do have a rather large farm, they operate on a rather small budget, and an even smaller computer budget (they still have PII-233s in there - guess what's getting replaced first ).

As far as connection to the server, I would love to have a long-range wifi netowrk going, and I think it could easily be done so that it is easily accessible anywhere on the farm that has the office and network in it, but the other farm is a distance away, and is an issue. You must understand that getting a good internet connection is tough in their area - so far, no one has been able to even install DSL because they are so far from an adequately sized town.

As tkdkid said, I guess I could always install CF server on the laptops too, but that would be really expensive at $1000/pop, unless there is a cheaper alternative. If there is another language that is as easy to master as CF, I would be all up for using that as a web interface.

Also, to whoever suggested they use the old pen-and-paper, yes, that could be done, but they're looking for a system that will allow them to do this all out in the field.

Anyway, I hope this added information might help with some suggestions that would get me on my way. Thanks very much for all the information so far, it is REALLY appreciated. Keep this great stuff coming!

Thanks again,
Dan

 

[DT4K]

So do they need to be able to check inventory from out in the field as well as enter inventory data?
If that is the case, the synching of separate databases is probably not a good idea. You really need a connection to a server. Would it be possible to have a wireless network on each farm site? I'm not a networking expert so somebody else will have to say if that is possible or not based on the size of the area you need to cover. If so, the communication between the two could be over some kind of vpn over a 56k dial-up line or maybe an ISDN. As long as the apps running on the remote laptops or PDA's aren't doing something like retrieving huge inventory lists, you really shouldn't need to pass much data, so a 56K or ISDN line should be plenty of bandwidth.

VB.Net is much much different than VB6. The only thing similar is the syntax and keywords. Honestly, if you already have VB6, I don't see any reason to use .Net. VB6 would be perfect for a client server app like this. I'd suggest a VB6 front end and either an Access or MySQL database. To be clear, I would only use Access as a database. I would not build your interface with it.

As far as having trouble searching in Access, I don't know how to help you with that. If you design your table structure well and know how to do basic sql queries, you should be able to do everything you want with ADO connections and sql queries from a VB6 app.

Very simplified example of what you were saying you wanted to be able to display:
Table structure:
tblItems(Name, Class, Type, LocationRow)
Paper Birch | Tree | Deciduous | X

1. select Name, LocationRow from tblItems order by Name ASC 'gets recordset of all items and location
2. select Name, Class, Type from tblItems where LocationRow = 'X' 'gets recordset of all items in row X

 

[dmurray14]

Shanti,

Thanks for the reply. I am familiar with basic SQL queries, so that should be good. As far as the network idea...it would require 2 connections, and they really only want to be paying for one. I could look into it, but I'm not quite sure how that would work. I'll have to look into cellular data...an iPaq with maybe a T-Mobile internet card might work.

Thanks for the ideas!

 

[rh71]

Smartphones... or whatever cell phone with browsers... just use the existing internet-capable service and hit a CF front end. CF was built for database access specifically in mind so the searching is done through SQL pointing to your Access datasource. Or use built-in Verity. It's really a simple job... and you can hit it from anywhere. Am I missing something ?

 

[DT4K]

Originally posted by: dmurray14
Shanti,

Thanks for the reply. I am familiar with basic SQL queries, so that should be good. As far as the network idea...it would require 2 connections, and they really only want to be paying for one. I could look into it, but I'm not quite sure how that would work. I'll have to look into cellular data...an iPaq with maybe a T-Mobile internet card might work.

Thanks for the ideas!

Sure.
Unfortunately, I know next to nothing about networking and that seems to be the biggest obstacle in this project. Does seem like a cellular type connection might get very expensive depending on if they charge by the minute or whatever.

 

[dmurray14]

Anyone else?

 

[tkdkid]

You may have already answered this, but do they need to search data while out in the field, or only enter it?

It's the disconnected-ness of the aplication that makes it difficult. It doesn't sound like you're going to be able to get a persistant connection to a server, at least not very cheaply. If you can get it through cellular, that would be great, and it shouldn't be too expensive.

Other than that, go buy a vb6 book that covers data access, that should give you some ideas.

 

[Rogue]

Do both farms have phone lines? Hell, connect the networks together with a modem over a phone line with wireless at both locations! That's how business much larger than this used to get done in the first place. Do wireless at both locations and sync data over dial-up.

 

[Evadman]

Searching in Access can be done, but it is less elegant of a solution. however, it is basicly free, since it is likely it is already installed.

I am using it to keep track of almost 13 gigs of data spread across 29 DB's with 89 users. So yes, it can be done. You can even use VBA to generate some nifty Barcodes for inventory tracking

 

[dmurray14]

tkdkid,

Sorry about that, I meant to include it in the previous message. No, they do not need to search data out in the field. They just need to be able to input it.

Rogue,

I would imagine both farms do have phone lines, but in all honesty I'm not sure. I like your idea, but the only problem is, it would mean that they would need yet another server over there, and we really are on a tight budget.

Evadman, I'm glad to hear that it can be done. I'll have to look at some websites and see if I can figure it out.

Thanks guys, keep it coming!

Dan

 

[DAM]

Originally posted by: dmurray14
Hey guys,

Hopefully someone has some experience in this. I am looking to design an inventory control system for a Nursery (farm of trees) that just does basic things like storing all the inventory, and allowing them to sort by type of tree, location, etc. They would like this to be customized to their farm. Originally, I was going to do this all in ColdFusion, but they need to be able to update out in the field. They will have laptops (or 1 laptop for now) to go out and inventory the trees. CF could be used with wifi, except the farm is rather large, and they have two of them, one a few miles away from the office. So, they will probably need to take inventory on the laptop and then come back to the office and connect it to the network to download the information to a server that all the computers will have access to. Only problem is, I'm not sure what the best way to go about doing this would be. Any ideas to what I could use as a base program? I was thinking Access, but it doesn't seem to be intuitive enough, and I couldn't figure out how to allow them to do a search and sort by a particular item. As I said before, I would like to use ColdFusion, because I am rather familiar with that, but they won't be able to access the network half the time. Any other ideas? Your help is greatly appreciated.

Thanks,
Dan

What is your timeline? I may be able to maybe do this for you, free of charge. I am nearing finals, so our timelines may not match.


dam