Normalization question

[Guest]

I apologize before hand, but it seems that I am have no lack of words right
now:

I have a friend who has been creating their own database without regard, up
until this point, toward normalization. He knows I work in Access and has
asked for help. I have been working on alleviating the very obvious
(identical data should only be stored in one table, etc); but am having a
hard time grogging the next step:

My friend has created created a Products table, with ItemNo as the unique
identifier, this shares a relationship with a Distributors Authorized table,
which also has ItemNo as the unique identiier. I know that this will likely
need correction, but I'm a bit perplexed per the way it's set up.

He has roughly 50 fields named for each of his distributors, and has THEIR
item number placed in that field; my first instinct is to write a module to
split each of those fields out into a Distributors Table, then breake out the
non-null values contained under each distributors and use that with HIS
ItemNo to create a join table (essentially forming 3 tables from 2, using the
third to create a many to many relationship).

However; the way he has his data entry forms set up, and due to the large
number of items he has, I'm not sure this is the best way to go, as it could
require quite a bit of scrolling in order to locate his items from a combo
box/list box in order to assign his distributors item numbers to them, and it
won't easily display the null values where he might want to see that.

Is it better to essentially create a reverse crosstab with the module,
breaking out the fields that are distributor names into their own table, then
create an additional table with the DistributorID and the ItemNo to match
everything up? Should I leave these 50 or so Distributors as field names?

Thanks in advance for any help.

 

[Jeff Boyce]

Tim

The disadvantage to keeping "50 distributers as field names" is the
intensive maintenance required when you have to add (or remove) one
distributor... all the related queries, forms, reports, macros and code will
need to be altered, not to mention the table.

It sounds like "Distributors" is another entity about which you will want to
keep information, so use a table to list distributors. ?Add one? Handle
this by adding a new row. ?Remove one? Handle this by ... including a
field on the Distributor table to hold a Yes/No field (ActiveDistributor?)
or a Date/Time field (DateDeactivated).

It sounds like you are describing that a Distributor has (offers) an Item
for which it has an ItemNumber (specific to that Distributor). You'd need a
table that lets you associate a DistributorID and an ItemID (from their
respective tables) with a (distributor-provided) ItemNumber. You could
either use an AutonumberID as the rowID for this table (unique, arbitrary
rowID) or use a composite Primary Key made of the combination of
DistributorID, ItemID and DistributorsItemNumber fields.

Does this get you closer?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Guest]

Jeff, thanks for your advice.

It does help, insofar as letting me know my first instinct was correct.
Although, the Distributor is actually the customer in this instance, and my
friend the supplier, I think that's little difference at all. I will be
creating a table now specifically for the join. Although, this now
introduces a couple of new questions:

1.) With the UI that grandfathered it's way down to me, the data entry form
used for these tables is Item-centric, and in this my friend has the ItemNo,
Description, UPC, and a textbox for entering the DistributorItemNo for EACH
distributor on his list (whether the value is null or not). With a join
table, I would populate the distributors into this form using a subform, but
that would omit any distributor who once had a null value for their
DistributorItemNo, as they would not be populated into the newly created join
table. What's the easiest way to prevent him from having to scroll through a
combo box/list box to add in a distributor-item join should a distributor
decide to start carrying another of his products?

2.) I completely understand and agree with using the ItemNo and
DistributorID as a combined primary key, but have had many problems creating
relationships based off of keys like this in the past (not sure why). I have,
in the past for similar table structures, used a combination of the primary
keys for the 2 tables which the newly created table is joining and made them
a combined primary key. Then used the Relationship Manager to create a
Many-to-one relationship with each of the 2 original tables, but continually
get errors. Is there a trick to this?