Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to store incoming and outcoming mailserver configuration per user, it's not a real live project, but to learn database-design (never done before).

Let's say I have a mail_server_configuration table.

| MailServerConfigurationId | Host | Port | Username | Password 

and my user table:

| UserId | IncomingMailConfigurationId (+FK) | OutgoingMailConfigurationId (+FK)

That is my current approach.

Q1: Is that the way to go or am I missing something?

Q2: What about data redundancy about hosts and ports? What if two users are using the same host&port? Should there be a "host" table?

share|improve this question

2 Answers 2

up vote 1 down vote accepted
+50

Because you're doing this to learn database design, I'd get strict. I'd also recommend Louis Davidson's book on database design. He focuses on SQL Server, but it's a really well-done book no matter which database platform you're using, and his chapter on normalization is outstanding.

Your user table has repeating attributes: two mail configuration IDs. In a real-world business scenario, this might slide, but it is a violation of first normal form. The way to correct that is to break those out into a separate table, turning columns into rows.

Looking at hosts and ports, there are a couple of possibilities. Do you want to limit appropriate ports, or "know" which port is which (e.g., 25 for SMTP, 110 for POP3, 143 for IMAP)? If so, create a ports table. In that case, you could use either a surrogate key or the port number as the primary key because well-known mail ports are distinct. On the other hand, because servers can use "weird" ports, you might not be able to create that table (or you may need to leave it open for users to add new ports to the list). In a business application, this would be a business decision. In an academic application, I'd probably create the table and make a foreign key connection to the mail server configuration table.

Regarding hosts, you have a similar question. If you want to limit hosts, check for common misspellings, or be able to display more information about the particular host (e.g., pop3.google.com's application name is GMail), you would want to create a Host lookup table, as well as application logic which lets users create new hosts. But there's something else to think about with hosts: how far down the rabbit hole do you want to go? Are you going to analyze based on the host's top-level domain (TLD)? If so, you would want to break out the domain into its own table. In fact, you might have three separate tables: host prefixes (mail, pop3, smtp, imap, etc.), host name (google, yahoo, etc.), and host top-level domains (.com, .co.uk, etc.). Your Host table would then have a Prefix ID, a Hostname ID, and a TLD ID and you could build the final hostname up from that. Whether this makes any sense at all for you to do depends upon the application: if you're using the mail server information to shoot e-mails out but aren't analyzing it further, then this is going too far; otherwise, if you do need to split out hostnames or domains as part of your application, keeping it all as "pop3.google.com" would be a violation of first normal form.

Finally, looking at the mail server configuration table, you have port on there. If your users have different credentials for receiving and sending e-mails, that would make sense, but otherwise, you end up duplicating data: username Bob with password PW at mail.yahoo.com will be the same regardless of whether he's connecting to port 25 or 110. The port really is more for communication than authorization, so it makes some sense here to split it out. You'll still have some "duplication" of sorts because of hosts that have pop3.hostname.com and smtp.hostname.com servers, but I wouldn't worry too much about that, especially if we don't need to split hosts out.

Assuming that you don't need to split out hosts any further, the table design would look a bit like the following:

Host
----------------------------
HostId smallint (PK; surrogate)
Name varchar(75) (AK1)
[additional host attributes]

Port
----------------------------
PortId smallint (PK; surrogate)
Number int (AK1)
Name varchar(20)

Direction { Inbound; Outbound }
----------------------------
DirectionId tinyint (PK; surrogate)
Name varchar(8) (AK1)

User
----------------------------
UserId int (PK; surrogate)
[additional user attributes]

UserHostCredential
----------------------------
UserHostCredentialId (PK; surrogate)
HostId (FK; AK1)
UserId (FK; AK1)
Username
Password

UserMailConnection
----------------------------
UserMailConnectionId int (PK; surrogate)
UserHostCredentialId int (FK; AK1)
DirectionId tinyint (FK; AK1)
PortId (FK)

AK here means alternate key. You'd put a unique key constraint wherever you see that. I numbered them to show you instances in which there are multi-column alternate keys, like mail server configuration.

This is a bit more work than your two-table design, but not really that much more. I would tend toward adding the Host and Port tables simply to get rid of potential update inconsistencies; for example, if Google's new POP3 server is pop3.googlemail.com instead of pop3.google.com, you would have to update all of the rows to match this. But if somebody misspelled a row (or you didn't catch it in your updating process), you now have a data anamoly. In the hosts table, you make one update and you're done.

But one important thing to note (and something that I've tried to hint at up above) is that business rules shape database design. I'm working from one set of assumptions, some of which may not exactly match a particular scenario. For example, I'm assuming that a username and password typically should relate to one person at one host. But if a number of different people all use the same credentials (for whatever reason), this may no longer apply. In that case, I'd move UserId off of UserHostCredential and onto UserMailConnection and then rename UserHostCredential to HostCredential. Both of those are "valid" database designs, and which one is correct depends upon the business purpose.

Similarly, I'm assuming that Port isn't necessarily determinate of direction (inbound or outbound). If you are able to make that determination--e.g., that 25 is always outbound, and there are no ports which are both inbound and outbound--then the direction stops being an attribute of a particular connection and instead becomes an attribute of the port itself. If that is the case, you would move DirectionId from UserMailConnection up to Port and make PortId part of the alternate key for UserMailConnection. Again, both are valid, fully-normalized table designs, but which business rule holds would determine which is the "correct" one for your purposes.

share|improve this answer
    
Thank you. Thank you very much for spending so much time. I really appreciate it. –  lurkerbelow Nov 9 '12 at 8:39

There are multiple ways this could be modelled. There is nothing wrong with the solution you have suggested. Yes some of the data would be redundant in terms of host and ports but there's nothing to say that you need to normalize these columns and create a table for hosts/ports.

I would suggest to have a user table which would contain the username id, username and password.

A table for mail configurations with the mail configuration id, host and port.

A bridge table with the username id, mail configuration id and mail type field to indicate incoming or outgoing.

If the way this system is going to work means that only one user can use a port then the design of the tables would make use of primary keys and unique indexes to ensure configurations don't use the same port.

A little more info on the requirements would be beneficial.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.