Ambiguity != Versatility
by Patrick McPhail - June 9, 2007 / 12:17pm View more articles
I used to do this all the time:
foreach ($a as $k => $v) {
// stuff happens
}
I don't do that anymore. Although maybe if I had an array full of 'As' with keys related to the letter 'K' and an inexplicable value that had something to do with the letter 'V'...
Meaningful naming conventions are even more important in database schemas.
Let's look at two examples of a MySQL table.
First, the good (with thanks to Fred)
Snice!
TABLE careers
+------------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------------+------+-----+---------+----------------+
| career_id | int(11) | NO | PRI | NULL | auto_increment |
| title | varchar(255) | NO | | NULL | |
| post_date | date | YES | | NULL | |
| scope | enum('full','part') | NO | | NULL | |
| summary | text | YES | | NULL | |
| responsibilities | text | YES | | NULL | |
| qualifications | text | YES | | NULL | |
| status | enum('active','inactive') | YES | | active | |
+------------------+---------------------------+------+-----+---------+----------------+
isn't it gorgeous? I know exactly what I'm going to get out of those fields (I prefer commitment over scope, but that's just semantics, I guess). I also know what's in this table simply by glancing at the name.
Now, the not-so-good.
Boo-Hiss
TABLE resource +------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+----------------+ | Resource_ID | int(11) | NO | PRI | NULL | auto_increment | | Type_ID | int(11) | YES | | NULL | | | Date_Posted | date | YES | | NULL | | | lister | varchar(150) | YES | | NULL | | | Info1 | varchar(100) | YES | | NULL | | | Info2 | varchar(100) | YES | | NULL | | | Info3 | varchar(100) | YES | | NULL | | | Info4 | varchar(100) | YES | | NULL | | | Info5 | varchar(100) | YES | | NULL | | | Info6 | varchar(100) | YES | | NULL | | | Lister_Member_ID | int(11) | YES | | NULL | | | Lister_Vend_ID | int(11) | YES | | NULL | | | Cat_ID | int(11) | YES | | NULL | | +------------------+--------------+------+-----+---------+----------------+
I know what they were going for: by using an ambiguous table name, with ambiguous column names, one could put pretty much anything in the table and not be "wrong".
Then here's me, three years later, attempting to troubleshoot the site. Imagine my surprise when I come across the "resource" table, on a site where there isn't a single instance of the term. Next step: I start describing tables, thinking I'll be able to glean some info from the structure. Guess how well that went?
I'll end the suspense and tell you that this table holds classified ads. Duh.
Meaningful names are the jam.
Oh, and since I'm being cranky I might as well advocate using lowercase exclusively. God help you if you try and select Lister (whatever that is) or info1 from the resource table
Comments have been turned off on this blog.
Read something more recent.
Hiring: We’re hiring a
RSS
8 Comments
id,name,created,modified,description are what I normally use. I don't have to remember whether it's book.title or book.name, and I like the way city.id looks compared to city.city_id
1. Mal, city.id does look nice....until you have to foreign key it in a junction across 4 tables. Imagine this example for someone's address: (just the where clause)
WHERE
address.id = person.id
AND person.id = city.id
AND city.id = state.id
AND state.id = country.id
Another hiccup is the "boilerplate", imagine the same query but you want all the values, this would be your Select Statement:
SELECT
address.name,
person.name,
city.name,
state.name,
country.name
Granted, I'm a huge fan of aliases, but to me this is just nuts. I won't even go into using an INT for a primary key that auto-increments. That's a convo for another day.
2 cents a couple times over.
WHERE
address.id = person.address_id
AND person.city_id = city.id
AND city.state_id = state.id
AND state.country_id = country.id
If there were one person to multiple addresses, one city to multiple people, one state to multiple cities, and one country to multiple states. Clear architecture, and clear joins allow for very simple and rational decisions when it comes to architecting your database.
Do check out Ruby on Rails, if not for anything other than the inherant database structure and data model schema that it follows by convention.
WHERE
address.person_id = person.id
AND person.city_id = city.id
AND city.state_id = state.id
AND state.country_id = country.id
This is due to one person possibly having more than one address (home, work, etc)