Solved

This is for the Pro's the Database Engineers!!!

Posted on 2001-08-22
15
393 Views
Last Modified: 2011-04-14
Hi there!
I want to study database managament and programing as well. I know how to use access and make databases/form etc. but I want to go further and learn the right stuff (which there isn't). But if you were me which NDBMS would you use/study if you wanted to make a database for 100,000 clients and every client would have 50 fields on the form (search had to de made as well - query)? This had to be accessed on a network with 12 workstations. I know that for a start I needed the DB to be on a SQL server, but at the moment I only have NT4. What is the best way to start? I want to make a good program and also I will have some remote users to access the DB out of the LAN. I am totally new and lost because I don't know what to study and how to start. Here's how confused I am:
Is an SQL a server or a database application like access, foxpro, oracle etc...
I just don't know, I am certain that here there are lots of professional people here that will give me some instructions/websites and will remember their days of headaches/learning as well. Please help a future database engineer/programmer.
Best Wishes
Lagruga.
 
0
Comment
Question by:lagruga
  • 5
  • 3
  • 3
  • +2
15 Comments
 
LVL 5

Accepted Solution

by:
ser6398 earned 50 total points
Comment Utility
Access and Foxpro bundle the database together with the front end (forms/reports) development tools.  Most SQL databases separate the database from the front end development tools.  

"SQL Server" is a specific product, it is a database created by Microsoft (front end development tool not packaged together with it).  Like Oracle, it is just a database product.  You can use the supplied database tools to insert/update/delete/select data, as well as add/delete/modify tables and other database objects.  To do any of this with these tools, you have to learn a language called SQL.  That is where I would start if I were you.  Books such as "Learn SQL in 24 hours" would be a pretty good start.

Next you need to learn how to build a relational database.  This is a very large topic it itself and there are a ton of books on the subject.

It is unlikely that the end users will want to learn SQL to access the database, so that is why you normally create a front end (forms and reports) to allow them to access the database.  What you build the front end in depends on which database you are using.  If SQL Server, then Visual Basic works pretty well.  If Oracle, then Oracle Forms and Oracle Reports works pretty well.  Java works pretty well for either of them (although slightly easier on Oracle).  So you finally need to learn how to work with the front end tool that you choose.  

Be warned that SQL databases are a large topic.  Most people tend to specialize in the database end (DBA) or on the front end development (Developer).  Trying to learn it all can take a while.

Good Luck.
0
 

Author Comment

by:lagruga
Comment Utility
Ser,
thanks for all the comments but a quick one, if I know access making the front end can I use SQL as a back end? Some people say that access is no good when it comes to databases.
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
Access is Ok for smaller databases used by a single client (like a personal database); however, it does not work well in a multi-user environment or when the database gets very large.  If several users are using the database at 1 time I recommend some kind of SQL database (SQL Server, Oracle, mySQL, etc.) instead of Access.
0
 
LVL 4

Expert Comment

by:YodaMage
Comment Utility
I agree with ser6398 for the most part. You can use SQL statements for manipulating most desktop databases, but the client must still perform the operations as you are missing the DB server piece. I'd say that a desktop DB like foxpro, access, or even paradox can be taken further then ser6398 states, as long as you understand the following:

1) The down falls and how to route around them with some creative coding, like using a seperate autoinc generator table instead of the autoinc datatype in a multi user environment. Using table/record locking, which will hurt performance but help avoid corruption.

2) The more users you add to the system, or the more user collisions at the row level, the more code that will have to be written and the more you will have to sow the system. I have written apps to function with up 40 users using desktop DB's, but only in environments where network speed was high, and data entry relatively low.

3) The above point really leads to one of the biggest shortfalls of a desktop DB in a multiuser environment, which is the hardware requirements. For example:
 a- System with InterBase 5.5, 10 clients, NT4 server  
    with (233 p2, 128 RAM), TBase 10 network, clients
    (266p2, 128 RAM) This as a simple example is a workable
    arrangement.
 b- Now lets use Access in place of IB. We are probably OK
    with the server, we will need to upgrade all clients,
    upgrade network to 100bps, and even in doing so will
    lose at least 35% of our speed.

So really the number of clients should not determine your DB selection, but rather read/writes and the ensuing network traffic. Also the size of your tables. A DB with many small tables may allow you to go desktop, but a DB with fewer large tables begs for SQL.

Hope any of this blather helps.
0
 
LVL 4

Expert Comment

