Effective design for sql database

Question

I am relatively new to mysql and wondered about the optimal structure for storing anything. Different forums seem to give different suggestions.

Imagine that I was trying to create my own mail service, I would have a user table:

|userid|username|password|joindate| 

If I wanted to store each user's emails in a table, how would I do it? One of the solutions I 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 will contain each email belonging to each user. It seems too cumbersome, is there an elegant way around this?

Answer 1

Firstly, a great question. It's great what you think and ask about it, instead of charging ahead and doing something that will quickly break down in the real world. Of course, if this is your first database, you are likely to get it the way you usually study. We all wrote some kind of scary spaghetti code, but the difference between a professional and an amateur learns from pain and invests in making the best decision next time.

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

Try to think about the big picture: not only what you need now, but also what you may need in the future, for example, the CC field, which Kate indicated. There can be several recipients in an email, therefore instead of To and CC should be fields in the EMails table, a reliable design will have an EMailDestinations table with the fields EMailID , Destination and DestinationType . This is already an example of a more extensible design: with this model you can start tracking BCC with just another DestinationType . But then perhaps this is over designing for your project, if you can be sure that there is only one destination for email. It is important to consider all the possibilities, even if in the end you get a simple design that meets your needs.

Good luck Feel free to come back to SO with questions in the future. If you have a clear and specific question, you will usually receive an answer VERY quickly.

Answer 2

I am relatively new to mysql and have been reflecting on the optimal structure for storing something.

Define "optimal."

Which database structure may be “optimal” is highly context sensitive.

Optimal for what? Inserts, selects? What selection criteria are connected, ordered, etc.

this table will contain all the email belonging to each user. It seems too bulky, is there an elegant way around this?

What exactly do you find bulky? It is about as simple as it gets.

Answer 3

This is an elegant way. This allows you to do things like this:

 SELECT * FROM email WHERE email.userid = $userToSearchFor 

Answer 4

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

Then this data may relate to other data.

those. in your case, your data is related to the user (via userid). All your users are stored in one table, all your letters are stored in one table.

You may have a more complicated relationship, for example, if an email is sent or sent to several people. Then you might want another table to handle the mapping between users and email messages.

You probably need a date in your email and a primary identifier so that you can relate to a specific email from another table.

Answer 5

Honestly, I think it depends on the service you create.

It is perfectly acceptable to use an email address as a username, because by definition it must be unique. In this case, I think it definitely belongs to the user relation.

In your example, keeping a few emails per user, your provided solution should work just fine.