Good Resources for Relational Database Design

Book: Database Design for Mere Mortals

What resources will help me understand the fundamentals of Relational Database Design

I highly recommend Data Modeling for Information Professionals. You are asking about basic fundamental theory and concepts which are much better explained in a book than on a Q&A answer site like SO.

Good place to look for example Database Designs - Best practices

Before you start read up on normalization until you have no questions about it at all. If you only did this in school, you probably don't know enough about it to design yet.

Gather your requirements for each module carefully. You need to know:

Business rules (which are specific to applications and which must be enforced in the database because they must be enforced on all records no matter the source),

Are there legal or regulatory concerns (HIPAA for instance or Sarbanes-Oxley requirements)
security (does data need to be encrypted?)

What data do you need to store and why (is this data available anywhere else)

Which pieces of data will only have one row of data and which will need to have multiple rows?

How do you intend to enforce uniqueness of the the row in each table? Do you have a natural key or do you need a surrogate key (suggest a surrogate key in almost all cases)?

Do you need replication?

Do you need auditing?

How is the data going to be entered into the database? Will it come from the application one record at a time (or even from multiple applications)or will some of it come from bulk inserts from an ETL tool or from another database.

Do you need to know who entered the record and when (highly likely this will be necessary in an enterprise system.

What kind of lookup tables will you need? Data entry is much more accurate when you can use lookup tables and restrict the users to the values.

What kind of data validation do you need?

Roughly how many records will the system have? You need to have an idea to know how big to create your test data.

How are you going to query the data? Will you be using stored procs or an ORM or dynamic queries?

Some very basic things to remember in your design. Choose the right data type for your data. Do not store dates or numbers you intend to do math on in string fields. Do store numbers that are not candidates for math (part numbers, zip codes, phone numbers, etc) as string data as you may need leading zeros. Do not store more than one piece of information in a field. So no comma-concatenated lists (these indicate the need for a related table) and while you are at it if you find yourself doing something like phone1, phone2, phone 3, stop right away and design a related table. Do use foreign keys for data integrity purposes.

All the way through your design consider data integrity. Data that has no integrity is meaningless and useless. Do design for performance, this is critical in database design and is NOT premature optimization. Database do not refactor easily, so it is important to get the most critical parts of the performance equation right the first time. In fact all databases need to be designed for data integrity, performance and security.

Do not be afraid to have multiple joins, properly indexed these will perform just fine. Do not try to put everything into an entity value type table. Use these as sparingly as possible. Try to learn to think in terms of handling sets of data, it will help your design. Databases are optimized to do things in sets.

There's more but this is enough to start digesting.

Resources about building an RDBMS

There are a few textbooks about this sort of stuff out there, when I was in college, we did this for a class project. This book should really help you on your way Database Systems: The Complete Book

I forgot to mention it, but my code is on googlecode here: cs4420-dbase

Please forgive the fact it is written in java, but I was outvoted by my teammates on that decision. but the basic ideas are all still there. It handles file creation and handling as well as a simple SQL parser and optimizer. It handles basic indexing (b-tree) and "memory" management. Please forgive some of the lack of commenting and strange commenting, many late nights were spent on that project.

Relational Database Design Patterns?

There's a book in Martin Fowler's Signature Series called Refactoring Databases. That provides a list of techniques for refactoring databases. I can't say I've heard a list of database patterns so much.

I would also highly recommend David C. Hay's Data Model Patterns and the follow up A Metadata Map which builds on the first and is far more ambitious and intriguing. The Preface alone is enlightening.

Also a great place to look for some pre-canned database models is Len Silverston's Data Model Resource Book Series Volume 1 contains universally applicable data models (employees, accounts, shipping, purchases, etc), Volume 2 contains industry specific data models (accounting, healthcare, etc), Volume 3 provides data model patterns.

Finally, while this book is ostensibly about UML and Object Modelling, Peter Coad's Modeling in Color With UML provides an "archetype" driven process of entity modeling starting from the premise that there are 4 core archetypes of any object/data model

Database design standards

This is the book I used a few years ago when I initially learned about database design in school: http://www.amazon.com/Database-System-Concepts-Abraham-Silberschatz/dp/0073523321/ref=sr_1_1?ie=UTF8&s=books&qid=1279133097&sr=1-1

For online research, make sure you look into "3rd normal form" designs for your databases. It allows for the most flexibility and maintenance.

Good Resources for Relational Database Design