Supplier planogram structure

[Tim Johnson]

I am correcting a database for a supplier.

I have normalized the great majority of the data, and am having a difficult
time thinking the next step through:

Initially, the supplier had a table for Products, another table with a
one-to-one relationship to the products specs, another one-to-one for
distributors, in which each product had a record and each supplier had a
column.

I have corrected this to the point where there is one product table, it
contains the specs, there is one distributors table that contains one record
for each distributor and a column for that specific distributor's
information, and a join table creating a many to many relationship between
the two. This seems to me to be the best way to normalize the data
(although, suggestions on this are welcome).

However, in addition to this, the supplier would like a structure set up for
plan-o-grams (the plan for where each item is placed on shelve in a store).
The trick is this, multiple items can be placed into multiple plan-o-grams.
One plan-o-gram can belong to mulitple distributors, and one distributor may
rely on mulitple plan-o-grams depending on their space availability. My
initial thoughts were to set up a series of tables:

tblProducts
index = ItemNo;

joined in a many to one relationship with

tblPOG
index = tblPOGType & ItemNo;

Joined in a many to one relationship with

tblPOGSet
index = POGSet & POGType

Joined in a many to one relationship with

tblDistributors

It seems to go against the whole purpose of relational databases, though,
the more I think about it, as I'm not sure how to create these relationships
without:
a.) Creating these as a separate entity from the Products to JoinTable to
Distributor (many to many) relationship; creating an all new normalization
issue or;
b.) Including this set somehow into the joined relationship, forcing to the
supplier to select a plan-o-gram anytime that he would like to view which
items are associated with which distributor.

Any ideas?

 

[Bob Quintal]

I am correcting a database for a supplier.

I have normalized the great majority of the data, and am having a
difficult time thinking the next step through:
[snip]

However, in addition to this, the supplier would like a structure
set up for plan-o-grams (the plan for where each item is placed on
shelve in a store). The trick is this, multiple items can be
placed into multiple plan-o-grams. One plan-o-gram can belong to
mulitple distributors, and one distributor may rely on mulitple
plan-o-grams depending on their space availability. My initial
thoughts were to set up a series of tables:

tblProducts
index = ItemNo;

joined in a many to one relationship with

tblPOG
index = tblPOGType & ItemNo;

Joined in a many to one relationship with

tblPOGSet
index = POGSet & POGType

Joined in a many to one relationship with

tblDistributors

It seems to go against the whole purpose of relational databases,
though, the more I think about it, as I'm not sure how to create
these relationships without:
a.) Creating these as a separate entity from the Products to
JoinTable to Distributor (many to many) relationship; creating an
all new normalization issue or;
b.) Including this set somehow into the joined relationship,
forcing to the supplier to select a plan-o-gram anytime that he
would like to view which items are associated with which
distributor.

Any ideas?

In 25 years of database design, I've never seen a real world example
that requires Fifth Form Normalization, but I think that this does.

see http://en.wikipedia.org/wiki/Fifth_normal_form

 

[Tim Johnson]

Thanks for the reference, Bob, and for your rapid response (as well as
reassuring...this one had me puzzling for hours, and it's good to know it's
your first time to see something like this, too) . Perhaps it's because I've
been crossing my eyes over this for far too long, or perhaps it's because I'm
not at the mental aptitude of the author or yourself, but I'm not quite
grogging the example here.

It does make it appear that my solution is not quite right, nor normalized.
Is this essentially the creation of a 3 field dependent join table?

I am correcting a database for a supplier.

I have normalized the great majority of the data, and am having a
difficult time thinking the next step through:
[snip]

However, in addition to this, the supplier would like a structure
set up for plan-o-grams (the plan for where each item is placed on
shelve in a store). The trick is this, multiple items can be
placed into multiple plan-o-grams. One plan-o-gram can belong to
mulitple distributors, and one distributor may rely on mulitple
plan-o-grams depending on their space availability. My initial
thoughts were to set up a series of tables:

tblProducts
index = ItemNo;

joined in a many to one relationship with

tblPOG
index = tblPOGType & ItemNo;

Joined in a many to one relationship with

tblPOGSet
index = POGSet & POGType

Joined in a many to one relationship with

tblDistributors

It seems to go against the whole purpose of relational databases,
though, the more I think about it, as I'm not sure how to create
these relationships without:
a.) Creating these as a separate entity from the Products to
JoinTable to Distributor (many to many) relationship; creating an
all new normalization issue or;
b.) Including this set somehow into the joined relationship,
forcing to the supplier to select a plan-o-gram anytime that he
would like to view which items are associated with which
distributor.

Any ideas?

In 25 years of database design, I've never seen a real world example
that requires Fifth Form Normalization, but I think that this does.

see http://en.wikipedia.org/wiki/Fifth_normal_form

 

[Claudia]

bonjour,
sil -vous plais comment je peut faire pour faire traduire en langue
française , merci de me donner une réponse , claudia;

"Tim Johnson" <[email protected]> a écrit dans le message
de groupe de discussion :
(e-mail address removed)...

 

