Explain pitfalls in relational database design?

Answer 1

One of the tenets of relational database design is "normalization". Normalization is the process of reducing a complex data structure into its simplest, most stable structure. In general, the process entails the removal of redundant attributes, keys, and relationships from a conceptual data model. In database design one has to make decisions about the trade offs offered by normalization. A completely normalized database can offer a great deal less redundancy and offer a infinite complexity and relational flexibility to the user/designer. On the other hand, this can get out of hand. The downside is that queries get overly complex and it can slow the database to a crawl as more and more data is accumulated. A simple example would be that of a database that represents an address book. The simplest design would be a flat database or a single table with columns like name, address, home phone, work phone. This would be a very fast, easily queried database but somewhat limited. What if you have someone who has more than two phone numbers? what if you want to put in their work address or the address of their vacation home. The fully normalized version of this database would have a core table for contacts that would link to a table of addresses, a table of phone numbers, a table of zip codes. There would also be tables called look up or type or category tables that would list the possible categories for phone numbers such as work, home, fax, cell etc.. as well as a table for address categories, work, home, vacation house etc.. you could also add a category table for the relationships between you and the contact and one for the relationships between contacts e.g, 'my friend', 'my boss', 'his sister', 'her uncle'. The Pitfall here is this database would quickly get too complicated for unsophisticated users to query and understand and it would get slow as more and more data was accumulated.

Xequence-- The pitfalls in relational database design is that you must learn the system. This process of 'learning the system' will take some time. Another pitfall is that everything must have referential integrity. Not an easy task to think about. The last pitfall in a relational database design is the length of time it requires to build stored procedures and triggers. These values cannot insert null values into a [does not allow null] field that is relational to a foreign key table with more cannot insert nulls that have constraints if the values goes below 0 to email a man in Nantucket that his gridview errored out on line index 4 with his foreign key constraint. *breathe* It gets better..