Efficient design for sql database

I am relatively new to mysql and was wondering the optimal structure for storing something. Different forums appear to give different suggestions.

Imagine I was trying to create my own webmail serivce, I would have a users tabl开发者_Go百科e:

|userid|username|password|joindate|

If I wanted to store each users emails in a table, how would I do this? One solution I have found is to have an emails table and do the following:

+------+--------------+---------------+
|userid|subject       |message        |
+------+--------------+---------------+
|1     |Hello         |some message   |
|1     |Another email |another message|
|2     |An email for 2|message        |
+------+--------------+---------------+

i.e. this table would contain every single email belonging to every single user. This seems overly cumbersome, is there an elegant way round this?

I am relatively new to mysql and was wondering the optimal structure for storing something.

Define "optimal".

What database structure may be "optimal" is heavily dependent on context.

Optimal for what? Inserts, selects? With what selection criteria, joins, ordering, etc.

this table would contain every single email belonging to every single user. This seems overly cumbersome, is there an elegant way round this?

What exactly do you find cumbersome? This is about as plain as it gets. ?

That is the elegant way. It allows you to do things like this:

SELECT *
FROM email
WHERE email.userid = $userToSearchFor

that is the ideal of relational data, is that the same data is stored in one table.

Then that data can have relations with other data.

ie in your case your data has a relationship with user (through userid). All your users get stored in one table, all your emails get stored in one table.

You may have a more complex relationship like if a email is CC'd or sent to multiple people. Then you might want another table to handle the mapping between the users and the emails.

You probablly want a date in your email table, and a primary id so you can have a relationship to a specific email from another table.

First off, excellent question. It's great that you're thinking and asking about this instead of charging ahead and making something that will quickly break down in the real world. Of course, if this is your first database, you'll probably get that anyway, that's how learning usually goes. We've all written some scary spaghetti code, but the difference between a pro and an amateur is learning from the pain and investing the effort to make a better solution next time.

There is no single simple answer to your question, and others have made the important points. I would add this: pick up a short book on normalization (I've had good results with the "Nutshell" series by O'Reilly). This may sound like a big topic, but the gist is simple: any specific piece of information is stored only once. This saves space, but more importantly means that you never have, say, a user's name stored inconsistently in different tables.

Try to think about the big picture, too: not only what you need now, but what you might reasonably need in the future, such as the CC field which Keith pointed out. An e-mail can have multiple destinations, so rather than having To and CC be fields in your EMails table, a robust design will have an EMailDestinations table with fields EMailID, Destination, and DestinationType. Already this is an example of a more extensible design: with this model, you can start tracking BCCs with just one more DestinationType. But then maybe that's over-engineering for your project, if you can be sure that there's only ever one destination for an e-mail. The important thing is to consider all the possibilities, even if you end up with a simple design as adequate for your needs.

Good luck! Don't hesitate to come back to SO with questions in the future. If you have a clear and specific question, you'll usually get an answer VERY quickly.

Honestly, I think it depends on the service you are creating.

It is perfectly acceptable to use an email address as the username since, by definition, it must be unique. In that case I think it definitely belongs on the User relation.

For your example, storing multiple emails per user, your provided solution should work great.