Database gurus: What kind of database is right for...

I am developing a 3-tier database application for a library (perhaps a small, mobile library) which is to store info about the literature contained within.

This is the biggest project I have attempted so far, and I have a few questions.

1) Can a 3-tier DB app run on a standalone PC (or is a 2-tier better)

2) What kind of database is best for such a project (I reckon about 20,000 books will be stored in the DB under fields like Author, Publisher, Category etc...)

I will be able to create DB types included in the VB Data Manager Add-in, so things like Microsoft MDB files, Paradox DB's, DBase etc...
The application should preferably be able to run on a small network (with a server) too, but thats not essential.

What do you Database gods think?

Thanks.

Adam

[gorthims]

Use MSDE
it is a desktop as well as client server and you can store up to 1GB of data in it

Can I use SQL to access this sort, and is it accessible from the data environment? (I know nothing about databases, and i'm having to read tutorials from a DB book!).

[Frans C]

Yes, I think MSDE is a good choice. You can easily upgrade it to microsoft sql server if necessary, and it is free!!
Have a look at:
http://msdn.microsoft.com/vstudio/msde/

my god, 20.000 records, that's not so much. use Access with ADO to get your app up and running. if it should become necessary to upgrade to SQL Server (or whatsoever), you could do that easily. it still is comparably comfortable to develop with mdbs as backend. if you stick to (as) standard SQL (as possible), porting to other DBMS should not be much of an issue.

i forgot to note, that the DBMS became less important, with n-tier apps, since you only adapt the data-centric layer slightly (if your framework is designed properly), when porting your app to another DBMS. so don't bother too much about the DBMS, the overall design of your app is of much more importance.

[Chris]

Hi, Sascha I don't think MS Access with ADO can handle this task. Although it onlt 20,000 records which MS Access can hold it properly, but when come to 3 tier architecture it will be more user access the database at the same time & this the wickless point of MS Acess.

As far as my knowledge, 3 tier application normally will store all the data under the so call SQL server, oracle, DB2 or others RDBMS database and ths call the Data Logic.

Then the programmer write a COM and install it under the transaction Server so call MTS. So every user under the LAN or others user access from outside through internet also can have the same algorithms to retrieve the data from the SQL server and normally this call Business Logic. I think this what Sascha mention "data-centric layer" rite.

Finally, we only have the User Logic deploy in the user terminal which only a bundle of GUI and does all the data display manipulation.

So, wossname, I think you're free to use any database that can have a better handle when there are a huge amount of user access the databse and make sure it won't corrupt so far. I don't think MS Access can fullfil your requirement. For low cost may be you can look for MSDE as gorthims and Frans C recommended.

i understood, that the app should run on 1 machine or maybe on a small network.

with a proper 3-tier design Access can handle quite a few concurrent users anyway, since the connections to the database are kept very short.


what happens is typically something like this:

-user clicks something on UI, UI sends a data request to business logic

-business logic requests data from data centric logic

-data centric logic opens database, runs the necessary query/ies, closes connection to database, sends data to business logic

-business logic handles state issues and sends data to UI

-UI refreshes

as you see, the connection to the database is only opened for a very short time. if you have 10 users with a solution like that, it will still be very seldom, that a 2nd or even 3rd database connection will be established. so the multiuser shortcomings of Access are up to a certain point passed by, using a 3-tier architecture. i have tested it, and it works well, if you don't have a huge amount of transactions.

so again, 20.000 records and (let's say) 6 - 10 users not running much more then 1 database request a minute should be easily handled with an Access backend.

Chris, is that Bugs the telly program with Jessy Birdshaw, erm...that woman off The Bill, the Aussie and the other two?

Thanks for the advice guys, I have started to download the MSDE stuff.

Matt, the Aussie fella is Craig McLoughlin from Neighbours. And I think its Jesse Birdsall, can't remember the bint's name (Jay something?) though.

[Edited by wossname on 05-30-2000 at 11:40 AM]

[gorthims]

That is the better way. U use MSDE only. It not so big and it is not so small. Since you wnnt to store huse binary data it is a better choice.

ALL THE BEST

what's huse binary?