Oh goodness... not the "BOM" discussion again. Ouch. But you're probably right.
Karen -----Original Message----- From: James Bentley <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Mon, Aug 26, 2013 6:23 pm Subject: [RBASE-L] - Re: Table design help? Karen, Your problem seems to fall under the category of a "Bill of Materials" with uneven levels. You might want to check out Joe Cleko's "Trees and Hierarchies in SQL for Smarties". Jim Bentley, American Celiac Society 1-504-737-3293 From: Karen Tellef <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Monday, August 26, 2013 5:19 PM Subject: [RBASE-L] - Table design help? 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
