Mysql – NEWB Getting to KNow New Database and Understand Where Relationships Are

MySQLprimary-keyrelational-theory

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    |

Best Answer

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.

Related Question