On my quest to properly design database schemas, I enjoyed reading Elmasri and Navathe's Fundamentals of Database Systems, which offer pragmatic approaches.

While Ramez Elmasri and Shamkant B. Navathe's book describe formal methods to architect databases, I particularly like the down-to-earth techniques they get to discuss too.

1 Rationale

There are algorithms that allow to build up a database schema decomposing a universal relation in a systematic fashion but they are heavy to apply, do not give unique results and some of these results may be inappropriate. According to a discussion in Elmasri & Navathe's Fundamentals of Database Systems (section 13.1.5, p. 434), a more relevant approach would be to first draw an ER, map it to relations and apply the algorithms on these new relations to check if they need further decomposing.

2 Recipe

I used a similar approach which worked well for me:

1. Define attributes: they should only contain data that you actually need to store, i.e. do not bother with keys or IDs yet if they are not information you actually need to see in the end. These will come naturally later.
2. From a universal relation built up from all these attributes, try to define all functional dependencies.
3. Split up the universal relation to ensure 1NF. You'll get entities. Not all attributes may find a place in your attributes: some will go to relationships. We'll now start drawing a ER diagram.
4. Relate these entities with relationships and define the cardinality. Some of the attributes you defined earlier may naturally find their place in the relationships.
5. Apply the steps depicted in Elmasri & Navathe's Fundamentals of Database Systems, section 6.8.1 p. 172 (ER-to-Relational Mapping Algorithm).
6. Check that your relations are in BCNF. If they don't, review your ER.

I don't think it matters to have multi-attribute keys: it is true they imply redundancy across relations, but you can't avoid them (i.e. replace a multi-attribute key with a single-one substitute) without breaking your DB consistency and normal forms.

3 References

• Ramez Elmasri and Shamkant B. Navathe's Fundamentals of Database Systems (Second Edition), published in 1994 by Addison-Wesley.