473,699 Members | 2,464 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Normalization connundrum

I've come up with this issue in several apps now. There are things that, from
one perspective, are all handled the same, so it would be desirable that they
all be handled in the same table with some field as a type specification.
From other perspective of foreign key relationships, however, they are
different things and can't be stored in the same table.

For example, I have a scheme for indicating mappings between dimension records
at one time period to new dimension records at another time period. I could
use one set of tables for all mappings since they all work exactly the same
way, but then I can't set up DRI between the mapping tables and the dimension
tables. If I just make separate mapping tables for each dimension table, then
I'm creating 4 new tables per dimension table, all identical with respect to
what fields they contain, what kinds of unique constraints they have, and what
relationships they have to each other with the sole distinction that they each
map to the integer-type key of a different dimension table. I would not look
forward to doing maintenance on this schema!

Is there any strategy for having the cake and eating it, too?
Jul 20 '05 #1
7 1651
Steve Jorgensen wrote:
I've come up with this issue in several apps now. There are things that, from
one perspective, are all handled the same, so it would be desirable that they
all be handled in the same table with some field as a type specification.
From other perspective of foreign key relationships, however, they are
different things and can't be stored in the same table.

For example, I have a scheme for indicating mappings between dimension records
at one time period to new dimension records at another time period. I could
use one set of tables for all mappings since they all work exactly the same
way, but then I can't set up DRI between the mapping tables and the dimension
tables. If I just make separate mapping tables for each dimension table, then
I'm creating 4 new tables per dimension table, all identical with respect to
what fields they contain, what kinds of unique constraints they have, and what
relationships they have to each other with the sole distinction that they each
map to the integer-type key of a different dimension table. I would not look
forward to doing maintenance on this schema!

Is there any strategy for having the cake and eating it, too?


That things are handled the same way is not a criterion for putting them
into a single table. Let me refer you to Date and Cobb.

A table is a set. If the data constitutes a set as defined by set theory
it belongs in one table. If not ... it doesn't. The sole exceptions
being non-relational models such as star and object schemas.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #2
On Mon, 05 Jan 2004 17:42:22 -0800, Daniel Morgan <da******@x.was hington.edu>
wrote:
Steve Jorgensen wrote:
I've come up with this issue in several apps now. There are things that, from
one perspective, are all handled the same, so it would be desirable that they
all be handled in the same table with some field as a type specification.
From other perspective of foreign key relationships, however, they are
different things and can't be stored in the same table.

For example, I have a scheme for indicating mappings between dimension records
at one time period to new dimension records at another time period. I could
use one set of tables for all mappings since they all work exactly the same
way, but then I can't set up DRI between the mapping tables and the dimension
tables. If I just make separate mapping tables for each dimension table, then
I'm creating 4 new tables per dimension table, all identical with respect to
what fields they contain, what kinds of unique constraints they have, and what
relationships they have to each other with the sole distinction that they each
map to the integer-type key of a different dimension table. I would not look
forward to doing maintenance on this schema!

Is there any strategy for having the cake and eating it, too?


That things are handled the same way is not a criterion for putting them
into a single table. Let me refer you to Date and Cobb.

A table is a set. If the data constitutes a set as defined by set theory
it belongs in one table. If not ... it doesn't. The sole exceptions
being non-relational models such as star and object schemas.


Then, the correct wording of the question might refer to avoiding schema
duplication (leading to difficulty of maintenance) rather than normalization.
Perhaps, the best answer in this case would not be the best normalized answer.
Jul 20 '05 #3
Steve Jorgensen wrote:
Then, the correct wording of the question might refer to avoiding schema
duplication (leading to difficulty of maintenance) rather than normalization.
Perhaps, the best answer in this case would not be the best normalized answer.


Not unless there is some compelling case for denormalization . And I
don't see one in what was posted.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #4
On Mon, 05 Jan 2004 19:38:41 -0800, Daniel Morgan <da******@x.was hington.edu>
wrote:
Steve Jorgensen wrote:
Then, the correct wording of the question might refer to avoiding schema
duplication (leading to difficulty of maintenance) rather than normalization.
Perhaps, the best answer in this case would not be the best normalized answer.


