Re: Normalization - am I losing the plot?

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: 8 Apr 2004 00:50:10 -0700
Message-ID: <cf15dee4.0404072350.7a182714@posting.google.com>

"Colin Basterfield" <colin.basterfield_at_xtra.co.nz> wrote in message news:<iqocc.6537$d%6.115028_at_news.xtra.co.nz>...
> Hi,
>
> I'm not going to state the table layouts because I'm not exactly sure what
> form they'll take yet, however I have come across a dilemma in my thinking,
> so wondered what people thought.
>
> I have a number of sites, site 1, site 2 etc
>
> I have a template which consists of a number of template items, which
> represents counts,
>
> e.g. apples, bananas, oranges, pears, lemons
>
> When the Site manager configures their site, items from the template list
> can be added appropriately
>
> e.g.
>
> Site 1 :- apples, oranges, pears, lemons
>
> Site 2 :- bananas, oranges, lemons
>
> At the end of each day the Site manager, or one of their trusted operatives
> will enter the count for each piece of fruit sold for a particular date on
> their site.
>
> e.g. site 1 - 4/4/04 : apples - 56, oranges - 7, pears - 0, lemons - 27
>
> The way I figured is a Site table, and a template item table. A joining
> table could be used to hold the selections for each site, in a many to one
> to many relationship.
>
> A further table would exist which holds the unique id from the site-template
> joining table, a date and a count.
>
> Is this logic correct???
>
> TIA
> Colin B

Colin,

Let's start with a logical model before rushing into a physical model.

My understanding of what you have posted is: (1) There are three (3) entities:

    (A) Type of fruit to be sold (Concrete entity)
    (B) Site where fruit is sold (Concrete entity)
    (C) Selling day (Abstract entity)

(2) There are two (2) business rules:

(A) The types of fruit to be sold at a site are determined by the manager

of that site;
(B) Each selling day, the total number of each type of fruit is recorded by

the manager or their delegate.

From these, I deduce the following relationships and the cardinality:

(1) A site sells one or more types of fruit;
(2) A type of fruit can be sold by zero or more sites;
(3) A selling day has zero or more types of fruit sold by a site;
(4) A type of fruit sold by a site can be sold on zero or more selling

days.

You will note that relationships (1) and (2) form a pair and (3) and (4) form another pair. This is done deliberately to avoid confusion about cardinality on a many-to-many relationship. Also, it is good practice to ennunicate the relationship.

You also note that I have converted the fruit-site relationship into an entity ("type of fruit sold by a site"). This is done to reduce the complexity of a ternary many-to-many relationship.

I have omitted the step of attribute discovery because the example is trivial.

The first draft of the logical design is: (1) Type_of_fruit table with one (1) column: fruit_name. Fruit_name is the

primary key.
(2) Site table with at least one (1) column: site_id. The manager_id could be

an optional column. Site_id is the primary key. (3) Selling_day table with one (1) column: date_of_sale. Date_of_sale is the

primary key.
(4) Type_of_fruit_sold_by_site with two (2) columns: site_id and fruit_name.

Both of these columns comprise the primary key. There are two (2) foreign

key relationships with the type_of_fruit and site tables. (5) Type_of_fruit_sold_by_site_each_day with four (4) columns: site_id,

fruit_name, date_of_sale, and number_of_fruit_sold. The first three (3)

columns comprise the primary key. There are two (2) foreign keys: ( fruit_name, site_id) and date_of_sale.

Based on the data you have provided, the last table would be populated as follows:
site date_of_sale fruit_name number_of_fruit_sold

---- ------------ ---------- --------------------
   1       4/4/04 apples                       56
   1       4/4/04 oranges                       7
   1       4/4/04 pears                         0
   1       4/4/04 lemons                       27

And the type_of_fruit_sold_by_site table would be populated as follows:
site fruit_name
---- ----------

   1 apples
   1 oranges
   1 pears
   1 lemons
   2 bananas
   2 oranges
   2 lemons

My logical design is different from yours in that I have converted your columns (appled, oranges, pears, etc.) into values for the fruit_name column. In effect, I have gone to a more abstract level. This design is more flexible because fruits can be added or deleted without modifying the structure of the tables. All sites can use the same table structure while enforcing the business rules.

Douglas Hawthorne Received on Thu Apr 08 2004 - 02:50:10 CDT