Database Design

[Rossmac]

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?

 

[Rossmac]

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!

 

[Rossmac]

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

 

[Rossmac]

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