Performance Tweak or Bad Design?

[ions]

I had a meeting with a developer today and we analyzed his table structure.

He was duplicating certain fields in the child table for "performance".

For example the parent (header table) had a Customer Name field and he would copy this Customer Name into every child (detail) record so he wouldn't have to look it up using a join?

Isn't this bad design?

Thanks for your opinion.

 

[georgedwilkinson]

Yeah, it is bad design. It shows that the human race has not evolved enough to be able to anticipate the consequences of their actions.

 

[genesis]

simply answered by georgewilkinson

 

[namliam]

Yeah, it is bad design.

Unless you have very serious perfermance issues that cannot be circumvented another way... Yes Thirded, bad design in any database.

 

[Galaxiom]

namliam I suggest that you are being too polite. It is hideous design.

I would suggest finding another developer. The mistake he is making is Kindergarten level Access. If he misunderstands joins so badly his work will be littered with wrong thinking.

 

[namliam]

namliam I suggest that you are being too polite. It is hideous design.

Personally I had to do something quite simular, where adding in some information made huge performance gains. This was related to a very slow and overcrowded network, and overworked database.

Generaly though its bad, unless like I said it fixes huge problems. Though customer name or something is a bad (or even worse) example. Waterfalling Foreign keys down a long but commonly used tree is quite common in bigger CRM and therefor in 'bigger' databases like Oracle or SQL Server.

Boyce-Codd Normal Form describes this and is commonly refered to as the 4th normal form. It introduces some redundency to aid in query performance.

 

[Atomic Shrimp]

I worked for/with a developer who insisted on doing it this way - in fact, nearly all of the tables had the same structure, including every concievable field (i.e. full address fields and product attribute fields in every invoice line, etc).

In spite of it being done for reasons of performance*, it actually ended up making things slower - because things inevitably got out of sync when a customer address or product attribute was changed - so nearly any process that wrote data ended up calling a 'refresh' function that chugged all the way through every table checking consistency.

Because the development environment only contained a small amount of example data, all the refreshing and consistency checking never seemed like a problem to the developer, nor to the customers until they had accumulated a few months' worth of data, and everything started to chug. The slowdown was exponential over time, leading to even more inappropriate decisions about splitting tables so that each month of sales data went into its own table.

*It does make the design process a bit easier if you only ever need to throw one table into your report generator etc, and never have to worry about joins and their implications, but this benefit is far outweighed by the problems it will inevitably cause.

 

[Galaxiom]

This was related to a very slow and overcrowded network, and overworked database.

I understand this justification but the real problem should have been addressed. Unfortunately it is incredibly hard to get accross to some that their network performance is the problem. The extent of ignorance and self delusion in some empowered decision makers never ceases to amaze me. You do have to get the thing performing despite the unlikely design or they will never tire of telling everyone of your incompetence.

Though customer name or something is a bad (or even worse) example.

I cannot think of a worse case for cascading a field. That is where I was coming from.

Maybe it is really a situation where this developer is simply reflecting the context of his experience. However given that he sounds like the same one in this other post if I would still find someone else unless that case is not as straightforward as it seemed.
http://www.access-programmers.co.uk/forums/showthread.php?t=182610

 

[Galaxiom]

I worked for/with a developer who insisted on doing it this way - in fact, nearly all of the tables had the same structure, including every concievable field (i.e. full address fields and product attribute fields in every invoice line, etc)

Sounds like a case for that projectile vomit smile I suggested a while back.
O>>>><<<<O

 

[namliam]

I understand this justification but the real problem should have been addressed. Unfortunately it is incredibly hard to get accross to some that their network performance is the problem. The extent of ignorance and self delusion in some empowered decision makers never ceases to amaze me. You do have to get the thing performing despite the unlikely design or they will never tire of telling everyone of your incompetence.

Preaching to the quire (or how ever you spell it/say it), totaly agree.
I tried forcing the network upgrades, opening a simple word doc at times took > 10 secs which no one seemed to care about...

Yet it is my database that is not performing, sure ...

I cannot think of a worse case for cascading a field. That is where I was coming from.

Maybe it is really a situation where this developer is simply reflecting the context of his experience. However given that he sounds like the same one in this other post if I would still find someone else unless that case is not as straightforward as it seemed.
http://www.access-programmers.co.uk/forums/showthread.php?t=182610

Meh... I have seen much worse things than that... whats worse I am forced to work on a daily basis with worse things than that...
I.e.
Columnnames like GHYTMGHDT and GHYTMGHTM, which would be "CreateDate" and "CreateTime"
As well as HUGTHGYSNM, ASGYBLTYNM, BGUASTORNM, all for customer name accross 3 different tables for shipping, billing and contact adress.

this is a commercial system that was bought for quite some considerable money!

** Disclamer ** Not actual field names but they are quite simular I can promisse you.

 

[Galaxiom]

** Disclamer ** Not actual field names but they are quite simular I can promisse you.

Changed to protect the guilty no doubt.

 

[namliam]

Changed to protect the guilty no doubt.

well one guess as to one of my "stok paardjes" (pet peeves?)

USE PROPER DENOMONATING COLUMN / CONTROL NAMES

Along side with

USE A NAMING CONVENTION, and stick to it

Another system (also big $$$) has things like customer_Number, CustomerNR, CustomerID, Customer_ID_Number, IDCustomer, Customer__Number
Also Name_Last, SurName...

It is very easy to spot when different people have been working on the project It makes creating queries on systems like this like walking a mine field

Edit:
Yes I hate my job sometimes

 

[Banana]

I've worked on Oracle databases where they use data warehousing structure... star schema or snowflake schema where there's usually one big fat table with few dimensions table and does have several repeating columns, columns with calculated data, along other things. It was a bear to work with and made reporting more painful than necessary because there were always that degree of uncertainty. Mind, it's easy to build a report in query builder because it's only few tables and there's no complex joins involved but as soon as they want to aggregate or group the data... stored procedures galore.

(Not to knock SPs here but it was a solution for a problem that shouldn't be there to begin with)

 

[RossWindows]

Columnnames like GHYTMGHDT and GHYTMGHTM, which would be "CreateDate" and "CreateTime"
As well as HUGTHGYSNM, ASGYBLTYNM, BGUASTORNM, all for customer name accross 3 different tables for shipping, billing and contact adress.

this is a commercial system that was bought for quite some considerable money!

** Disclamer ** Not actual field names but they are quite simular I can promisse you.

Does that system run on iSeries by chance?

 

[ASherbuck]

Yeah, it is bad design. It shows that the human race has not evolved enough to be able to anticipate the consequences of their actions.

I needed that this morning, thank you George.