Keith Devens .com

Entry 1191

The Keith Devens School of Database Design is now in session.

For myPHPNuke, which I just mentioned, someone posted a table design for their users table. I'd like to take a look at that now.

Here are a few of their fields:
user_icq varchar(15),
user_oicq varchar(15),
user_aim varchar(18),
user_yim varchar(25),
user_msnm varchar(25),

As you can see, these can contain addresses for AOL Instant Messenger, Yahoo Instant Messenger, ICQ, Microsoft Netmeeting, etc. (I'm not sure what oicq stands for). This type of scheme forces you to modify your table whenever you want to add a new type of contact like maybe an ICC (Internet Chess Club) handle, an Unreal, Quake, Half Life, etc. handle, you name it. It also forces you to select each of those fields in code when you want to display it, so not only do you have to change your table, you have to change your code too.

Here's how something like this should arguably be done. It requires two more tables, possibly named Optional_Address_Types and User_Addresses. Here they are:

Optional_Address_Types
Address_Type_Id
Address_Code
Address_Name

User_Addresses
User_Id
Address_Type_Id
Address_Value

Sample data can be as follows:
Optional_Address_Types:
Address_Type_Id Address_Code Address_Name
1 ICQ ICQ
2 AIM AOL Instant Messenger
3 YIM Yahoo Instant Messenger
User_Addresses
User_Id Address_Type_Id Address_Value
34 1 10083592
376 2 AIMuser3621
376 3 yahooIMuser3626

You do a join, you print out the data, and you're done. In the user preferences screen, you want to give people another type of address they can enter, you add a record to the Optional_Address_Types table and you're done. You can even easily have only those optional addresses that the user actually specified in the user's info page, without having to say:

if($user_icq){print 'ICQ Address: $user_icq';}
if($user_aim){print 'AOL Instant Messenger: $user_aim';}

You just basically say, "for each type of extra address they have specified, print it (and sort it in alphabetical order, or whatever)"

As a result of all this, your code maintenance is easier, your code is more flexible, you don't have to go changing the database when a cool new technology comes out and you want to be able to store its address type, and your user table is much less cluttered.

Now I'll turn to the user table itself. There are many examples of bad design principles here. I'll give some example fields:

name varchar(60) NOT NULL,
uname varchar(25) binary NOT NULL,
email varchar(60) NOT NULL,
femail varchar(60) NOT NULL,
counter int(11) DEFAULT '0' NOT NULL,
umode varchar(10) NOT NULL,
uorder tinyint(1) DEFAULT '0' NOT NULL,
thold tinyint(1) DEFAULT '0' NOT NULL,
ublockon tinyint(1) DEFAULT '0' NOT NULL,
ublock tinytext NOT NULL,
pass varchar(40) NOT NULL,
user_from varchar(100),
user_from_flag varchar(25),
user_regdate varchar(20) NOT NULL,
user_occ varchar(100),
noscore tinyint(1) DEFAULT '0' NOT NULL,
posts int(10) DEFAULT '0',
rank int(10) DEFAULT '0',
level int(10) DEFAULT '1',

Ok, first of all, they're suffering from the common notion that short names are better. They're not. That's one of the first and simplest rules of database design. Descriptive names make you type a little more, but they're more understandable for everybody. Most importantly, they're self documenting, so you don't actually have to go to the code to find out what a field really does. For instance, name and uname, should be Full_Name (and that arguably should be split up into First_Name and Last_Name), and User_Name. Next, email and femail. What the heck is femail for?

Next, what's umode and uorder? What are thold, ublockon, ublock. Pass is their password, but why not just say password? Does it really give that much extra satisfaction to save four characters?

Next rule. Any time you see a field with a generic name, like counter, level, rank, or posts, you need to be more descriptive. Consider counter. What does counter do? Does it count how many times they've posted, their "karma" level, the counter for how many times they're user info page has been viewed? What's the difference between level and rank? Also, it's usually a bad sign any time you see a field with "flag" in the name. What does user_from_flag do?

Anyway, I don't need to enumerate every single field, but before I go... tell me they're not storing a date as a varchar in user_regdate!. Dates should be dates! Databases give you date and time and datetime data types. They have functions that act on dates: you can compare them and add or subtract with them in the database itself.

Finally, they have all these "radmin" fields:
radminarticle tinyint(2) DEFAULT '0' NOT NULL,
radmintopic tinyint(2) DEFAULT '0' NOT NULL,
radminuser tinyint(2) DEFAULT '0' NOT NULL,
radminmain tinyint(2) DEFAULT '0' NOT NULL,
radminsurvey tinyint(2) DEFAULT '0' NOT NULL,
radminsection tinyint(2) DEFAULT '0' NOT NULL,

and on and on. I assume this means, "this user has rights to administer articles, topics, users", etc. This is also a candidate for the same type of solution I used above with my lookup table. If you add a new "type" of thing (maybe you're allowed to administer mailing lists from within the system) you have to modify your table again!

I should really go to bed, so this concludes this class session