Database Design

Rossmac · 27 Sep 2009 at 19:01

Hey, ok so this is my first time designing a database.

It's obviously a recreate of an instant messenger and I just want to ask for opinions on how this looks.

This is all I need at the minute to get the basic system up and running, from there I'll start adding features.

Should I use a number instead of email address as the primary key?

Are there any things that I'm missing or thinking about it in a wrong way?

Pho · 27 Sep 2009 at 19:36

I'd have:
userid - auto-incrementing number (use this in the friendslist like you have or in other tables as the foreign key) and stick an index on it to speed things up
email - set as the primary key to avoid two people having the same email

I'd move 'online' to a separate table. This way you only need to lookup that table to work out whose online, and not have to scan the whole database of users.

You might also want to encrypt the password, with MySQL you can use something simple like MD5.

Finally, if you wanted to expand (scale) this more you might want to keep the users table simple:
userid, email, password

and move optional fields into a new table:
tblUserOption
id, name
1,forname
2,surname
3,dob

And then create a new table to link options to users, i.e.:
tblUserOptions
userid,option,value
1,1,John
1,2,Smith
2,3,1986-01-01

etc. This way you can scale it easier without having to keep adding columns to the users table.

dangerstat · 27 Sep 2009 at 19:43

I'm not sure what your goal is here, are you looking to create your own instant messenger application?

If so, then you're better starting at the other end of the spectrum, writing a server and client application that enables *very* basic stuff at the start and building on that.

Rossmac · 27 Sep 2009 at 19:53

dangerstat said:
I'm not sure what your goal is here, are you looking to create your own instant messenger application?

If so, then you're better starting at the other end of the spectrum, writing a server and client application that enables *very* basic stuff at the start and building on that.

I already have a server and a basic client written (I'm looking to rewrite the client using WPF in C# though over the next week). I'm currently using an Access DB file which has got the above design pretty much. I'm currently moving it across to a dedicated SQL Server so that I can transfer all of my data access/writing to stored procedures instead of direct methods from the server code.

It's just the database design that I want to get right first time!

djkav · 28 Sep 2009 at 20:52

I'd have:

Tables:
Users (UserID [PK], Email, Password, Disabled)
UserProfiles (ProfileID [PK], UserID [FK], Firstname, Surname, DOB, DisplayName etc)
FriendsLists (FriendsListID [PK], UserID [FK], FriendUserID, Blocked)

Relationships:
Users --> UserProfiles (UserID)
Users --> FriendsLists (UserID)

[PK] = Primary Key
[FK] = Foreign Key

You will also want to put a cascading delete into the relationships too. So if you delete a User from the table Users. The DB will automatically delete all related records to that UserID.

Hades · 29 Sep 2009 at 00:14

Personally I'd avoid using the email address as the primary key on most tables. What happens if the user wants to change their email? I'd suggest a new table which maps email address (unique indexed) to a userid. Then use the userid as the primary key on the other tables. That way you can easily change the users email address without having to change more than one table.

I appreciate you only have a couple of tables now and it isn't an issue but if your database grows then it could be

Rossmac · 29 Sep 2009 at 04:00

Thanks very much for all of the suggestions so far! I'm going to be reading up for a while on better database design as I'm not the most clued up.

Hades: yes, I too think that's the best idea, so I have a new unique userid created each time a new row is added to the users table so that the email could potentially be changed at a later date and I'll be using the userid as the primary key.

Many thanks for the advice on the designs to be thinking about and I'll take each of them on board and get the best one I think I best understand

Sic · 29 Sep 2009 at 08:55

I would listen to Hades. Even though it's only likely that it's for practice, always aim to do the best, and get the most scalable app you can.

In theory, an email for a primary key is fine, but if the user wants to change it, there could be a lot more leg-work involved. If you're using a storage engine with a strict view on relationships, it could prove fun to change that value. That being said, an (innodb) on update cascade might save you.

A unique key is the way I'd go, then programmatically make sure that no-one else tries to use that email.

When it comes to securing your password, I would do the following:

- define a private key that is stored in some sort of configuration file in your app.
- when a user creates/changes their password, create a random string of 50 or 60 characters (the more you use, the better, imo) and hash it with your private key and their password. Then store the hash as the password and your random string as a salt or something: sha1(private_key . salt . password)

then when the user tries to log in, take their submitted password and do the same to make sure it's right: sha1(private_key . salt . password) - if the hashes match, you're in business.

It's more of a security thing than a database design thing, but the extra column makes it kinda fringe on database design!

Dj_Jestar · 29 Sep 2009 at 09:30

primary key on userid.
unique constraint on email address.
Index on firstname, index on lastname, and index on firstname lastname (for friend searching).

I'm not sure a separate table is necessary for email address, unless you want to either maintain multiple email addresses per user, or keep a history of email addresses.

Rossmac · 9 Oct 2009 at 02:58

Sic said:
I would listen to Hades. Even though it's only likely that it's for practice, always aim to do the best, and get the most scalable app you can.

In theory, an email for a primary key is fine, but if the user wants to change it, there could be a lot more leg-work involved. If you're using a storage engine with a strict view on relationships, it could prove fun to change that value. That being said, an (innodb) on update cascade might save you.

A unique key is the way I'd go, then programmatically make sure that no-one else tries to use that email.

When it comes to securing your password, I would do the following:

- define a private key that is stored in some sort of configuration file in your app.
- when a user creates/changes their password, create a random string of 50 or 60 characters (the more you use, the better, imo) and hash it with your private key and their password. Then store the hash as the password and your random string as a salt or something: sha1(private_key . salt . password)

then when the user tries to log in, take their submitted password and do the same to make sure it's right: sha1(private_key . salt . password) - if the hashes match, you're in business.

It's more of a security thing than a database design thing, but the extra column makes it kinda fringe on database design!

Thanks for this, I've always wondered how best to do password security, and after doing further reading I know a lot more