This is really a basic design normalization theory inquiry. I'm
interested in opinions here. Here is the scenario I'm working with. To
support a public website we have announcements which can appear on the
home page and 4-5 subcategory pages for which there are links to the
home page. The number of sub-categories never changes except during
major business model changes. The announcements themselves singular
attributes like Content, start and stop dates. Also, the announcements
have associations with the 4-5 subcategories and website placement
options which include home page placement, subcategory placement,
download page or no placement.

Announcement
------------
AnnouncementID (PK)
Start
Stop
Text
etc.

The question here is whether it makes sense to consider announcement
placement as a separate entity from the announcement.

AnnouncementPlacement
---------------------
AnnouncementPlacementID (PK)
AnouncmentID (FK)
HomePagePlacement (True/False)
SubPagePlacement (True/False)
DownloadPagePlacement (True/False)


Or should we consider placement as an attribute with an associative
table called announcement placement.

AnnouncementPlacement Placement
--------------------- ---------
AnnouncementID(FK) PlacementID
PlacementID (FK) Placement(Home/Sub/Download)

Does the consideration of the three placement attributes in the entity
Announcment placement absolutely violate 1NF by constituting a
repeating group? Or can you semantically consider these to be 3 valid
separate attributes of the entity announcement placement. In practice,
placement changes could be managed with updates to one record if we
think of announcement placement as an entity whereas with the
alternative we need to look at modifying more than one record at times
when making placement changes. Is this really just a denormalization
that makes sense? Or is it ok to consider Announcement Placement an
entity valid within the rules of normalization?

A colleague of mine thinks that consideration of Announcement
placement as an entity is a denormalization and that strict
normalization principals dictate an associative relationship between
Announcement and Placement where the placement of an announcement is
defined by multiple records. What is your opinion? Does normalization
theory mandate consideration of two or three similar attributes like
home page, sub page and download page placement as a repeating group
and demand creation of Placement as an entity and Announcement
Placement as an association or is there room in the rules to consider
Announcement Placement a valid normalized entity with attributes like
home placement and subcategory placement? Or would you agree with me
that this is a grey area in which both considerations are ok and
within the rules of normalization?

On 25 Sep 2003 09:43:35 -0700, mlkearns (AT) yahoo (DOT) com (Matt K) wrote:

Think about this ... every time you add a new page, you must add a
column to keep up. Not good IMHO... even if you say this happens only
seldomly. Chances are it will happen much more often than that, and it
is a nightmare to find all the places where something is placed.

Actually, yes ... What does "PlacementID" represent? Typically, one
would have an Announcement table, a Location table, and a resolving
table with the IDs from both tables comprising a joint candidate or
primary key and having a foreign key constraint referencing the main
tables. IOW, your typical many-to-many relationship.

Think about the SQL statement you would need to issue a report
counting the occurrences of each announcement over the various
locations. Then you'll see what I mean.


--
Bob Hairgrove
rhairgroveNoSpam (AT) Pleasebigfoot (DOT) com