[Bob Quintal]

Thanks for the reference, Bob, and for your rapid response (as
well as reassuring...this one had me puzzling for hours, and it's
good to know it's your first time to see something like this, too)
. Perhaps it's because I've been crossing my eyes over this for
far too long, or perhaps it's because I'm not at the mental
aptitude of the author or yourself, but I'm not quite grogging the
example here.

It does make it appear that my solution is not quite right, nor
normalized. Is this essentially the creation of a 3 field
dependent join table?

Actually, what the result should be is three tables each containing
2 foreign keys,

Table 1 Table 2 Table 3
field 1 Field 2 Field 3
field 2 Field 3 Field 1

with relations matching the field numbers. A pictoral view looks
like a triangle. Each relationship is many to many, and other joins
link to the master tables

So you would have tables 1) Distributor-ItemNo, 2) ItemNo-POGSet, 3)
POGSet-Distributor, plus tables to define ItemNo, Distributor,
POGSet POGType..

HTH.
Q

=?Utf-8?B?VGltIEpvaG5zb24=?=

I am correcting a database for a supplier.

I have normalized the great majority of the data, and am having
a difficult time thinking the next step through:
[snip]

However, in addition to this, the supplier would like a
structure set up for plan-o-grams (the plan for where each item
is placed on shelve in a store). The trick is this, multiple
items can be placed into multiple plan-o-grams. One
plan-o-gram can belong to mulitple distributors, and one
distributor may rely on mulitple plan-o-grams depending on
their space availability. My initial thoughts were to set up a
series of tables:

tblProducts
index = ItemNo;

joined in a many to one relationship with

tblPOG
index = tblPOGType & ItemNo;

Joined in a many to one relationship with

tblPOGSet
index = POGSet & POGType

Joined in a many to one relationship with

tblDistributors

It seems to go against the whole purpose of relational
databases, though, the more I think about it, as I'm not sure
how to create these relationships without:
a.) Creating these as a separate entity from the Products to
JoinTable to Distributor (many to many) relationship; creating
an all new normalization issue or;
b.) Including this set somehow into the joined relationship,
forcing to the supplier to select a plan-o-gram anytime that he
would like to view which items are associated with which
distributor.

Any ideas?

In 25 years of database design, I've never seen a real world
example that requires Fifth Form Normalization, but I think that
this does.

see http://en.wikipedia.org/wiki/Fifth_normal_form

 

[Bob Quintal]

bonjour,
sil -vous plais comment je peut faire pour faire traduire en
langue française , merci de me donner une réponse , claudia;

Prends un cours d'anglais, langue seconde.

Les discussions dans ce groupe sont en temps reel,d'ordre technique sur
la programmation du logiciel Microsoft Access, et il n'y a pas de
service de traduction disponible.

 

[Guest]

Note, these 3-table multi-multi joins don't work very well
in Access.

That is, one of the great simplifications of Access is that you
can use a single bound form for create-read-update-delete,
but this great simplification starts to fail when you have these
many-table many-to-many joins.

That does not mean that you have the design wrong! It's
just that complex things remain complex.

When you have the many-to-many joins, Access can't work
out which record to update or delete. So you have to identify
the records separately, and create-update-delete the records
separately, because you can't just bind everything to one
recordset and let Access work it out for you.

There are two solutions: (A) flatten everything out a little bit
and duplicate the data. Use code or user processes to keep
the data synchronised between the tables. (B) Normalise,
and use code or user processes to keep the joins synchronised
between the tables.

Note, using some other tool or database engine does not make
this problem go away! Complex things remain complex!

However, you may find reduced advantage using Access
to bind your data to forms, and increased utility in SQL
Server stored procedures.

(david)

Thanks for the reference, Bob, and for your rapid response (as
well as reassuring...this one had me puzzling for hours, and it's
good to know it's your first time to see something like this, too)
. Perhaps it's because I've been crossing my eyes over this for
far too long, or perhaps it's because I'm not at the mental
aptitude of the author or yourself, but I'm not quite grogging the
example here.

It does make it appear that my solution is not quite right, nor
normalized. Is this essentially the creation of a 3 field
dependent join table?

Actually, what the result should be is three tables each containing
2 foreign keys,

Table 1 Table 2 Table 3
field 1 Field 2 Field 3
field 2 Field 3 Field 1

with relations matching the field numbers. A pictoral view looks
like a triangle. Each relationship is many to many, and other joins
link to the master tables

So you would have tables 1) Distributor-ItemNo, 2) ItemNo-POGSet, 3)
POGSet-Distributor, plus tables to define ItemNo, Distributor,
POGSet POGType..

HTH.
Q

=?Utf-8?B?VGltIEpvaG5zb24=?=

I am correcting a database for a supplier.

I have normalized the great majority of the data, and am having
a difficult time thinking the next step through:

[snip]

