Advice on which type of DB to use

[mikecollett]

Hello all.

I am thinking about building a new database to supercede the raft of access databases that exist on our site. There are about 10 reasonably big access databases that all hold a part of the picture that i want to paint. It has become unsuitable to hook into each one for the bit that i need as each user tweaks their own database, causing my stitching efforts to fall down, requiring me to trace and cope with the changes. Luckily ive got the clout and backing to effect a change if i can come up with a sound solution.
Breifly, we are a fairly large(450 employee) timber processing facility with different styles of product and therefore tracking and reporting needs (inputs/outputs/conversions/downtimes/quality rates/goals and targets/etc are all reported on). Each line has its own reporting staff and databases each with many years of data that is archived off annually as copies of the access databases, making trending over years a nuiscance.
I think I have reasonable Access skills, (havent come across anything i havent been able to solve thru VBA as a result of experimenting, courses or advice from sites such as this), so i reckon I can tackle any program.

My understanding is that access is good up to a certain number of records. Id say we are well past the max. SQL server exists, I know that, but the cost would be prohibitive i think.

So, what brand of database would you suggest?

Cheers
Mike C

 

[c0i0nes]

Off the point, but how much data are we talking about? Like if you copied all the tables off all the "raft of" database files and dropped them all into a single new MDB, and made all the relevant joins, how many Gigabytes? Sorry, how many Megabytes are we talking about?
Guide: File size: Access 97 1 Gb, Access 2000, 2 Gb
And then, just for fun, cleansed all the out of date or irrelevant data, all the bad data and inconsequential fields, and corrected the correctable data, and pidgeonholed the duplicate and dubious data. Then perhaps rationalise and normalise the data you have left.
Guide: 100,000 records per table
Guide:10-20 simultaneous users
100Mb or more? Yes, you need to consider moving your back-end to a grown-up database. Not this year, maybe, but before too long. At 5-10% of Microsofts safe estimate,you need to consider a better option, and plan the changeover.
However there are issues, and you have users who are familiar and comfortable with Access front ends.
All you have left now is the 10-odd front-end Access databases that your users are familiar with, and you need to link them to your single rejuvenated back-end database.
Especially if you consider upgrading everything, you need to rejuvenate your back-end first. Access clients may be of use for the next few years while you build your next generation interfaces.

 

[dannywild82]

um... how about this. mySQL/oracle etc are PROPER databses. access / filemaker etc are kids toys that shouldnt be used for anything than a high school attendance monitoring system.

If access could do it, why do MS make (ok - sell) Foxpro? But then again, why use foxpro when you have mySQL??? - dont bother with a quick fix. head in book all day breeds good results

 

[r937]

what a great thread

some real gems in here

for example, inadvertent humour:

"there is nothing, i repeat nothing
that i cannot do or learn
should i be inclined to do so
...
hello im back, was having a little
trouble logging on (for 2 weeks!)"

another example:

"Who wants their database to enforce
foreign keys? That's silly overhead
that your programs should be enforcing anyway"

which is totally preposterous, egregiously so, and will only end up with you "squishing 50 hours into 4 days"


mike, what about oracle? you said two of your three existing databases run on it

if your IT professionals are already running oracle and are now looking into sql server and mysql, i would like to suggest that you simply go with the one they end up choosing

sounds like there's a lot more going on politically than technologically

if oracle isn't working out for you, perhaps the reason is because the applications that were built on it did their own foreign key checking

sorry, couldn't help it

no offence rob, but you're swimming against the current


rudy

 

[mikecollett]

Firstly thanks to all who have added to this thread, it is great to read what other people think about a topic such as this.

cOiOnes - i have no idea what the final size would be but i am sure it would be smaller than the cumulative size right now (approx 1 gig in files all up). One of the very biggest reasons i wish to do the conversion/rebuild is to standardise the indexes/categories and the like. Some databases use text to link data eg "Rotary Lathe 3.0m" - its enough to make you want to bang your head against a wall.

r937 - how do you look embarrassed in cyber space? well i am right now. i wondered if anyone would say anything after i hit the submit post button on the last mesage. but in my defense i now know what went wrong and it wont happen again. i hope.
I have to change the types to one access, one oracle, one jd edwards and one ms dos based(?) as informed by our techs. They are all off the shelf products that not all of our techs are fully involved with (the so called experts are spread across 5 branches in this country and all across japan).
currently i am leaning towards sql server, principally because i wont need to start from scratch, lessening implementation time. It also means that should i find i need a consultant (i never shy from asking for help and advice) i have a better chance of understanding what he says. Also there are a lot of users who are familiar with access and could build their own front ends. that involves the users, reults in front ends that look and behave how they like (i dont really care as long as the data is all there and in a format that is safe and whole)

anyway, keep it coming folks, it is fascinating reading!

Mike C

 

[r937]

yes, keeping Access as the front end is an excellent idea

File > Get External Data > Link Tables

uses ODBC to connect to all kinds of databases

(caution: doing a join between tables on different databases is certainly possible, but the join takes place using the Access engine)

SQL Server, if your IT folks choose it, is a top-of-the-line database system, and other than the licensing fee, and the cadre of in-house engineers you will require to keep it running, it's great

rudy

http://r937.com/

 

[GiffordS]

My oh my. There are some SERIOUS egos at work here. I'm just a dumb ole country boy, but I'll throw my two cents in just for fun.

The SQL Server is certainly the obvious choice. It keeps you in the Microsoft family, and allows the VBA skills you've acquired to continue growing. The downside is, as has been pointed out, that SQL Server is touchy and very high maintenance.

If I were you, I would have pushed real hard for the MySql solution. Sure, it would mean more work in getting up to speed with MySql, but it would also expose you to a whole world outside of the "evil empire". From what you have described here, and having at least a little familiarity with implementing dbs, I don't think there's much you are doing that MySql isn't going to be able to handle. MySql's scalability, and the fact that it's open source, would be the biggest selling points. Open source, in this case, means that you've got some of the best minds in the world working on new and better versions, and that you'll see those new versions much sooner than you will from Microsoft.

That being said, my real opinion isn't even about the databases. If I stop and look at this from the point of view of your CEO I start to pull my hair out. Forget what some of these ppl say about formal education versus real world experience. Based on what you've said you've proven yourself to the powers that be time and again. If I'm the CEO I'm having a very long conversation with my IT staff, and I'm asking them why I'm still paying them if they can't manage to hand you a plan that doesn't leave you hanging out to dry. Right now, how it's set up, if everything goes fine they look like heros, but if it flops it all falls on you. I'm sorry, but the IT management of a large international company really should be able to hand you one database solution, and more importantly, they should be able to make it easy for you. Really, they should be embarrassed.