A Beginner'S Guide to SQL Database Design

A beginner's guide to SQL database design

I started with this book: Relational Database Design Clearly Explained (The Morgan Kaufmann Series in Data Management Systems) (Paperback) by Jan L. Harrington and found it very clear and helpful

and as you get up to speed this one was good too Database Systems: A Practical Approach to Design, Implementation and Management (International Computer Science Series) (Paperback)

I think SQL and database design are different (but complementary) skills.

Inserting / Creating SQL Database Values

For names, should I use varchar / text / tinytext / blob? What is the typical name length?

• If you're only going to support "normal" Western European / English names, then a (non-Unicode) varchar type should do
• If you need to support Arabic, Hebrew, Japanese, Chinese, Korean or other Asian languages, then pick a Unicode string type to store those characters. Those typically use 2 bytes per character, but they're the only viable options if you need to support non-European languages and character sets.

As for length: pick a reasonable value, but don't use varchar(67), varchar(91), varchar(55) and so forth - try to settle on a few "default" lengths, like varchar(20) (for things like a phone number or a zip code), varchar(50) for a first name, and maybe varchar(100) for a last name / city name etc. Try to pick a few lengths, and use those throughout

• E-Mails have a max length of 255 characters as defined in a RFC document
• Windows file system paths (file names including path) have a Windows limitation of 260 characters

Use such knowledge to "tune" your string lengths. I would advise against just using blob type / TEXT / VARCHAR(MAX) for everything - those types are intended for really long text - use them sparingly, they're often accompanied by less than ideal access mechanisms and thus performance drawbacks.

Indexes: in general, don't over-index your tables - most often devs tend to have too many indexes on their tables, not fully understanding if and how those will be used (or not used). Every single index causes maintenance overhead when inserting, updating and deleting data - indexes aren't free, use them only if you really know what you're doing and see an overall performance benefit (of the whole system) when adding one.

Learning Data(base) Modelling - Start-up Guidance

I suggest you to take this course at Stanford (free, online): http://db.class2go.stanford.edu

Similar course is available at coursera (free, online): https://www.coursera.org/course/db

You did not mention, for what purpose you would like to learn database modelling. If data warehousing is is also on your list of skills, you should consider the following books:

• Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema
• The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling (Second Edition)
• Star Schema The Complete Reference

---

Related Topics