I had an epifany this morning that made me want to question my current database setup. I am creating an online registration site in asp.net with a sql server backend.

Basically there is a hierarchy of a registration that has 4 different levels. Within each level there can be 2 different fees: an account fee charged to the account or family, and a child fee charged to the child.

I have it set up as 4 different tables of the hierarchy (e.g. Level 1, Level 2, etc...) and within each level is an account fee table and a child fee table. So 12 tables total.

But what I was wondering today is maybe this only needs to be 3 tables? All 4 of the "level" tables can be 1 table with a column to designate which level it is. A second table could the 'account fee' table with a column to designate which level it belongs to, and a third table 'child fee' with a column to designate which level it goes to.

Taking it a step further, I probably only need one fee table, with a column to designate if its a child fee or account fee and a column to designate with level it belongs to.

I know it's nearly impossible to understand my whole situation, but maybe someone has had a similar experience with this issue? For my situation, I thought at one time I considered this and I had a reason not to set it up this way.

TIA for any thoughts or advice.

Answers -

Too normalized?  Most decidedly yes - depends on what you are doing.

I once worked on a project for an OLTP system that had a 3N form model.  To simply fetch information about orders to invoices was a six table join! 

 

What you described above, again depending on the nature of what you intend to use it for, I would 9 times out of 10 consider to function as a 'flat' lookup table (ie. I'd do it all in one table)

 

Regards

randyvol

I wouldn't have thought it was the fault of the normalisation process itself. Usually, when I hit this sort of problem, I tear the whole data analysis away and start again, because it is a sign I haven't understood the way that the business is really working with its data and processes. The only time I've ever de-normalised, I've bitterly regretted it later on, when the database gets more complex, or the requirements change.

Randyvol, I really understand your point and agree to an extent, but I'd argue that a six-table join sometimes performs as well as, or sometimes better than, a clumsier join based on just two tables. I've just been reading Joe Celko's excellent http://www.dbazine.com/ofinterest/oi-articles/celko22  (One True Lookup Table) which goes into a lot more detail about the drawbacks of a 'flat' lookup table. One can get it to work well but it isn't a general panacea.

Thanks you both for your thoughts. I think what I am still going through is I am relying too much on my data structure to have rules. Meaning I am creating the tables with the thought in mind that only a certain subset of data can fit in to it.

It's almost like saying "only people from one state or region can go in this table", where I should have one table with a "region/state" column to designate where they belong. Certainly my situation is not that simple; I think it just requires more thought on my part. It seems the more I plan the more I realize that I simply need to begin to create the database as situations arise that I never would have thought of and correct them as they come up.

Hi Phil -

Without re-starting the holy war over normalization, I guess it really only matters to me from two perspectives; first and foremost - does it perform? If so, does it require joining so many tables (I've seen 64-table joins before - what fun that must have been to figure out) that it becomes a royal pain?

Those are the two that interest me.  The rest to me at lest, are just the opinions of the warring camps.