Not unless there is some compelling case for denormalization . And I
don't see one in what was posted.


The compelling aspects as I see them are ...

1. It is simply a good practice to eliminate duplication in code or in a data
model. Any intentional duplication is a source of potential trouble, since
it's possible to make subtle mistakes when attempting to change that shared,
repeated code.

2. Having the number of supporting tables multiplied by the number of
dimension tables makes for a vastly more complex ER diagram that someone will
have to decipher in the future.

3. I wouldn't have to resort to dynamic table names in code or writing a code
generator to avoid duplicating code as well. This is especially an issue if I
am I use stored procedures to update the data in these tables.

Perhaps, you don't think these are compelling, and that's fine. I find them
compelling, though, and I'm still hoping for some advice on how to accomplish
it.

I had one idea in which I can use an intervening table to translate between
the general and specific cases. Basically, I would have a generic dimension
table with a type code, then a specific dimension table (say category) that
also has a dimension ID and a type code and a 1-1 relationship to the generic
dimension on both fields. The specific table, then has a constraint that only
allows the applicable dimension type code as a valid value for the type code
field.

Any opinions on this? Other ideas?
Jul 20 '05 #5
Steve Jorgensen wrote:
Perhaps, you don't think these are compelling, and that's fine. I find them
compelling, though, and I'm still hoping for some advice on how to accomplish
it.
You are correct. I'd suggest you ask Mr. Celko for his opinion. ;-)
I had one idea in which I can use an intervening table to translate between
the general and specific cases. Basically, I would have a generic dimension
table with a type code, then a specific dimension table (say category) that
also has a dimension ID and a type code and a 1-1 relationship to the generic
dimension on both fields. The specific table, then has a constraint that only
allows the applicable dimension type code as a valid value for the type code
field.

Any opinions on this? Other ideas?


The fact that you don't know how to implement this and are talking about
a translation table, at least to me, would be warning enough you are
heading down the wrong road.

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.wash ington.edu
(replace 'x' with a 'u' to reply)

Jul 20 '05 #6
On Mon, 05 Jan 2004 22:06:55 -0800, Daniel Morgan <da******@x.was hington.edu>
wrote:
Steve Jorgensen wrote:
Perhaps, you don't think these are compelling, and that's fine. I find them
compelling, though, and I'm still hoping for some advice on how to accomplish
it.


You are correct. I'd suggest you ask Mr. Celko for his opinion. ;-)
I had one idea in which I can use an intervening table to translate between
the general and specific cases. Basically, I would have a generic dimension
table with a type code, then a specific dimension table (say category) that
also has a dimension ID and a type code and a 1-1 relationship to the generic
dimension on both fields. The specific table, then has a constraint that only
allows the applicable dimension type code as a valid value for the type code
field.

Any opinions on this? Other ideas?


The fact that you don't know how to implement this and are talking about
a translation table, at least to me, would be warning enough you are
heading down the wrong road.


I'm not sure what you're saying. I know how to implement what I just
described. As you say, perhaps it's not the "right" approach, and certainly
more complicated than what I'd prefer, but in the absence of any other
suggestions, it's the only one I've come up with besides either implementing
the massive duplication or forgoing the use of DRI.

Also, I'm not sure what seems so obviously wrong to you about the translation
table idea (though I would welcome an explanation). It's defintitely a
kludge, but on the surface, it doesn't look too terrible to me as kludges go.
Jul 20 '05 #7
Steve Jorgensen (no****@nospam. nospam) writes:
I've come up with this issue in several apps now. There are things
that, from one perspective, are all handled the same, so it would be
desirable that they all be handled in the same table with some field as
a type specification. From other perspective of foreign key
relationships, however, they are different things and can't be stored in
the same table.

For example, I have a scheme for indicating mappings between dimension
records at one time period to new dimension records at another time
period. I could use one set of tables for all mappings since they all
work exactly the same way, but then I can't set up DRI between the
mapping tables and the dimension tables. If I just make separate
mapping tables for each dimension table, then I'm creating 4 new tables
per dimension table, all identical with respect to what fields they
contain, what kinds of unique constraints they have, and what
relationships they have to each other with the sole distinction that
they each map to the integer-type key of a different dimension table. I
would not look forward to doing maintenance on this schema!


