User schema for website

Question

I need to create a database schema for storing user information (id, name, p/w, email address ...etc). I have always picked arbitrary amounts when sizing these fields. With this said, I have two questions:

1) What are good sizes for these fields? I am sure there is a maximum email address length for example...etc.

2) I now need to store user mailing addresses for credit card purchases, including international mailing addresses. This is an area I do not want to pick arbitrary sizes.

Does anyone know of a good schema for either? Is there a project for this maybe?

Thanks!

Answer 1

I'll give you a hand with part 1. In general you should stress very much about the size of your MySQL DB fields, you don't have to get the number exactly right -- just make sure that someone with a reasonable answer doesn't get their data truncated.

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255),
`email` varchar(255),
`password` char(256)

Notice that for password I have a 256bit character field instead of a varchar field. Thats because you should never store plain text passwords in a database. Instead, you should always store the password in a hashed format with some sort of unique "salt" for that password. You can find some tutorials online, and the length of the password field depends on the type of hashing you use on the password.

Answer 2

Also consider which db engine you will use and whether the primary key will be email, rowid, or an arbitrary number. I typically save passwords on a second table called "security" using a hash as suggested above. Here's an example.

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` varchar(255) NOT NULL,
  `active` char(1) default 'Y',
  `created_date` INTEGER UNSIGNED default 0,
  `email` varchar(255) default NULL,
  `first_name` varchar(255) default NULL,
  `last_name` varchar(255) default NULL,
  `modified_date` INTEGER UNSIGNED default 0,
  PRIMARY KEY  (`user_id`, `active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 3

This is a pretty tough question to answer, because in my opinion there is a difference between what you "should" allow and what is considered allowable by the IETF.

The maximum allowable email address is 256 characters which includes a slash at the beginning and end of the email address (therefore only 254 usable characters). You can find detailed information about it on this page by Dominic Sayers.

But will any legitimate user actually have an email address that long?

As for street addresses, I don't believe that is specified anywhere, however according to the world's longest website the longest street name is 72 characters. Therefore if you made the field 100 characters you would have more than enough room for the street address.

You don't have to be really too concerned with getting everything 100% correct, you should be more concerned with the quality of the data which you decide to accept into the database (make sure it is valid/clean). Also provide clear rejection messages if someone does enter something which is simply too long -- and make sure there is an easy method for the owner of the website to be contacted if that does happen.

One thing I'd like to note, NoSQL is all the rage right now, and it uses schema-less database engines, for example MongoDB and CouchDB. It is not the best solution for everything, however if you are very concerned about having the correct schema, possibly a schema-less database might be a good option.