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

 

[DaPi]

Its sounds like that you do need an "industrial strength" db - and they cost . . .

My guess is that Access -> MS SQL Server would provide the cheapest migration path in terms of man-power. This could easily be the most expensive part of the project. DaPi - no silver bullet

 

[dannywild82]

just to comment. if you have no formal training and have programmed only in vb, you cannot write 'any program'.

 

[zemp]

By 'any program' I think OPMCoordinator meant 'any program' in a language he is familiar with. Refering more to the complexity rather than the language or syntax. If not give him the benifit of the doubt.

Even with VBA, programming concepts can be learned. Syntax will change but concepts remain basically the same. Besides he knows about Tek-Tips, lots of resources at your finger tips.


I agree with DaPi, you will need one of the big boys. A good solid high end database. Since you are familiar with microsoft product, quirks and problems it will probably be easier and cheaper (in the long run) to use MS SQL Server. Don't get bogged in short term thinking.

Thanks and Good Luck!

zemp

 

[mikecollett]

Thankyou for your open mind zemp.

when i was 12 i was writing games in gwbasic - remember that?

i have university programming training. I had a university degree in y hand before i turned 19.

At 19 i was a government representative on foreign national fishing vessels.

After running a sawmill i was asked at the age of thirty to set up 60 million nz$ (about 25 million $us) of radiata veneer manufacturing equipment and train all brand new staff and develop the administration systems around the way i wanted it set up. which turned a profit in the third month i might add.

there is nothing, i repeat nothing that i cannot do or learn should i be inclined to do so.

Ive only been playing in vb for about 4 months and i have worked out every thing i have wanted to do (with a little help at times granted).

so yes please, give me the benefit of the doubt.

i am willing and am more than capable to learn another programming language.

Mike C
(with a very out of joint nose)

 

[zemp]

"there is nothing, i repeat nothing that i cannot do or learn should i be inclined to do so."

I love that attitude!! But sorry, I have only heard about gwbasic. I got into computers only 4 years ago, Got tired of the politics in professional coaching and after more than 10 years needed a change. Computers seemed a good place to start. Back to school for a crash course and now I am developing and selling software all over North America and the Carribean. I have been told many times that I can't do something. To hell with them, if I want to do something it will get done.

All the best with your project OPMCoordinator.

Thanks and Good Luck!

zemp

 

[pweegar]

Try taking a look at MySql. There are versions for various platforms including Linux and NT. It's touted as being an industrial strength DBMS. And the best part it's FREE to download and is open source. Create a front end for your db's in Java, Perl, C++, etc.

 

[dannywild82]

apologies. didnt mean to imply you are not a competant individual. however, i would contest the logic of a project manager who would give you the go-ahead with a project such as this.
University Programming training simply isnt enough. Besides, only 15% of a software project time is spent programming. Even then, much more education is necessary to understand the potential conflicts, design issues, platform considerations and schema field theory required to implement a semi-ciritical solution.

I fully agree with everyone having a 'happy hack', but this will only end in tears.
Maybe your best bet is to produce a prototype to present to management and then bring in consultants.
Best of luck,
Dan

 

[DaPi]

"gwbasic" that's one of those new languages that runs on those toy machines that IBM have just invented, isn't it? I hear that a Mr Gates wrote the operating system - sounds like a career limiting move to me. DaPi - no silver bullet

 

[mikecollett]

hello im back, was having a little trouble logging on (for 2 weeks!)
gw basic is an old old programming language. I used to use it on an pc10 series 3 around 1985(?). i think it was a 186 processor (186,286,486,686,1meg... im now on a 1.8gig!)anyway...

as for our management, im forever contesting their logic! (I used to be management, but took a step back for family reasons - wifey is preggy yet again!)

What i want to do is tap into three of our existing databases (that i cannot change at all - a) Stores and Maintenance control "Avantis" oracle - b)Accounting "FMIS" oracle - c)Finished goods tracking and ordering "JK" japanese parent company sponsored program ?could be a row of people with piles of stones for all i know?)
ie we know what raw material comes in, finished product goes out, and how much money comes in and goes out. What happens in the middle is a fragmented picture.

that MySQL looks interesting alright, and our IT people are looking into it (the professionals!), but it seems likely that we will go the MS SQL Server path ($30,000nz to set up!)

thanks for your advice

Mike C

 

[bartsimpson]

I remember GW BASIC too - a long time ago. I've now implemented a few databases, and quite agree that you need to upgrade to a new "industrial strength" system. But databases are an art in themselves, have a lot of linking and relationships that need to be carefully worked out.
It would be a really *bad* idea to try this yourself, as the potential to mess up is huge.
I agree with dannywild82 - the best way forward would be to bring in consultants to design and implemnt the database. You can then get into something like Crystal Reports to get data out - and you will learn how such a database should be set up - hopefully !

Good luck with your project.

 

[skiflyer]

I would really push your guys to look into MySQL, it's at least as good as MS SQL, (I say better, especially when this new version gets released)... and from what you've described it's almost certain you can develop most of your solutions in PHP, which is incredibly rapid development.

Definately the kind of thing you could pick up with the right resources and some decent skills.... and as stated above, all for free.

And now with Innodb, MySQL even supports transactions... personally I'm still baffled when people setup MS SQL, anyone want to tell me why?

-Rob

 

[dannywild82]

