Which Database to Use?

[bluewhaleCA]

We are at a point where we're not sure staying with a product is the best path.

We started developing our own database for photos, documents, correspondence etc using MS Access. We moved to FileMaker perhaps 5 years ago. Our question is should we consider moving to some form of SQL?

We have two items prioritized:

1: To be able to use a tablet on site to sync with the camera taking digital photos, ensuring the photo matches the description and location. The information will be entered via pull down menu's. Then hopefully back that data up while on the road or uplink to the main database.

We currently have ~ 12000 items possible on our inspection list. Things like Window Sill, West facing, cracked but not leaking. etc.


2: To be able to access all information to

do a conflict check on all parties to the suit/action

be able to pull all information up on a particular job
to be used in depositions/trials. This includes being
able to pull all photos of west facing window leaks or
tile cracked on first floor inspected.

to be able to search across all data/jobs to look for
trends or common issues. Our current data backup is
1.6 TB and growing at about 1 TB per year.


We have done the Access then FileMaker projects mostly in house, and will probably do SQL the same way if we choose to go that way.

I'm posting this in the General DB section as posting in each applications forum would get answers that favor that product. I'm not looking to start a cat-fight here, but I would appreciate pro/con of one path or another if possible. We have years and a lot of money invested thus far and are simply trying to make a better/more informed decision this time around. :}


Many Thanks


Paul

 

[ousoonerjoe]

MS SQL or Oracle are absolutely a good idea at this point. They are scalable and adaptable to growing and changing technologies. Both have their advantages and disadvantages. It's a matter of upfront costs and how you want to implement it all.

I will add this. Oracle on a Windows server did not perform as well as Oracle on a Sun box with Solaris. Unless that's changed in the last few years, I would keep that in mind.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[Olaf Doschke]

I can see why you started with with file based databases like Access and filemaker due to the need of offline data on tablet PCs.

It's getting easier to do that with flavours of SQL Server. Everyone may think of SQL Server Express for a local server instance, but there's even a more light version: Compact Edition.

Bye, Olaf.

 

[ousoonerjoe]

The MSDE was a great step towards 'portable MS SQL Server'. You can have the ease of relatively small data files, but the power of SQL. Instead of MDB files you use MDF. It has a 2 GB limitation per datafile. It's free and available for distribution. Might be something to look into.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[bluewhaleCA]

Would the consensus ( thus far ) be that the lite version of SQL ( I'm thinking of the MS flavor as many of our purchased applications run on it ) be capable and scaleable enough for this project? Let's say 6 TB's in 5 years.

Or would it be the lite version in the field on the tablets, and something more robust in the office?

I hadn't thought of using SQL lite on a tablet.... was thinking of making the portable database up in a front end app.

Please bear with me as I haven't done much database work aside from small forms and tables. Our DB person doesn't like to speak in forums, thus I'm here :]


Thanks


Paul

 

[ousoonerjoe]

The idea of lite field and robust office is the route I would take. The tablets aren't going to need more than 2 GB of data on them at one time, are they? You can easily create a data transfer process from the Central Database to the tablet's database. At the end of the day, just 'dock' the tablets and have them upload changes that are made on them to the central database. (Only key note here is working out a way to make sure that if both databases are updated with info, there will need to be a process to resolve/merge changes together so you don't lose anything when they resync).

Alternative would be to use an intranet/internet setup with the Tablets using a browser to connect to a website. The website would display your search criteria and results. This would also allow you "to be able to search across all data/jobs to look for trends or common issues".

Both options would probably be a good idea. Place all case pertinent data on the tablet, but if something comes up in court that you need, you can still pull it down off your server.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[ousoonerjoe]

....(still kicking around the idea)

By setting up a central database and utilizing SSRS (SQL Reporting Services) you can easily wrap your searches into a PDF for reasonably quick archival and packaging(CSV, PDF, XLS, etc) to give to opposing council.

As you can see.... there are many different ways you can run with SQL. And the design/development of the system shouldn't take that long at all in .Net. I would suggest building it in-house so you can make all the adjustments and customizations on the fly as needed. Updates and add-ons are at your control and need.

--------------------------------------------------
"...and did we give up when the Germans bombed Pearl Harbor? NO!"

"Don't stop him. He's roll'n."
--------------------------------------------------

 

[MDXer]

You really should be looking at building 2 seperate databases.

Database 1 would be for you first requirement and would be of a transactional design. You could use MS SQL and possibly run Compact edition on the Tablets.

Database 2 would fit for pats of your second requirements and would be of more a DW design.

Remember:
OLTP great for loading lots of data into.
OLAP great for getting lots of data out of.

 

[ceh4702]

Might want to look at something like ImageNow. I dont know its full potential but it is capable of importing FAX documents, and from other sources as well as importing documents through a duplex high-speed SCSI scaner to the Database over the network. No need for uploading later. I have used this interface for Scanning Transcripts, and one or two sided pages of Text. It has a scripting language that controls it, and you can design how it processes the documents.

It may not be what you are looking for.

If you do not like my post feel free to point out your opinion or my errors.