I'm not at all into data warehousing, so I don't know what dimension tables
are, but it sounds to me that you have a bunch of table which are like:

CREATE TABLE entity(entityid int NOT NULL PRIMARY KEY,
entityname varchar(30) NOT NULL)

and you want to collapse them into one. Yes, I have seen this. And I
killed it when I took over that data model. You get less tables, but
you also get less flexibility. In the system I work with, we may have
over fifty such tables. But not all are equal. Suddenly there is a need
for an extra column in such a table, one is specific for the entity in
question. Not very fun if you have all in one table.

Two other drawbacks with this arrangement is when you look at a graphical
data model, you can only see that a table refers to the general entity
table, but not for which entity. And each reference requires two columns
in the referring table, whereof one is a constant column. I rather have
extra tables than superfluous tables.

Now, judging the previous thread, you seem to have a special problem
with mappings that are in the same domain, but different at different
points in time. These mappings might be worthwhile to lump into one
table, four by four so to speak. Actually, we have such an arrangement
somewhere in our data model for one specific entity. But it would be
dangerous to lump everything into this basket.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
1700
by: Adrian | last post by:
Normalization Question - Please bear with me, hopefully things will be clear at the end of the question. Given a treaty table containg treaties; Treaty1, Treaty2 etc and a benefit table; Benefit1, Benefit2 etc. A treaty can only have certain benefits:- For example Treaty 1 can process Benefit1 and Benefit2. To maintain this relationship a new table TreatyBenefit has been
3
3172
by: JoeB | last post by:
I found an interesting thread on this from Nov., 2000, but it didn't fully answer my question. I understand normalization, but am trying to find the line between good database design and personal preference. I'm wondering if I've created too many tables. Normalization as I understand it would be to create tables if a field can have more than one occurrance (for example, book titles by an author). But if a field will only have one value,...
4
1788
by: Catherine Jo Morgan | last post by:
I'm wondering if I'm trying to normalize data way beyond what will prove practical. For example, if I put People, Addreses, and Comms in 3 different tables, with junction tables for People/Addresses and People/Comms, then will the data entry form for new contacts have to be in form, subform, subsub form and possibly more subs? If my table structure is split so much that a common data entry form has fields bound to 8 or 10 different...
2
1479
by: Kostas | last post by:
Hi all, I strived understanding the concepts of all normal forms up to the domain-key normal form but there are some issues I still am unsure of, and I will describe the first one in this post. (it looks so simple that there has to be an answer) Suppose a relation that holds information about Phone Numbers: PhoneNumber (PhoneID, ActualNumber, PhoneType, Comments)
7
2455
by: John Welch | last post by:
I have three tables and the way they are currently set up violates good normalization, but I'm having trouble figuring out a better way. Can someone suggest a more elegant solution? My tables are: tblCompanies: CompanyID - PK other fields like address, type of business, etc
20
6920
by: hippomedon | last post by:
Hello everyone, I'm looking for some advice on whether I should break the normalization rule. Normally, I would not consider it, but this seems to be a special case. I have created an "Outcomes Database" used to store response data from measures/ questionnaires for a longitudinal health study. It is essentially derived from Duane Hookom's Survey Database (thanks Duane!!!), with many modifications added to fit the needs of my lab.
36
2938
by: Shraddha | last post by:
What is mean by normalization of pointers...Where we use that?
1
2023
by: weird0 | last post by:
I know that is not the appropriate group for asking this question, but I do not know of any better forum. Can anyone plz explain clearly what normalization is? The first three normal forms upto 3NF and BCNF. I know the first one but the remaining ones are really confusing. The language changes dramatically into functional dependencies x---y, all that, and normalization, the real topic is lost in between. Decent explanation or good...
22
2449
by: sophia | last post by:
Dear all, can any one explain what is meant by pointer normalization given here:- http://c-faq.com/ansi/norml.html
0
8700
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9184
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9048
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8931
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8892
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6539
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
3064
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2014
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.