Ambiguity != Versatility
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
In my professional career I've been fortunate to work on (don't take this the wrong way) small Databases like Imarc all the way up to DB's consisting of 200+ tables branching across 5 DB Schema's. I'm not the end-all of DB designers but I've picked up a few things.
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.
@Nick: Using the Rails methodology of writing a table, and depending on the associations, you'd have a join like so...
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.
My apologies... the correction needs to be made for the person to address relationship.
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)
I'm not much of a "brand name" programmer. I don't like calling design patterns things. Ruby on Rails, Model-Glue, Fusebox, etc. I just use the design pattern itself. MVC (Model View Controller) is the current method in use for me (I'm a CF programmer). Because of that fact I would agree, naming conventions, to database tables, are very important. I think Patrick's examples are very good and was agreeing completely. The point I was pushing across is that a lot of "tips and tricks" out there only work inside small 5 page 2 table configurations. I, like many, work at what I call the 10,000 foot level. I MUST look outside my little world to make sure I have not broken integration or created duplicates.
DELETE FROM resources WHERE info2 NOT LIKE '%Patrick's examples are very good%';
You'd need to escape the single tick ( ' ) in "Patrick's"
sonofabitch
Read something more recent.
Statements and opinions expressed in this blog and any comments made are the private opinions of the respective poster, and, as such, iMarc LLC is neither responsible nor liable for such content.
Visitors
I prefer a mix of the two, with a number of "boilerplate" fields at the start of each table followed by the table specific fields. 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