Entity Relationship Diagram

[justmehere]

I have one more question about ERD. The ERD I am working on is the one using the symbols SQUARE = Entity, DIAMOND = Relationship and Oval = Attributes...etc.

I wish to know do you have to design this diagram with normalization in mind or can you simply draw the schema using broad or generally named entities without thinking about data redundancy ? Or how else should it be approached ? Thank you.

[skhanal]

There is no rule, you follow whatever works best. I would create it with normalization in mind, when you finally implement it physically you break the normalization rules for performance reasons (that is data redundancy to avoid complex joins)

[justmehere]

What can I say....you guys ROCK

[stecal]

What does "whatever works best" mean? If you mean:

Design to perform; don't tune to perform
Don't use generic data models
Design your data model for efficiency
Define your performance goals from the start
(Effective Oracle by Design)

Then yes, that works best.

I would create it with normalization in mind, when you finally implement it physically you break the normalization rules for performance reasons (that is data redundancy to avoid complex joins)

Denormalizing is more of the exception rather than the rule, but the final answer is: it depends. For sure, based on what you said, you do not denormalized every table; some, maybe, but it is not a blanket process.

"You should consider denormalizing data only when you confront a performance problem. You should almost certainly look at alternative SQL expressions and alternative physical structures first, as these solutions don't have the deleterious trade-offs of denormalization." (Database Design for Smarties)

"Therefore, you will occasionally be expected to denormalize some portions of a database design in order to meet performance requirements. However, the price you pay for increased performance through denormalization is a larger amount of data redundancy."(Database Systems: Design, Implementation, & Management)

[skhanal]

Exactly. There is no one answer that fits everyone. I do not want anyone to go by the books and normalize to extreme, it looks good academically but in practice, joining more than 5 tables is a pain.

[stecal]

What makes 5 as the pain qualifier? That's relative to the developer. Maybe for some people three is too many. Or, ten is no problem. As far as academically is concerned, virtually every book that covers the normal forms points out that 4 and 5NF are rare occurrences, and that 3NF or Boyce-Codd Normal Form (BCNF) is generally desirable.

[skhanal]

It is generally desirable, not always is my point. Even 3NF may not be used in certain situation.

5 is the number I picked, it's not an absolute rule just a rule of thumb.