Hi Karen, Since you're not certain how they are going to use the distributer data, I'd just go with the distributer table. It allows the best structure with the least amount of work. You could have null entries for suppliers w/o distributors like your example, or you can handle it at report time with a view, or temp table and inserts, or ...
Ben On Mon, Aug 26, 2013 at 3:19 PM, Karen Tellef <[email protected]> wrote: > 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 > > > >
