Database Design

Associate
Joined
10 Nov 2005
Posts
650
Location
Unknown
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.

databasedesign.jpg


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

Pho

Soldato
Joined
18 Oct 2002
Posts
9,281
Location
Derbyshire
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.
 
Soldato
Joined
15 Nov 2008
Posts
5,060
Location
In the ether
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.
 
Associate
OP
Joined
10 Nov 2005
Posts
650
Location
Unknown
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!
 
Soldato
Joined
28 Aug 2006
Posts
2,980
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.
 
Last edited:
Man of Honour
Joined
19 Oct 2002
Posts
28,095
Location
Surrey
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 :)
 
Associate
OP
Joined
10 Nov 2005
Posts
650
Location
Unknown
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 :p
 

Sic

Sic

Soldato
Joined
9 Nov 2004
Posts
15,365
Location
SO16
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!
 
Caporegime
Joined
18 Oct 2002
Posts
29,388
Location
Back in East London
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.
 
Associate
OP
Joined
10 Nov 2005
Posts
650
Location
Unknown
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 :)
 
Top Bottom