Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

For a programmer with no prior exposure to databases

  1. What would be a good database to learn Oracle vs SQLserver vs MySQLvs PostgreSQL?
  2. I have come across lot of discussion MySQL and PostgreSQL and frankly I am confused on which to start with. Are these very different, in the sense if one had to switch, would the exposure to one be counter-productive to learning the other?
  3. Is working with databases heavily platform dependent?
  4. What exactly do people mean by Data base programming vs. administration?
  5. Do people chose databases based on the programming language used for the application developed?
  6. In general, Working with databases is it implicit that we work with some server?
  7. Does the choice of databases differ when it comes to game development? If so what factors does it differ by?
  8. What are the Best Tips that you have found to be useful when learning databases

Edit: Some FAQ i had and found the same on SO

  1. What should every developer know about databases?
  2. Which database if learning from scratch in 2010?
  3. For a beginner, is there much difference between MySQL and PostgreSQL
  4. What RDBMS should I learn/use? (MySql/SQL Server/Oracle etc.)
  5. To what extent should a developer learn database?
  6. How are database programmers different from other programmers?
  7. what kind of database are used in games?
share|improve this question
    
Do you need to use a database or maintain a database installation? –  user1249 Mar 12 '11 at 11:41
    
This would be in regards to using a database in development process –  Aditya P Mar 12 '11 at 12:23
    
Points 1 and 2 are off topic. –  ChrisF Mar 12 '11 at 23:02
add comment

2 Answers

up vote 5 down vote accepted

I think you will find many people on here that provide answers from similar experience to mine.

Over the last 12 years I have worked with MySql, PostgreSQL, SQLServer, Oracle. More recently, NoSQL in the form of App Engine Datastore, SQLite in iOS development.

  1. Any one of these are advanced and provide solid SQL standard capabilities. They also take turns in providing functionality outside of the standard. PostgreSQL has the most flexible license. If you work on windows primarily or even do Access programming you can use MSDE (microsoft desktop engine?) inside of Access instead of Jet. MSDE files can be directly mounted by SQLServer. So MSDE programming is SQLServer programming. The databases vary the most once you start programming code that runs inside the database. Also XML and OODB features are vastly different from database to database.

  2. MySql and PostgreSQL are both quite feature rich...many of the old arguments from a few years ago no longer apply. Both are readily available in very affordable hosting plans. If you are a linux terminal wizard.. you may find that PostgreSQL's console interactions are little nicer. If you like to dable with opensource apps that use a database you may find more apps support MySql first and PostgreSQL second.

  3. Platform independent. I am guessing you are asking Windows/Linux/Solaris/OSX/AIX..etc. The database providers work very hard to make their databases run on as many platforms as possible (except MS). Oracle has some additional .NET APIs that provide enhanced interop with the MS ecosystem. Outside of SQLServer, the database can run on most any platform.

  4. Programming vs. Administration (DBA). You can sort of think of Programming as the development cycle activity and Administration as the production or rehearsing for production activity. DBA's usually are the gatekeepers and health workers of the database. They won't usually work too closely with application code riding layers above the database. Programmers will work and often code changes for the database but may have to get them cleared through the DBA. DBA's likely also have the heavy responsibility of ensuring the security and safety of the data.

  5. Language can often be a factor, however if a company has a database, usually you will be using the same platform. These things can be a large investment in time and personel resources. Once you have a system that works and you have practices in place for working with it they typically want to stay with it.

  6. Yes. a server somewhere. unless your app is not distributed. SQLite is a fairly capable database that can run with multiple clients. It is now in your webbrowser (HTML5) and on your iphone. It supports enough SQL to get you rolling. We may see more and more client side database development happening.

  7. I can't really speak to game development.

  8. Great Question. Words of wisdom I have heard from others.

A. The database is not a magical box. You must learn the complexity of what is happening under the hood. Queries on sizeable databases can take 2hours to return and if written differently may return in 2minutes. Know what is happening a level below what you are developing in. EXPLAIN is an SQL term that will show you the databases plan, this is a good place to start.

B. You can do very terrible things to a production database...You will be hard pressed to find a battle worn database dev/dba that hasn't accidentally blown away a table or updated all records to lastname='Prince Jr.' instead of just Freddy's. Respecting the power and magnitude of the mistakes that can be made.

C. If you use SELECT DISTINCT and now you are getting the result you want, make sure you know why.

D. UNION does something different than UNION ALL

E. When move a lot of data into or out of a database. Find out how to BULK LOAD/COPY/DUMP. Parsing and INSERT statement is expensive!

F. Bind variables!! Most adapters you will use to talk to a database will allow you to bind your variables.. "SELECT * FROM foo WHERE candy = ':1'" ('chocolat'). After the first time you run this the database will have a plan and know what to do again and again.

G. "SELECT *" should not be used in production code. It is for adhoc fooling around only.

H. Referential integrity is really good stuff. FOREIGN KEYS are your friend.

I wish you the best of luck. Database development was and still is a passion of mine. In a world where most programming jobs seem to want javascript skillz. Knowing what is going in the backend makes you a much better front end developer.

share|improve this answer
    
very insightful words of wisdom. can you elaborate on 5. based on your experience with various programming languages as to how they factor in choice of database? –  Aditya P Mar 12 '11 at 15:05
    
The language that you use in your application is almost always your choice completely. Say I choose python, then I will use a dbconnect/dbadapter which knows how to talk over the wire/locally to the database and it passes SQL. So in python I will structure SQL statements and use the adapter to execute them against the database. You can look to use an ORM (object relational mapper) in python (or any other language) like SQLAlchemy. This ORM can for most SQL needs generate the SQL and talk over the adapter for us. Database choice is largely independent of language choice. –  kevpie Mar 12 '11 at 15:26
add comment

1, 2 - Any database dialect is good for learning SQL. I'd suggest you to pick an open source alternative, just because they are easier to install

3.No, database development is not heavily platform depended (MS SQL is an exception). At least don't worry about that at the beginning.

4.Database administration - watching a database server, making sure that it works OK, maintaining server configuraion, etc. Database programming - essentially writing SQL queries, procedures, functions etc.

5.Programming language is not the most important factor of database choice, but it is often one of the factors.

Also see http://stackoverflow.com/questions/110124/tips-for-getting-started-with-sql and http://stackoverflow.com/questions/1332778/what-are-your-most-common-sql-optimizations

share|improve this answer
    
+1 for sql link on SO. –  Aditya P Mar 12 '11 at 14:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.