by:gizmola
Comment Utility
I guess I'm at odds with YodaMage's perspective on RDBMS vs. PCDB.  From my point of view there are entirely different perspectives.  First, a PCDB doesn't have a server or engine.  All the intelligence in regards to the engine resides in the memory of any/all clients running an application.  The client reads datafile and index data across the wire, performs the actual query logic, and data access routines locally and just has an entirely different philosophy from a client/server RDBMS.

An RDBMS seperates workload, so that the client is really a set of libraries that understand how to to send SQL across the network and receive result sets back.  In most cases, this model is a lot more efficient and scalable than the PCDB turns out to be.

Now the original question discusses 100K clients (although the 50 fields per form, is a little vague).

My answer to lagruga would be, to learn about how databases are structured first, that is, learn the database design (normalization, entity-relationship diagrams, etc.) and learn SQL adequately, before worrying about the answer to the 100k users question.  In understanding those issues, the question of scalability will be highly illuminated, since as we all know, that is a question that really only can be explored within the context of the application.



0
 
LVL 4

Expert Comment

by:JMelchhammer
Comment Utility
Hello lagruga,

well there's some more things I'd like to add here too :-)
If you are new to the concepts of a relational database management system (RDBMS) i think you should rather think of it like a kind of "service" than in terms of something like a "file" (like you might think of as you used Access).
Think of it like a service like the www. You type in your URL and get your information. You know this from Access, what you're doing there is pretty like SQL. You got a question and (most times) you click it together with a technique called "query by example" where the program (Access) gives you choices which fields should have which values until you get your request narrowed down to the data-rows you want to have.
A database works like that - without the user-interface. You do your requests in a Standardized Query Language (SQL) and you do get results. Thats the first thing.
Most times youre precising requests like "SELECT A FROM B WHERE C=D". This is known despite the thought that it only is a service. Thats pretty much like talking about SQL-servers. Every RDBMS is a sql-server, Oracle, db2, Informix, Sybase, Interbase, MySQL etc. etc. (yes, the Microsoft SQL-Server too is, and its a lucky name for an SQL-server, isnt it? :)
I concur with ser6398, you should try to learn about SQL as a computer-language, cause mostly you will have to deal with it.
What depends on initializing the database, i.e. creating tables and relationships etc., this is (yes, ser6398, you mentioned it before ;-) a huge area. Every database is different in installing, maintaining tuning (and even in SQL though its a STANDARD query language) like every os is different in these things. Even every version of one particular database is different here, like, lets say, Win95, WinNT and XP. What I wnat to say is that its pretty much work to learn all aspects of a database (and perhaps of its versions...)
Nevertheless. If you got time then I'd start simple'n'cheap. You should perhaps start with MySQL because thats a database for free (it is, the rest is kidding :). It is not a complete RDBMS like the other ones mentioned before, but you can get and install it for NT (http://www.mysql.com as page [with documentations], http://www.mysql.com/downloads/mysql-3.23.html as download for 95/98/NT/2000) and perhaps a kind of graphical tool to get started with it, like "mySQLfront" (http://www.anse.de/mysqlfront/), then get a good MySQL book (perhaps before installation :) and a good SQL-book, both starter level for the beginning and thats it. You can test, try, install, deinstall MySQL as much as you want (on your NT-"server"), you can try it client/server-like or only on your own workstation etc. This might give you a feeling for database-"server"-working.

So cheers up and have a good time!

P.S.: TO AVOID A "FLAME-WAR" ON THE BETTER DATABASE PLEASE READ CAREFULLY (  :) This advice is given as-is... ah, means I'm not preffering any kind of database here. Me personally am pretty long into the oracle-stuff. Why not suggesting MySQL if it seems appropriate to the questioneer? I did Informix, db2, am planning a medi-huge system based on Interbase and what depends on databases: They all got advantages and disadvantages, more or less, thats why I hate them all. Thats it.

jMelch
0
 
LVL 4

Expert Comment

by:JMelchhammer
Comment Utility
P.S. to all: I pretty much enjoyed this typing, so please be not offended if I got a little bit "over the edge" or perhaps "low/medium/high level" on the one or other point, ok?

P.S.S.: ser6398, how comes that I read a question and think I got the answer and see that you already posted it, hmmmm? :-) Cool for cats!

jMelch
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:lagruga
Comment Utility
Hello guys, first I would like to say thank you for your advice,time/typing etc... To tell you the truth one or two things I am a bit confused now and trust me if you were on my place you would be too. I will read/reread and rereread all your comments and see what I can come up with. Can I just ask one more thing and please don't laugh or think that "this guy is a stupid more on". If I wanted to make a program for data input (front end) as stated on the question which program would you use that would be safe and secure in terms of data/colision etc...
Cheers to the Pros
La Gruga
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
The best choice for front end depends heavily on what you choose for back end (as I stated in my original comment).