However, in addition to this, the supplier would like a
structure set up for plan-o-grams (the plan for where each item
is placed on shelve in a store). The trick is this, multiple
items can be placed into multiple plan-o-grams. One
plan-o-gram can belong to mulitple distributors, and one
distributor may rely on mulitple plan-o-grams depending on
their space availability. My initial thoughts were to set up a
series of tables:

tblProducts
index = ItemNo;

joined in a many to one relationship with

tblPOG
index = tblPOGType & ItemNo;

Joined in a many to one relationship with

tblPOGSet
index = POGSet & POGType

Joined in a many to one relationship with

tblDistributors

It seems to go against the whole purpose of relational
databases, though, the more I think about it, as I'm not sure
how to create these relationships without:
a.) Creating these as a separate entity from the Products to
JoinTable to Distributor (many to many) relationship; creating
an all new normalization issue or;
b.) Including this set somehow into the joined relationship,
forcing to the supplier to select a plan-o-gram anytime that he
would like to view which items are associated with which
distributor.

Any ideas?

In 25 years of database design, I've never seen a real world
example that requires Fifth Form Normalization, but I think that
this does.

see http://en.wikipedia.org/wiki/Fifth_normal_form

 

[Tim Johnson]

Thanks for the advice, David. Unfortunately, I think that cost is more of an
issue in this instance than ease of use. Would the lite edition be
sufficient for this?

I'm not very well versed in SQL server and am reluctant to suggest this as a
solution if I don't know how to initialize, design and deploy.

I've tried the many to many to many and it might be too many . While it
is the most normalized form, you could be right about flattening and using
code to update/delete. I've never come across a situation before where I've
felt that de-noralizing a database may be the best method.

I'm going to try a few different solutions and will post all of my
findings/my solution if anyone is interested.

Thanks everyone for all of your suggesitons and help.

Note, these 3-table multi-multi joins don't work very well
in Access.

That is, one of the great simplifications of Access is that you
can use a single bound form for create-read-update-delete,
but this great simplification starts to fail when you have these
many-table many-to-many joins.

That does not mean that you have the design wrong! It's
just that complex things remain complex.

When you have the many-to-many joins, Access can't work
out which record to update or delete. So you have to identify
the records separately, and create-update-delete the records
separately, because you can't just bind everything to one
recordset and let Access work it out for you.

There are two solutions: (A) flatten everything out a little bit
and duplicate the data. Use code or user processes to keep
the data synchronised between the tables. (B) Normalise,
and use code or user processes to keep the joins synchronised
between the tables.

Note, using some other tool or database engine does not make
this problem go away! Complex things remain complex!

However, you may find reduced advantage using Access
to bind your data to forms, and increased utility in SQL
Server stored procedures.

(david)

Thanks for the reference, Bob, and for your rapid response (as
well as reassuring...this one had me puzzling for hours, and it's
good to know it's your first time to see something like this, too)
. Perhaps it's because I've been crossing my eyes over this for
far too long, or perhaps it's because I'm not at the mental
aptitude of the author or yourself, but I'm not quite grogging the
example here.

It does make it appear that my solution is not quite right, nor
normalized. Is this essentially the creation of a 3 field
dependent join table?

Actually, what the result should be is three tables each containing
2 foreign keys,

Table 1 Table 2 Table 3
field 1 Field 2 Field 3
field 2 Field 3 Field 1

with relations matching the field numbers. A pictoral view looks
like a triangle. Each relationship is many to many, and other joins
link to the master tables

So you would have tables 1) Distributor-ItemNo, 2) ItemNo-POGSet, 3)
POGSet-Distributor, plus tables to define ItemNo, Distributor,
POGSet POGType..

HTH.
Q

:

=?Utf-8?B?VGltIEpvaG5zb24=?=

I am correcting a database for a supplier.

I have normalized the great majority of the data, and am having
a difficult time thinking the next step through:

[snip]

However, in addition to this, the supplier would like a
structure set up for plan-o-grams (the plan for where each item
is placed on shelve in a store). The trick is this, multiple
items can be placed into multiple plan-o-grams. One
plan-o-gram can belong to mulitple distributors, and one
distributor may rely on mulitple plan-o-grams depending on
their space availability. My initial thoughts were to set up a
series of tables:

tblProducts
index = ItemNo;

joined in a many to one relationship with

tblPOG
index = tblPOGType & ItemNo;

Joined in a many to one relationship with

tblPOGSet
index = POGSet & POGType

Joined in a many to one relationship with

tblDistributors

It seems to go against the whole purpose of relational
databases, though, the more I think about it, as I'm not sure
how to create these relationships without:
a.) Creating these as a separate entity from the Products to
JoinTable to Distributor (many to many) relationship; creating
an all new normalization issue or;
b.) Including this set somehow into the joined relationship,
forcing to the supplier to select a plan-o-gram anytime that he
would like to view which items are associated with which
distributor.

Any ideas?

In 25 years of database design, I've never seen a real world
example that requires Fifth Form Normalization, but I think that
this does.

see http://en.wikipedia.org/wiki/Fifth_normal_form