Database newbie - Sound Software Engineering practice for DB

[Mongryong]

You should learn how to design good DBs by hand first if you haven't
done so already.  The two main concepts you'll want to learn about is
'normalization' and 'denormalization'. You should become familiar with
terms like 'primary' and 'foreign' keys.

Once you have a good handle on designing DBs by hand, it should be easy
to translate your design into SQL statements.

Now, it's just a matter of learning the API and applying good
programming design patterns.  The latter part is a huge subject on its
own.  A concept you might want to learn about is 'connection pooling'.

Most of this information, if not all, should be on the web.  Or, you can
do what I did and pick a book on MySQL.  Most books should cover DB
design (by hand), SQL, APIs, and design approaches.  

Good luck!

On Tue, 2003-01-21 at 19:19, Otto Tronarp wrote:
> Hi,
> 
> I'm a complete database newbie, the little I know I learned the last
> couple of hours when browsing the web, and have a couple of questions on
> what sound software engineering practice is when it comes to using
> databases.
> 
> Say that I want to make an app that calculates some statistics of soccer
> matches in several different leagues. So I have the following SQL table:
> CREATE TABLE matches (date DATE,
>                       home_team VARCHAR(20), 
>                       away_team VARCHAR(20), 
>                       home_score INT, 
>                       away_score INT, 
>                       league_id VARCHAR(5));
> 
> (BTW: Is it smart to have have all the matches from all leagues in the
> same table or should I have one matches table per league?)
> 
> So if I want all the matches where arsenal plays at home I could do
> (with MySQLdb) something like this (untested code):
> 
> con = MySQLdb.connect()
> cur = con.cursor()
> cur.execute("""USE soccer""")
> cur.execute("""SELECT * FROM matches WHERE home_team="Arsenal" """)
> result = cur.fetchall()
> 
> But it doesn't look to good with all those "raw" SQL statements
> cluttered everywhere in the code, if I change the structure of the table
> I might need to change the code in a lot of places. So, I thought it was
> a good idea to hide it in a class, maybe something like this.
> 
> class LeagueDB:
>     def __init__ (self, con, dbname):
>         self.con = con
>         self.dbname = dbname
>     def _newCursor(self):
>         cur = self.con.cursor()
>         #with MySQLdb I could use db.select_db but this
>         #doesn't look like a part of the DB2 API so....
>         cur.execute("""USE %s""" % self.dbname)
>         return cur
> 
>     def getHomeMatches(self, team):
>         cur = self._newCursor()
>         cur.execute("""SELECT * from matches WHERE home_team="%s" """,
> team)
> 	return cur
> 
> 
> But often I want different constraints on the query, like only matches
> before a specific date or in a specific time period, etc. I could do a
> getHomeMatchesBeforeDate(self, team, date),
> getHomeMatchesForTimePeriod(self, team, start_date, end_date) but it is
> pretty clumsy so I want something like getHomeMatches(self, team,
> constraints), any ideas how I could implement that in a smooth way? Or
> am I completely off track and should use raw SQL queries all the time?
> Or is there another way?
> 
> Further, returning the cursor and operate on the rows directly exposes
> how the table is structured so I thought something like this would be
> appropriate  
> 
> class LeagueDBResult:
>     def __init__(self, cur):
>         self.cur = cur
>     
>     def __iter__(self):
>         return self
>    
>     def next(self):
>         row = cur.fetchone()
>         if not row:
>             #maybe close cursor?
>             raise StopIteration
>             m = Match(row) #Constructs a match object from a row
>         return m
> 
> and change the return in getHomeMatches to return LeagueDBResult(cur).
> 
> Am I on track here and this is "The Right Thing" to do or should I go
> back to the drawing board?
> 
> Does anyone have any suggestion on a smal app that uses databases in The
> Right Way that I could study and learn from? Or any other pointers
> suggestions?
> 
> 
> Regards,
> Otto
> 
> 
> -- 
> http://mail.python.org/mailman/listinfo/python-list