Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm teaching myself SQL using MYSQL and a textbook.

While working through the textbook I'm using a database I'm kinda familiar with - an exported copy of my CMS driven websites database.

My website collects and sends emails to people who subscribe each time a new article is added. When I log in to the back end of my CMS website I can see the number of emails sent to each email address. People sign up via the "get alerts" page.

While reading through my text book I am currently covering functions in SQL. Since I am already familiar with my data I thought that it would be a good opportunity to try to recreate the data described above - the count of emails sent to each recipient.

There are many tables in my database but I'm pretty sure that the ones of relevance are called "alert" and "alert_sent".

I have used SELECT to view the top 5 records and try to determine what a common field between the two would be but cannot see any. I then wondered what the standard procedure here was?

When one is given a new database, is there a rule of thumb in figuring out what fields are what? Is there typically a "key" of some sort that defines what tables/fields are showing? Or do DBAs typically have to just browse the data and figure it out?

Apologies if this question is vague. I'm trying to teach myself by example in using a database I'm moderatley familiar with.

If it's only help, here is the results of DESCRIBE on the two mentioned tables:

Table "alert"

Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| id                 | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id            | int(11) unsigned    | YES  | MUL | 0       |                |
| alert_type         | tinyint(4)          | NO   |     | NULL    |                |
| alert_recipient    | varchar(200)        | YES  | MUL | NULL    |                |
| alert_code         | varchar(30)         | YES  | UNI | NULL    |                |
| alert_confirmed    | tinyint(4)          | NO   |     | 0       |                |
| alert_lat          | varchar(150)        | YES  |     | NULL    |                |
| alert_lon          | varchar(150)        | YES  |     | NULL    |                |
| alert_radius       | tinyint(4)          | NO   |     | 20      |                |
| alert_ip           | varchar(100)        | YES  |     | NULL    |                |
| previous_noconfirm | tinyint(4)          | YES  |     | NULL    |               

and table "alert_sent"

Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| incident_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
| alert_id    | bigint(20) unsigned | NO   | MUL | NULL    |                |
| alert_date  | datetime            | YES  |     | NULL    |                
share|improve this question
    
are there any id columns? Can you diagram the DB? Sometimes seeing it visually lets you see it from a different perspective. –  webdad3 Jun 19 '13 at 14:54
    
@oldmanprogrammer I've added the DESCRIBE of the alert tables, there are some columns with ID int he name. Not sure what you mean by diagram - is that a function of some sort? Is there any more useful info I could add? Would a sample of a few rows be of use? –  Doug Firr Jun 19 '13 at 15:02
    
It looks like there are 2 other tables that I would be interested in looking at (user and incident). –  webdad3 Jun 19 '13 at 15:06

1 Answer 1

up vote 1 down vote accepted

I'm not a DBA but I've looked at many "new to me" databases.

I don't know if there is a trick to understanding a database. What has worked for me is just to explore the db and query it to see what it returns.

Many times as a new developer to a project I'm given a task to fix or update something. I usually look at the table like you did with alert and look at any other id columns that might be in the table (i.e user_id & incident_id). That starts the bread crumb trail. Then I look at the foriegnkeys to see what they are doing (i.e. cascade delete). The FK usually point to another table that could also lead to other tables.

If the bread crumb trail of id columns gets bigger than 2 or 3, then I usually try to diagram the db if possible. Sometimes that helps me to see the tables in a different perspective.

Other than that, it just takes time. As you get more tasks under your belt with the db you will understand more of it.

share|improve this answer
    
So there is no rule of thumb per se, just get to know the DB and investigate –  Doug Firr Jun 19 '13 at 19:58
    
thats what I do. I don't think there is a magic bullet. Just dive in and ask questions when you can. –  webdad3 Jun 20 '13 at 14:41

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.