If you use Access - it already comes with a front end tool; however, you could upgrade to a Visual Basic front end.

If you use SQL Server - Visual Basic would probably be easiest, but you could use Java as well.

If you use Oracle - Oracle Forms and Reports would probably be easiest, but you could use Java and even Visual Basic.

If you use mySQL - Java, Perl, or Visual Basic would probably be the best.

Each of these programs (together with proper database design) have sufficient ability to make it secure and to maintain data integrity, if you use them correctly.
0
 
LVL 4

Expert Comment

by:gizmola
Comment Utility
If you intend your application to be deployed on the web, there is really only one way to go.  You want to deploy a multi-tier application.   The solutions are either two tier or 3 tier.  In particular java and .jsp have become a popular 3 tier solution, for applications which have significant business logic.  In general the package of java technologies is called J2EE.  I won't talk about this further, but it's certainly something to be considered, and where a lot of heavy duty development is being done these days.  You can reference the Apache Jakarta Tomcat project online if you are interested in exploring this more.

Whether you are 2-tier or 3-tier, in most cases data storage is provided by a relational database.  Your main choices are between opensource products or commercial products.  The main commercial databases are: microsoft sqlserver, sybase (ms sql server was based on this product), oracle, informix, and IBM DB2.  All of these products are strong, and usually the choice has already been made by the corporation for you, so you're just finding ways to interface with them.  In all cases you talk to the database with embedded SQL statements in your front end code or in the objects that comprise the interface to the backend from your middleware.  I guess it's worth noting that the majority of the largest commercial websites use Oracle: Amazon, E-Bay, etc.  

Open source options are mSQL, MySQL, and PostGreSQL.  Of these MySQL is the most popular, since it's easy to setup and use, but PostGreSQL is generally considered the best of the group technically.  RedHat is basing it's new database on the Postgresql code base.

In regards to the web front end,  2 tier solutions have been around longer and are usually more iterative in terms of development.  I think they're a good place fro you to start.   ASP (which uses the VB syntax, and is available as part of the ms IIS webserver), Allaire ColdFusion, and various commercial .JSP server products exist from Allaire, IBM and ATG.  You can usually tell what's being used by the extension of the web pages:  .asp (microsoft IIS w/ASP) .cfm (cold Fusion) .jsp (java server pages).  On the open source front (where the earliest solutions first were developed) you have various .cgi options, perl (which is more of a general purpose sysadmin language, but is often used to create .cgi pages or in conjunction with the mod_perl apache web server module.  Probably the most popular solution these days, and one on equal footing with the major commercial solutions is PHP.

If i were to recommend a place to start, i'd recommend this combination of technologies, all freely available for download, with plenty of online documentation available:

Web Server:  Apache
SQL Database: MySQL
Web development language: PHP


These 3 work together, and there are many open source systems based on them.  Sourceforge.net is a good place to find these systems.  Do a search on PHP, and peruse the many projects based on this combination to get an idea of how popular they are.

0
 
LVL 4

Expert Comment

by:YodaMage
Comment Utility
For windows development and even minor web deployment-

Interbase 6 and Kylix (The Linux Version of Delphi), both are free and open source.
0
 

Author Comment

by:lagruga
Comment Utility
HI everyone,

Can I say that I am really sorry for my ignorance and not getting back to this question. Please accept my appologies, I have been here and there.... and so on (not trying to give excuses). I am really impressed with all comments here and it really gets difficult to give points out, If I could I would give points to everyone. Can I just say that I'll read all info again and will give a result to you shortly.  Once again, sorry!
Lagruga

0
 
LVL 4

Expert Comment

by:YodaMage
Comment Utility
You can spread points if you like. Post questions with a point value equal to what you think someone deserves, like:

40 points for gizmola
40 points for ser6398

In the body of the question just put something like "Thanks"

Those folks will then answer those questions with something like "your welcome"

At that point either:

A) delete this question
B) ask the Mod to reduce the points for this question to 40 or something, then mark question answered to the last person on the list of those you wish to reward.
0
 

Author Comment

by:lagruga
Comment Utility
Hello partners,

I have spread a few points to you guys please check. Its been there for a while.

- gizmola
- ser6398
- JMelchhammer

Thanks

Lagruga
0
 

Author Comment

by:lagruga
Comment Utility
Thank you for your help.

lagruga
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now