Just about to bring an application online and the client threw in a last minute
"by the way". I'm welcoming any suggestions on how to structure this. Hope
that the email will format good enough to explain my points!
There are generic Products, so let's say Widget is one of them, with a
ProductID of 100. Underneath that was (originally) a table that identifies
all the Suppliers of that Product. So we could have 3 suppliers of Widgets,
for SupplierIDs 201, 305 and 504.
ProductID SupplierID
100 201
100 305
100 504
Here's the new wrinkle. In most of the cases, Suppliers are stand-alone
entities, one location, one set of contacts. Now they want to incorporate
multinational suppliers. So let's say that 201 and 305 are stand-alone
entities, but 504 is BigCo Ltd, with 3 locations around the world. We still
need to know for reporting purposes everything that the parent BigCo supplies
us, but the Product really needs to be associated directly with one of the 3
locations of BigCo Ltd.
So I figured that a decent structure would be to have a table called
SupplierGroups, with BigCo Ltd being one of the groups, with a GroupID of 1.
I will then add a GroupID column to my master Suppliers table; so for BigCo's
subs I would have 3 rows as follows in my Suppliers table:
SupplierID GroupID Supplier Name
504 1 BigCo US
505 1 BigCo China
506 1 BigCo Russia
My Widget above is associated directly with SupplierID 504 BigCo US. For
reporting purposes, I can easily get to the parent company BigCo by using the
GroupID column of the associated group if there is one.
So -- so far do you think that sounds okay? I did, until they threw in yet
another wrinkle after I presented this. And this one I can't figure out how to
handle best. With me so far? Appreciate it....
Now, each individual Supplier can have multiple distributors. So BigCo US has
just one address, one set of contacts, but could have multiple distributors of
their products. There won't be many of them, but it will happen. They might
or might not care about statistics per distributor, but I do need to yet again
associate the Widget with BigCo US, but for a particular distributor. I guess
the "easiest" thing is to create more suppliers but it won't help me figure out
what BigCo US supplies us so I'm thinking this structure would be no good:
SupplierID GroupID Supplier Name
504 1 BigCo US - Distributor A
503 1 BigCo US - Distributor B
505 1 BigCo China
506 1 BigCo Russia
Or do I create another table called Distributors, link it to a SupplierID.
SupplierID DistributorID Supplier Name
504 1 BigCo US - Distributor A
504 2 BigCo US - Distributor B
Do I then link the Widget down to the very lowest level -- a DistributorID
rather than a SupplierID? The problem is, few of the suppliers will have
multiple distributors, so I would have to create a "dummy" distributor for
every supplier, right?
Or do I expand my Product/Supplier table to include both a SupplierID and a
DistributorID if there is one? Structure looks simple but the code to
maintain these levels might not be. Here, the Widget has SupplierID 504, but
through 2 different distributors:
ProductID SupplierID DistributorID
100 201 -0-
100 305 -0-
100 504 1
100 504 2
Does anyone already done anything like this? Feel free to contact me off-list.
Thanks everyone!
Karen
