Database Normalization 8

[TimBiesiek]

Hi All,

Am currently creating a new DB. At the mo there are about 27 odd tables, not including the archive tables, and log tables we plan to have, so it is quite a large DB.

I want to normalize the tables in this DB, but a workmate is saying we should have a certain field that relates to the owner of the data in every table, and sub table. I don't see this as neccessary, as this would de-normalize the tables, and the owner can be gained by going back through the table joins anyway.

E.g.

Companies Table - This shows info about the companies
Clients table - Clients are 'owned' by companies
Properties table - Properties are supplied by clients
Meters table - Meters belong to properties

Workmate thinks that the company ID should exist in the Clients table, Properties table and Meters table. However, the property the meter belongs to can be found, as can the client the property is supplied by, as can the company the client is 'owned' by...

Thoughts on this? Which would be the best way to go?

 

[lespaul]

Then I would make sure I had some documentation signed by someone high in the organization indicating that your recommendations for normalization were overruled and that any delays or issues evolving from the denormalization are not your responsibility.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[TimBiesiek]

Hmmm, yes. Good idea thanks Leslie! Then they can't pin it back on me when things turn to custard!

 

[Darrylles]

Tim,

You mentioned the fact that you thought that a de-normalized database was a database without design that had been normalized.

Now you twist the English language tiger, and I'm not sure if you are taking the p. You obviously know full well that there is no English word beginning with 'de-'.

But, no, if we can twist the English language in such a way, then de-normalize means to take a normalized table and add fields to it that make it not conform to normalization rules (doesn't it?). Tim, can you go to another forum please?

ATB

Darryle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[TimBiesiek]

Hi Darryles,

Nope, what I meant was that what LesPaul was describing sounded like an undesigned database, that hadn't been normalized at all... E.g. a 1 table (Manager!) database, with all fields in the single table...

Denormalization (Man, there goes that English language again!) is exactly what you describe. To denormalize a table, fields are added to it, that effectively mean you don't need joins to other tables... E.g. If I had a table that held orders, and the orders had a company, and the company had a user, then adding the user key to the orders table would be denormalizingm, as it would then reduce the need for the link between the orders-users tables. Thus, queries could then be reduced to simply look up by a user ID...

Confused? Good.... I am too...

 

[Darrylles]

Tim,

My apologies, you were referring to Les's link. I am in the UK (05:30), and I need my bed. I therefore do not have the wherewithal nor desire to follow links.

You get a star for pointing out my obvious error, and for also enabling me to escape very quickly to my pillow.

(Is this the first EVER star to a thread-starter?)

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[TimBiesiek]

Lol! No worries! Apology accepted! Heck, 05:30?!?! Don't you have work tomorrow? Or today, as the case maybe!

Goto bed! Before I set my PC onto you!

 

[jsteph]

It seems that most people want to 'break' away from correct methodology just for the sake of not conforming...
Why on earth duplicate information, that can be reached via a single correctly normalized table?

I think MDXer had the most appropriate answer. It seems the argument was really about apples & oranges. If you have a data warehouse, which is what alvechurchdata's 'muppets' would be using (assuming the enterprise had the resources to build one).

So, as stated by MDXer, an olap db is denormalized by design.

A data-warehouse, for example, is separate tables and it's loaded/refreshed on a timed interval--daily usually, since most trend reporting I've ever seen doesn't care about the current day's outstanding orders, for instance.

Yet if the enterprise does not have the resources to have a separate olap system--then in my opinion it's not necessarily a bad idea to put, say, the Customer's address in say, the Orders table. This is a case where you put on your analyst hat and decided if it's worth it. How much time and resources does it take to teach the 'muppets' how to join that table every time they need it? Is a View feasible instead?

So, a counterpart statment to "It seems that most people want to 'break' away from correct methodology just for the sake of not conforming" would be "just because you can, doens't mean you should". Meaning, sure normalization is a noble goal, but in the real world, it's not always the best solution for all the people all the time.
--Jim