Dan,

Over-normalization is what happens when a project is left unfinished. It is better for the stability and maintenance of an application if the database is fully normalized during the design phase. Once the database is normalized, you can then make informed decisions about where to de-normalize to increase reporting speed and simplify the code.

The overriding factor in determining how much normalization is required is the size of the project. If there will be enough data to require a cluster of servers, for instance, more normalization will increase speed by allowing the servers to intelligently divvy up the tasks involved in processing the database.

Of course, if the application will be running on a 10 workstation network with an NT or Novell file server, your best bet would be to de-normalize the data a lot more.

Personally, I don't split up the address and zip/city/state into separate tables. But it's still a good idea to have an address table that is separate from the customer! People move around, and unless you are storing snapshots of invoices (very wasteful of diskspace), you will not be able to reproduce an old invoice with the exact same data.

These are the kind of tradeoffs that happen all the time during design & development. That is why it is so important to fully normalize the database and use that model as the starting point. It is immensely easier to de-normalize a database than to increase the normalization once development has begun. And fully normalizing the database promotes critical thinking about project details that might otherwise be overlooked.

I know this sounds like a ton of work, but it really pays off in the long run. After two or three projects you will be cranking out fully normalized database designs at top speed. In the end, by fully normalizing the database, you are really taking away one of the reasons why projects fail.

- Keith

>Cindy-
>
>I appreciate your input.
>
>These are problems that I'm always dealing and yet I've seen a really good data base design, Normalized to 3rd Normal Form. Everything I've seen either goes overboard with normalization, along with poor design or they flatten everything out, which can be a nightmare in and of itself.
>
>>Dan,
>>
>>Yes, you'd have to go through the InvoiceHeader to get the customer's details, but you'd probably want that information there anyways.
>>
>>Again, it's much simpler to have normalized data and do a little extra typing each time. You'll never have to do cleanup operations when your tables are out of synch and it will be easier to add new features into your database later on.
>>
>>However, if you had a data warehouse with huge amounts of data loaded in batches on a monthly basis, and you often wanted customers and Invoices without the work order stuff, then I'd add the customer ID to the Invoice Header table.
>>
>>
>>
>>>Another point. Some people for reporting purposes like to store IDs into tables to simplify joins at a later time, even though the IDs don't really belong.
>>>
>>>I'm having difficulty coming up with a good example. But let's say work orders had a customer Id and in the Invoice Header you stored the Work order Id. To get at the customer information from the Invoice Header table you would have to join to the Work Order table and then to the Customer table.