skiflyer - mySQL is free, and is stable. however. It does not support user defined domains or nested statements, and does not enforce referential integrity through foreign keys. Sure, it allows you to define a foreign key - but does nothing with it. Im sure theres more too. But for the cost / benefit i would prob still use mySQL.

Love you all

Dan

 

[skiflyer]

Ok, nested statements... there's a big one... but foreign keys?

Who wants their database to enforce foreign keys? That's silly overhead that your programs should be enforcing anyway (have you ever tried to use SQL or Oracle error messages from foreign key violations to enforce them? What a pain). If you're using a heavy duty database you're not putting fields in by hand... straight up it's a performance hit, then you add the obfuscated error messages which make doing it progimatically easier anyway.

I forgot about the nested queries and some of those other built ins that come with the big boys... and then of course everyones favorite word... legacy.

-Rob

 

[dannywild82]

um foreign keys.
golden rule - the lower down you can handle the error - the better.
it seperates your code, so not handling low level errors in UI code. you can just use triggers to handle anyway.

Performance hit????

you think your code is going to be more efficient than formally tested vendor code??? please!

also, its not going to be the server side processing thats the bottleneck anyway! more your data retireval and network bandwidth.

please reply this is the first intelligent discussion ive had on here!


Dan

 

[manufcs]

Hello OPMCoordinator,

Decission to change database depends on many factors out of which I am pointing out few of them.

1. The size of existing database. More the database size better data recording tool you require.
2. Type of application whether it is LAN/WAN.
3. Facilities or utilities you currently require, or may require in future like SQL Server support DTS, whereas Oracle has SQL*Loader, IMP, EXP.
4. Security features database server provide. "Slammer" has violated security of well known database server.
5. Management Skills - Do you have enough management skills, and knowledge about the tool.
6. Your Budget.
8. Compatibility with other tools, servers, hardwares, languages.
9. additional hardware and software requirement.
10. Past history of the company and the stability of the tool.

I would consider these factors and many more as per my reqirements. These are basic FAQs that should be asked before taking any decision.

Manu

 

[skiflyer]

dannywild...

I don't expect my code to be more efficient than the vendor tested code, especially not on something like database lookups.

The issue isn't just which is faster, the fact (in my experience anyway) is if you're using the database to validate the foreign keys, you're still using your own code to now read and interpret these errors and do something with them.

My code simply needs to run sql command and a comparison to see if the foreign key matches.

There is some validity to going lower down, especially when your a vendor pushing your software out and some idjit may be programming whatever they like.

But when it comes to this, I just as well enforce my own foreign keys, and take that load off the database, not to mention remove from my code the interpretation of those error messages, which (again, in my experience) are obfuscated and non-standard.

Sometimes they're not even consistent, as they may vary based on a myriad of factors... do they spit back your original query? do they spit back the name of the database, do they spit back some server information, version information, etc. etc.... making that particular chunk of my code a pain in the but if I want to pass along something useful to my user, whereas when I've written the foreign keys into my code, I can have nice simple error handlers which are in plain english and are easily understandable (unlike this post, sorry, tail end of squishing 50 hours into 4 days so I can knock off early friday). They're also resistent to moving the server, heck, alot of it is even resistent to switching the type of database.

So all that said, yes if your only and primary concern is that no bad data ever get in the database, what you're saying I agree with 100%.

If you have that concern, plus the concern of informing your users in plain english when something doesn't work as expected, then I believe the best way to address both is to build the foreign keys into your code.

-Rob

 

[jwanyoike]

Hi all, am a fresh programmer and i would only wish to get comments from the reast of you comparing Access database and Mysql.
Briefly tell me the demerits and strength of each against the other.
I would wish to have a system which would hold thousands of records in a networked environment of more than 10 computers.

Thanx
Joel

 

[manufcs]

Hi,

I know few of them. Hope someone can add to these.

Access is not ment to support centralized database system.
MySQL support it.

Access supports RDBMS, whereas it is enforced from front-end in MySQL.

MySQL support larger database size then Access (exactly how much that needs to be find out)

MySQL is open source and free whereas Access needs to be purchased.

MySQL make seperate file for each table whereas Access holds all tables in single database file. I feel this is a drawback of MySQL.

MySQL does not support Stored procedures or triggers, whereas Access can.

This may help you jwanyoike

Manu

 

[skiflyer]

(By the way, as of Jan 21st, MySQL now does support nested queries, so disregard that as a drawback posted earlier, triggers and stored procedures are supposedly on the way)

Access is commonly known to degrade in performance around 5-10 users... I'm not sure where the documentation is on this, or if it's even an exact science... but I know it's a commonly heard comment on Access.

MySQL is a powerhouse, it's what you'd want if you were developing a site which is going to have hundreds of thousands of queries a second.

MySQL ties the closest to PHP, though it has connectors for just about any language, but with connectors sometimes come performance issues..

Access ties the easies to ASP and VB, so if you're used to one of those for development, that makes a big difference, again connectors to just about any language.

Access is (IMO) easier if you're going to be entering significant amounts of data by hand, rather than through some sort of front end.



(*Note, the nested queries and such are still in a version which is gamma level software, the release version of MySQL does not support these yet)

Those are my brief thoughts on each, I use Access pretty regularly at work, but whenever it's my own project and I have a choice, it's MySQL all the way.

-Rob