I am working on a webmail client. I want to store the remote mailboxes information in a Postgres database. As a general rule of thumb, most IMAP servers will have a similar default folder structure like so:

    • Drafts
    • Outbox
    • Sent Messages
    • Trash
    • Junk Mail

etc, etc.

As I add users to my system, this information will be repeated across most users. So here's my question:

Considering these are small-ish strings, am I better off to just store all the mailbox information in one row (ie: mailbox name, attributes, delimiter, parentId, hasChildren) or should I store the mailbox names in a separate table and join on a foreign key?

Right now I'm thinking the former as the cost of storing the extra information is probably less than the cost of throwing more computing power at the database to maintain the same performance. But I am a novice when it comes to database design and I want my application to scale well.

  • How many accounts are you expecting? even 50,000 accounts with 10 folders each should not be a big deal on modern hardware. – Max Vernon May 26 '16 at 18:32
  • @maxvernon 50,000 would be great! Since this is in development, it's hard to say how much traction this offering is gonna get. But yes 50,000 is a good number to go by for now. Getting to 50,000 users would give me enough time to switch it over if I see the need for it I guess. So you saying, don't bother trying to optimize for space for now? – ReX357 May 26 '16 at 18:35
  • 1
    I wouldn't optimize for space on this particular column. Optimizing for space would be useful if the column was going to be a foreign key used in many other tables. – Max Vernon May 26 '16 at 18:37

Joining the mailbox table to a "FolderNames" table using a foreign key will be problematic if any particular user wants to change the name of any given folder, without changing that name for everyone else. You could get around this by having a nullable CustomFolderName column in the mailbox table.

Depending on the expected number of accounts, storing the name of the mailbox in the mailbox table is probably not a deal-breaker for performance, unless perhaps there are details you haven't shared with us. When you say these are "smallish strings", don't specify the column as having a maximum length of 1000 characters if your app will limit the folder names to 15 characters.

Overthinking optimization early on can be burdensome to the development effort. Focus on the database, just don't go entirely overboard at the very earliest stages of development. You're quite likely to change the structure as development progresses anyway. Ensuring you use the correct data types as appropriate is going to be far more important. For instance, please don't store dates in string columns. Use a date column. If there is going to be a time element, use a date/time column. Don't use 128 bit integers where 32 bit will suffice.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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