If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Parts table design question
It's time I re-worked the Parts/Ordering/Book-In tables, as I've outgrown my
original, I'm interested to hear opinions on how the table structures should be designed to accomodate the following; Manufacturer A produces a part, part number 12345 Manufacturer A produces an identical part but numbers it 12398 (as it fits a different model) Manufacturer A produces a box of 10 parts 12345 (or 12398) and gives it the part number 9876 Manufacturer B makes a Pattern 'copy' of 12345 but sells it under the part number B54321, & uses B59876 for a pack of 10 of the same pattern item Wholesaler C sells A's Part 123456 (&12398) under Part number C11111, A's part 9876 under Part number C11111A, B's Part Number B12345 under Part number C45456 other wholesalers will also supply pattern copies under different numbers & genuine items under their own part numbers in single items & bulk quantities. I currently have a recursive link table table, but am struggling as I need to be able to stock & sell (& obviously cross-reference too) a part that I buy & sell without ever knowing the original (genuine) part number, in otherwords how do I do the recursive links without a 'master' version to relate to? hope that makes sense. Tony |
#2
|
|||
|
|||
Parts table design question
Tony,
This is probably not the answer that you expected, but hopefully it's helpful. There were a few fundamentals which were not covered or clear in your post. If you yourself are not of these, clarifying these for yourself would be a good start. What specifically do you want the database to do? - Record parts, part numbers and information on them? - Record the identicalness or equivalency between parts? - Any other DB functionality required to "stock and sell? (inventory, pricing etc.) - Provide certain information to users? Could not understand your last paragraph to get such answers. Does a part have to be "identical" to be a cross reference? I presume not. Does "cross reference" suitability automatically mean a two way street? I.E. if "B" can replace "A", can it be automatically presumed that "A" can replace "B", or must this statement be checked/ made seperately? Will your DB systems presume and record that there is a genuine / original part number that the others "copy". You posts seems to be based on the answer to this being "yes" but then your second-to-the-last sentence seems to sa "No" This is probably not the answer that you expected, but hopefully it's helpful. Sincerely, Fred "TonyT" wrote: It's time I re-worked the Parts/Ordering/Book-In tables, as I've outgrown my original, I'm interested to hear opinions on how the table structures should be designed to accomodate the following; Manufacturer A produces a part, part number 12345 Manufacturer A produces an identical part but numbers it 12398 (as it fits a different model) Manufacturer A produces a box of 10 parts 12345 (or 12398) and gives it the part number 9876 Manufacturer B makes a Pattern 'copy' of 12345 but sells it under the part number B54321, & uses B59876 for a pack of 10 of the same pattern item Wholesaler C sells A's Part 123456 (&12398) under Part number C11111, A's part 9876 under Part number C11111A, B's Part Number B12345 under Part number C45456 other wholesalers will also supply pattern copies under different numbers & genuine items under their own part numbers in single items & bulk quantities. I currently have a recursive link table table, but am struggling as I need to be able to stock & sell (& obviously cross-reference too) a part that I buy & sell without ever knowing the original (genuine) part number, in otherwords how do I do the recursive links without a 'master' version to relate to? hope that makes sense. Tony |
#3
|
|||
|
|||
Parts table design question
Hi Fred,
thanks for response, answers in-line. "Fred" wrote: Tony, This is probably not the answer that you expected, but hopefully it's helpful. There were a few fundamentals which were not covered or clear in your post. If you yourself are not of these, clarifying these for yourself would be a good start. What specifically do you want the database to do? - Record parts, part numbers and information on them? Yes - Record the identicalness or equivalency between parts? In this instance just equivalence (the bolt must be the same thread pitch, diameter & length to do the job, but can be any colour) - Any other DB functionality required to "stock and sell? (inventory, pricing etc.) the basics are part number, description, location, selling price, terms & a list of possible suppliers & their part numbers - Provide certain information to users? Could not understand your last paragraph to get such answers. see below Does a part have to be "identical" to be a cross reference? I presume not. see above - But I do need to be able to identify a 'multi-pack' of the same part regardless of it's part number. Does "cross reference" suitability automatically mean a two way street? I.E. if "B" can replace "A", can it be automatically presumed that "A" can replace "B", or must this statement be checked/ made seperately? it is a two way street (although the user would need to know if one/both/all parts are pattern (copy) parts or genuine items (but this can be a true/false field in the part table itself) Will your DB systems presume and record that there is a genuine / original part number that the others "copy". You posts seems to be based on the answer to this being "yes" but then your second-to-the-last sentence seems to sa "No" No, this is what's causing me the headache. In most instances the answer will be yes, the user will look up the genuine part number in the parts diagram, then refer to the database to see if they have the genuine part OR one of many possible replacements for it (including the possibility of multi-pack's). BUT, some of the pattern parts catalogues just show pictures of the original item without any reference to genuine part numbers & the user needs to know if they have this item in stock under another number that again, may NOT be the genuine number (but rather a 2nd pattern manufacturers part or a wholesalers own part number for a genuine item). I did have a link table between my supplier table & part table, in which I stored THAT suppliers part number for the part (& a link for that part number back in the main parts table), but it doesn't readily allow for multi-packs and/or the fact that some suppliers supply both genuine parts and pattern parts under that suppliers own part numbers coupled with the fact that the genuine (master) part number is not always known. I do hope this is making some sense, don't feel I'm doing a very good job of explaining it, so feel free to ask as many more questions as you feel necessary. Tony This is probably not the answer that you expected, but hopefully it's helpful. Sincerely, Fred "TonyT" wrote: It's time I re-worked the Parts/Ordering/Book-In tables, as I've outgrown my original, I'm interested to hear opinions on how the table structures should be designed to accomodate the following; Manufacturer A produces a part, part number 12345 Manufacturer A produces an identical part but numbers it 12398 (as it fits a different model) Manufacturer A produces a box of 10 parts 12345 (or 12398) and gives it the part number 9876 Manufacturer B makes a Pattern 'copy' of 12345 but sells it under the part number B54321, & uses B59876 for a pack of 10 of the same pattern item Wholesaler C sells A's Part 123456 (&12398) under Part number C11111, A's part 9876 under Part number C11111A, B's Part Number B12345 under Part number C45456 other wholesalers will also supply pattern copies under different numbers & genuine items under their own part numbers in single items & bulk quantities. I currently have a recursive link table table, but am struggling as I need to be able to stock & sell (& obviously cross-reference too) a part that I buy & sell without ever knowing the original (genuine) part number, in otherwords how do I do the recursive links without a 'master' version to relate to? hope that makes sense. Tony |
#4
|
|||
|
|||
Parts table design question
Tony,
Your 2nd post helps provide the overall picture. The fact that a lot is still not clear is probably due to the complexity (the large number of scenarios, including variability of the relationships between part numbers. For example, when you say that some "just show a picture of the original part number", there is apparently a goal to imply suitability without stating it. A silver lining is that I have a feeling that wrestling with / dealing with / creating data rules for these types of questions is the hard part, and then, once done, the databasing will be the easy part. Rather than trying to understand every detaisl of yoru complex situation, I have just two more questions and then could probably provide some useful advice in the key areas. You say that some manufacturers "just show pictures of the original item without any reference to genuine part numbers & the user needs to know if they have this item in stock under another number that again, may NOT be the genuine number" Again, this seems to be that the supplier is providing a hint of applicability to the original number, without stating it or even providing any info (other than a picture) of what part he is implying applicability for. Is there or is there not a relationship between the two that must be documented? (e.g "applicable", or "supplier hints applicability".) If your DB must document or do something with this "hinted applicability" then there IS a master original part number, even if the suitability of it's replacement is only hinted. And someone must come up with that original part number that they are implying applicability for. They can't have it both ways. Either there is some applicability that you want to document or there isn't. Of course, "isn't" is a lot simpler! Second, what do you want the database to do regarding the multi-packs? (aside from just providing a record of them). Is that when they look up a single part number, then it tell them that a multi-pack of thos is avialable? |
#5
|
|||
|
|||
Parts table design question
In this instance just equivalence (the bolt must be the same thread pitch,
diameter & length to do the job, but can be any colour) I don't know much about bolts, but it would seem to me that there must also be some shear strength associate with each of these as well. Is that not important? How about bolts with the same pitch, diameter, and length, that accept pins, or that have the same overall length, but with a shorter thread. Are these characteristics not important. If it is not important to store lineage (which bolt came first), and the critical items are those listed above, and you are storing that information for each bolt, then I would think you could do your searchs based on that information, couldn't you? If you know a part#, any part number, you could look up that part, then query your database on the bolt characteristics, for other manufacturers bolts with the same characteristics. Dale -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "TonyT" wrote: Hi Fred, thanks for response, answers in-line. "Fred" wrote: Tony, This is probably not the answer that you expected, but hopefully it's helpful. There were a few fundamentals which were not covered or clear in your post. If you yourself are not of these, clarifying these for yourself would be a good start. What specifically do you want the database to do? - Record parts, part numbers and information on them? Yes - Record the identicalness or equivalency between parts? In this instance just equivalence (the bolt must be the same thread pitch, diameter & length to do the job, but can be any colour) - Any other DB functionality required to "stock and sell? (inventory, pricing etc.) the basics are part number, description, location, selling price, terms & a list of possible suppliers & their part numbers - Provide certain information to users? Could not understand your last paragraph to get such answers. see below Does a part have to be "identical" to be a cross reference? I presume not. see above - But I do need to be able to identify a 'multi-pack' of the same part regardless of it's part number. Does "cross reference" suitability automatically mean a two way street? I.E. if "B" can replace "A", can it be automatically presumed that "A" can replace "B", or must this statement be checked/ made seperately? it is a two way street (although the user would need to know if one/both/all parts are pattern (copy) parts or genuine items (but this can be a true/false field in the part table itself) Will your DB systems presume and record that there is a genuine / original part number that the others "copy". You posts seems to be based on the answer to this being "yes" but then your second-to-the-last sentence seems to sa "No" No, this is what's causing me the headache. In most instances the answer will be yes, the user will look up the genuine part number in the parts diagram, then refer to the database to see if they have the genuine part OR one of many possible replacements for it (including the possibility of multi-pack's). BUT, some of the pattern parts catalogues just show pictures of the original item without any reference to genuine part numbers & the user needs to know if they have this item in stock under another number that again, may NOT be the genuine number (but rather a 2nd pattern manufacturers part or a wholesalers own part number for a genuine item). I did have a link table between my supplier table & part table, in which I stored THAT suppliers part number for the part (& a link for that part number back in the main parts table), but it doesn't readily allow for multi-packs and/or the fact that some suppliers supply both genuine parts and pattern parts under that suppliers own part numbers coupled with the fact that the genuine (master) part number is not always known. I do hope this is making some sense, don't feel I'm doing a very good job of explaining it, so feel free to ask as many more questions as you feel necessary. Tony This is probably not the answer that you expected, but hopefully it's helpful. Sincerely, Fred "TonyT" wrote: It's time I re-worked the Parts/Ordering/Book-In tables, as I've outgrown my original, I'm interested to hear opinions on how the table structures should be designed to accomodate the following; Manufacturer A produces a part, part number 12345 Manufacturer A produces an identical part but numbers it 12398 (as it fits a different model) Manufacturer A produces a box of 10 parts 12345 (or 12398) and gives it the part number 9876 Manufacturer B makes a Pattern 'copy' of 12345 but sells it under the part number B54321, & uses B59876 for a pack of 10 of the same pattern item Wholesaler C sells A's Part 123456 (&12398) under Part number C11111, A's part 9876 under Part number C11111A, B's Part Number B12345 under Part number C45456 other wholesalers will also supply pattern copies under different numbers & genuine items under their own part numbers in single items & bulk quantities. I currently have a recursive link table table, but am struggling as I need to be able to stock & sell (& obviously cross-reference too) a part that I buy & sell without ever knowing the original (genuine) part number, in otherwords how do I do the recursive links without a 'master' version to relate to? hope that makes sense. Tony |
#6
|
|||
|
|||
Parts table design question
"Fred" wrote: Tony, Your 2nd post helps provide the overall picture. The fact that a lot is still not clear is probably due to the complexity (the large number of scenarios, including variability of the relationships between part numbers. For example, when you say that some "just show a picture of the original part number", there is apparently a goal to imply suitability without stating it. A silver lining is that I have a feeling that wrestling with / dealing with / creating data rules for these types of questions is the hard part, and then, once done, the databasing will be the easy part. Rather than trying to understand every detaisl of yoru complex situation, I have just two more questions and then could probably provide some useful advice in the key areas. You say that some manufacturers "just show pictures of the original item without any reference to genuine part numbers & the user needs to know if they have this item in stock under another number that again, may NOT be the genuine number" Again, this seems to be that the supplier is providing a hint of applicability to the original number, without stating it or even providing any info (other than a picture) of what part he is implying applicability for. Is there or is there not a relationship between the two that must be documented? (e.g "applicable", or "supplier hints applicability".) not MUST, if the original (by original I mean oem genuine part number of which there can be more than one A12345 & A12398 are the same part EXACTLY but have different part numbers as they fit different models of machinery) part number is known I want it to be obvious that it is a/the genuine number, but if the genuine number is not known I still want to be able to cross reference against other non-genuine 'suitable replacement parts'. It would be too restrictive on the end user to require them to ascertain a genuine part number for every instance of pattern parts sold/stocked, it is obvious from the pictures what the part is, but the user may have no means (or need) to find the manufacturers genuine part number, especially as this item may only ever be used once and be valued at pennies. The 'hinted' applicability you mention is irrelevant in as much as a part is relevant or it isn't, ne reference need be recorded as to whether a 'hint' has been made. The reasons for not displaying the genuine part number by the aftermarket supplier can vary, but the end user needs to know if the part is genuine or aftermarket, they themselves have to determine applicability. If your DB must document or do something with this "hinted applicability" then there IS a master original part number, even if the suitability of it's replacement is only hinted. And someone must come up with that original part number that they are implying applicability for. They can't have it both ways. Either there is some applicability that you want to document or there isn't. Of course, "isn't" is a lot simpler! I want to know the relationship between; Genuine Part Number (IF known) Aftermarket Part number/s (If any, could be 0 to 4 different numbers for the same individual part) Bulk Part numbers (usually 2, 5, 10, 25 or 100 of an individual part sold in a single package to the database user, who then usually sells the parts individually to the end user, but will occasionally re-sell the bulk package 'as is' to other trade customers) including hopw many items make up the 'bulk package' at the same time as knowing if the part number in question is genuine, aftermarket, bulk or bulk & aftermarket. The relationship will need to be established by the database user, they may not even know that 2 parts are identical/interchangeable as the records are created (A12345 & A12398 again). Second, what do you want the database to do regarding the multi-packs? (aside from just providing a record of them). Is that when they look up a single part number, then it tell them that a multi-pack of thos is avialable? Basically yes, although I will be working out a way of offering the user the ability to order a bulk pack if they are trying to order the same (or nearly as many) single items as contained in a bulk pack, but this is secondary to the above criteria and should become easier to see when the basic structure is 'in-place'. There are a few further irregularities/querks of the trade that may throw up more issues, but for now would more likely cloud things rather than aide clarity. many, many thanks Fred for taking the time to try and understand my needs and helping point me in the right direction. Tony.. |
#7
|
|||
|
|||
Parts table design question
thanks and sorry Dale, I tried to find an example that illustrated my point,
but as you have so neatly identified, nothing is as straightforward as you first think. Bolts are just one of thousands of lines of parts that can fit the horticultural machinery I deal with. The lineage of any given part is not important, just it's suitability to do the job it's required to do. Probably a better example would be that of a paper element air filter, only a part specifically designed to fit machine Z will do the job, but I can buy the genuine part, one of many aftermarket manufacturers 'copies' of the part or a box of 10 filters from either the genuine manufacturer or one of the aftermarket 'copy' manufacturers. Anyone with enough knowledge will be able to look at a picture and determine that the image of the filter they are looking at will fit machine Z because, lets say, for example, it is triangular. To be blunt, the database user is interested in balancing the difference in cost between the aftermarket part AND the genuine part IF KNOWN and what they will then sell that part for, and how much af a saving they can make by buying a bulk pack. Relying on searches to be able to identify possible and actual interchangeable parts is not practicable, too many users entering information manually with many having literacy / dyslexia problems I'm afarid. I need a way of linking parts as and when someone identifies the interchangeability, which could be anyone at anytime. "Dale Fye" wrote: In this instance just equivalence (the bolt must be the same thread pitch, diameter & length to do the job, but can be any colour) I don't know much about bolts, but it would seem to me that there must also be some shear strength associate with each of these as well. Is that not important? How about bolts with the same pitch, diameter, and length, that accept pins, or that have the same overall length, but with a shorter thread. Are these characteristics not important. If it is not important to store lineage (which bolt came first), and the critical items are those listed above, and you are storing that information for each bolt, then I would think you could do your searchs based on that information, couldn't you? If you know a part#, any part number, you could look up that part, then query your database on the bolt characteristics, for other manufacturers bolts with the same characteristics. Dale -- HTH Dale Don''t forget to rate the post if it was helpful! email address is invalid Please reply to newsgroup only. "TonyT" wrote: Hi Fred, thanks for response, answers in-line. "Fred" wrote: Tony, This is probably not the answer that you expected, but hopefully it's helpful. There were a few fundamentals which were not covered or clear in your post. If you yourself are not of these, clarifying these for yourself would be a good start. What specifically do you want the database to do? - Record parts, part numbers and information on them? Yes - Record the identicalness or equivalency between parts? In this instance just equivalence (the bolt must be the same thread pitch, diameter & length to do the job, but can be any colour) - Any other DB functionality required to "stock and sell? (inventory, pricing etc.) the basics are part number, description, location, selling price, terms & a list of possible suppliers & their part numbers - Provide certain information to users? Could not understand your last paragraph to get such answers. see below Does a part have to be "identical" to be a cross reference? I presume not. see above - But I do need to be able to identify a 'multi-pack' of the same part regardless of it's part number. Does "cross reference" suitability automatically mean a two way street? I.E. if "B" can replace "A", can it be automatically presumed that "A" can replace "B", or must this statement be checked/ made seperately? it is a two way street (although the user would need to know if one/both/all parts are pattern (copy) parts or genuine items (but this can be a true/false field in the part table itself) Will your DB systems presume and record that there is a genuine / original part number that the others "copy". You posts seems to be based on the answer to this being "yes" but then your second-to-the-last sentence seems to sa "No" No, this is what's causing me the headache. In most instances the answer will be yes, the user will look up the genuine part number in the parts diagram, then refer to the database to see if they have the genuine part OR one of many possible replacements for it (including the possibility of multi-pack's). BUT, some of the pattern parts catalogues just show pictures of the original item without any reference to genuine part numbers & the user needs to know if they have this item in stock under another number that again, may NOT be the genuine number (but rather a 2nd pattern manufacturers part or a wholesalers own part number for a genuine item). I did have a link table between my supplier table & part table, in which I stored THAT suppliers part number for the part (& a link for that part number back in the main parts table), but it doesn't readily allow for multi-packs and/or the fact that some suppliers supply both genuine parts and pattern parts under that suppliers own part numbers coupled with the fact that the genuine (master) part number is not always known. I do hope this is making some sense, don't feel I'm doing a very good job of explaining it, so feel free to ask as many more questions as you feel necessary. Tony This is probably not the answer that you expected, but hopefully it's helpful. Sincerely, Fred "TonyT" wrote: It's time I re-worked the Parts/Ordering/Book-In tables, as I've outgrown my original, I'm interested to hear opinions on how the table structures should be designed to accomodate the following; Manufacturer A produces a part, part number 12345 Manufacturer A produces an identical part but numbers it 12398 (as it fits a different model) Manufacturer A produces a box of 10 parts 12345 (or 12398) and gives it the part number 9876 Manufacturer B makes a Pattern 'copy' of 12345 but sells it under the part number B54321, & uses B59876 for a pack of 10 of the same pattern item Wholesaler C sells A's Part 123456 (&12398) under Part number C11111, A's part 9876 under Part number C11111A, B's Part Number B12345 under Part number C45456 other wholesalers will also supply pattern copies under different numbers & genuine items under their own part numbers in single items & bulk quantities. I currently have a recursive link table table, but am struggling as I need to be able to stock & sell (& obviously cross-reference too) a part that I buy & sell without ever knowing the original (genuine) part number, in otherwords how do I do the recursive links without a 'master' version to relate to? hope that makes sense. Tony |
#8
|
|||
|
|||
Parts table design question
Tony,
Sorry if I sound a little retentive on the fine points. Access and answering these posts is my hobby. My day job is running technical companies, my roots are engineering and, since 1982, a subset of my work has included a lot of part numbering, manufacturing, engineering etc. systems. The other people who respond to these posts know specialized coding etc. 100 times better than I do. I use “relationship” in two different meanings, #1 being a way to put words on and give entity status to your relationships between parts, and #2 referring to database structure. Databases define relationships between items based on explicit rules. After reading all of your posts, even the list of relationship types seems to be a “work in progress” much less the explicit rules to define them. Trying to extract it literally from your text, there are 10 types of relationships ("Identical", "Interchangeable", "Copy", "Pattern Copy". "Cross Reference", "Replacement", "Under", "Under different part", "Patten Part" and "Own Part number for" between parts. And some of these have real definitions, while others don’t because they are in the “eye of the beholder” (= left to the user to decide) But, this is fine, it just means that you need a system that provides a framework to accommodate all of these possibilities. In DB (def #2) terms, you have a “many-to-many” database relationship between parts. My structure idea includes is what’s normally called a “junction table” but I’m describing it in a different way without using that term. Also, my structure idea kind of “loops”; I’ve only done this a few times but it seems to work.....i.e. don’t jump off the cliff with my “parachute” until try it out on a small scale. . And I've avoiding suggesting a BOM ("Bill of Material") structure for your multi-packs because I think that that would be overkill. First, take your first try at listing the types of relationships that you intend to define and document. I assume it really only 2 or 3 from the above list of 10. Make a little 2 or 3 field tables which will be a source for a “dropdown” to populate fields later. Here’s a 2 field example: (I use longer field names to be descriptive, you should shorten them) tbeRelationshipTypes fldRelationType: EG “SubstituteFor”, “MultiPackOf”, “MaybeeSubstituteCustomerToDecideForThemself” fldDefinition Your definition (data rule) for that relationship type. The three field version would add a “RelationshipTypeIDNumber” and, when using this as a dropdown, you’ll load just this code number rather than the text. Two main tables, a "Parts" table and a "Relationship" table. The tblParts table lists every partnumber, plus all desired information that relates to that part number. (but which does NOT relate to any other part number- for multipacks, I'm not considering a description of how to build it to be a violation of this). Example fieldsin Parts Table : PartNumber (Primary Key) Description (including what's in a multi-pack) Originalness (choices = yes, no) Notes Price Any other fields you want that relate ONLY to that part number Now make a "tblPartRelations" Table with a record for each instance of any type of a relationship between two parts. Use directionality in your entries and definitions, I.E consider each record to be a one way definition. I.E. if two parts can each replace each other, then you need to enter two records showing that Part#2 has a “SubstituteFor” relationship with Part#1, a with the positions of those numbers swapped. Key Fields a PartNumberA The # of the part that is the subject of PartNumberB’s relationship as defined in this record. PartNumberB The # of the part that “has” the relationship to another part Relationship "What is part number B in relation to part number "A". Make a dropdown list from your tblRelationship types to either load a code for more descriptive text, or exact shorter text from the list, depending on whether you chose the “2 field” or “”3 field” table for the dropdown list. Put the Parts table in twice in the relationships window, and then Link PartNumberA to PartNumber and PartNumberB to Partnumber I think that this overall structure will accommodate your wide ranging "relationship" possibilities and also your end objectives of what you want this database to do. Hope this helps a little Fred |
#9
|
|||
|
|||
Parts table design question
Tony,
See above, I attached my idea to your earlier post Fred |
#10
|
|||
|
|||
Parts table design question
Hi and thanks again Fred,
I think your junction table with additional relationship link will do what I'm after better than any approach I've come up with & the directionality of the table also makes a lot of sense. I'll need to have a good think on all the possible types of relationships, as you say, you have put an entry on each different way I have tried to describe the relationships, rather than the differing possiblities. First thoughts after a long days work are; Alternate Genuine Part Number reverse = the same Genuine Bulk Pack Part Number reverse = Singular item from Genuine Bulk Pack Aftermarket Bulk Pack Part Number reverse = Singular item from Aftermarket Bulk Pack Aftermarket (pattern) Part Number reverse = Genuine Part Number Alternate Aftermarket Part Number reverse = the same Genuine Part Number reverse = Aftermarket Part Genuine Singular Item from Bulk reverse = Genuine Bulk Pack Part Number Aftermarket Singular Item from Bulk reverse = Aftermarket Bulk Pack Part Number Genuine Part Under Suppliers Part Number reverse = Genuine Part Number which gives 10 different relationships with defined reverse relationships that can be entered into junction table without further user input, which is good, although it does omit the possibility of a few conceivable scenarios whereby the supplier supplies a genuine part under their own part numbers etc. etc. there is one further relationship that I encounter, which I think can be handled by the relationship table & that is of assembly/kit parts purchased & then split to be sold as component parts, which could (very, very occasionally) encompass aftermarket parts too therefore adding a further 4 relationships; Genuine Item from Kit reverse = Genuine Kit/Assembly of Parts Aftermarket Item from Kit reverse = Aftermarket Kit/Assembly of Parts All a bit more complicated than I'd hoped to make it, but I can't see a simpler way. Just to add, part number on it's own can't be guaranteed unique, so i'll be using an autonumber field, which throws up one further question for you; my database users can order (genuine parts only referred to here) each part from various wholesalers who can use different prefixes for each manufacturer eg. Supplier 1 uses BS-A12345 (the - dash is arbitary) whereas supplier 2 uses BP-A12345 for the same part (BS = Briggs & Stratton, BP = Briggs Part (I assume)) Currently I use the prefix as part of the Part number which guarantees uniqueness, but I need to get away from this way of doing it as often users end up with duplicated records. I have intended to use another junction table between the Part table & the supplier table so that an order will always use the correct prefixes & a part can be selected using any of it's relevant prefixes. Does this seem sensible to you, or can you see a better way utilising your proposed relationship table also being linked to the supplier table? I don't know if you have ever been unfortunate enough to deal with the horticultural machinery trade, but we are fighting to get out of the dark ages when it comes to technology and part numbering / supply! thanks again, TonyT.. "Fred" wrote: Tony, Sorry if I sound a little retentive on the fine points. Access and answering these posts is my hobby. My day job is running technical companies, my roots are engineering and, since 1982, a subset of my work has included a lot of part numbering, manufacturing, engineering etc. systems. The other people who respond to these posts know specialized coding etc. 100 times better than I do. I use “relationship” in two different meanings, #1 being a way to put words on and give entity status to your relationships between parts, and #2 referring to database structure. Databases define relationships between items based on explicit rules. After reading all of your posts, even the list of relationship types seems to be a “work in progress” much less the explicit rules to define them. Trying to extract it literally from your text, there are 10 types of relationships ("Identical", "Interchangeable", "Copy", "Pattern Copy". "Cross Reference", "Replacement", "Under", "Under different part", "Patten Part" and "Own Part number for" between parts. And some of these have real definitions, while others don’t because they are in the “eye of the beholder” (= left to the user to decide) But, this is fine, it just means that you need a system that provides a framework to accommodate all of these possibilities. In DB (def #2) terms, you have a “many-to-many” database relationship between parts. My structure idea includes is what’s normally called a “junction table” but I’m describing it in a different way without using that term. Also, my structure idea kind of “loops”; I’ve only done this a few times but it seems to work.....i.e. don’t jump off the cliff with my “parachute” until try it out on a small scale. . And I've avoiding suggesting a BOM ("Bill of Material") structure for your multi-packs because I think that that would be overkill. First, take your first try at listing the types of relationships that you intend to define and document. I assume it really only 2 or 3 from the above list of 10. Make a little 2 or 3 field tables which will be a source for a “dropdown” to populate fields later. Here’s a 2 field example: (I use longer field names to be descriptive, you should shorten them) tbeRelationshipTypes fldRelationType: EG “SubstituteFor”, “MultiPackOf”, “MaybeeSubstituteCustomerToDecideForThemself” fldDefinition Your definition (data rule) for that relationship type. The three field version would add a “RelationshipTypeIDNumber” and, when using this as a dropdown, you’ll load just this code number rather than the text. Two main tables, a "Parts" table and a "Relationship" table. The tblParts table lists every partnumber, plus all desired information that relates to that part number. (but which does NOT relate to any other part number- for multipacks, I'm not considering a description of how to build it to be a violation of this). Example fieldsin Parts Table : PartNumber (Primary Key) Description (including what's in a multi-pack) Originalness (choices = yes, no) Notes Price Any other fields you want that relate ONLY to that part number Now make a "tblPartRelations" Table with a record for each instance of any type of a relationship between two parts. Use directionality in your entries and definitions, I.E consider each record to be a one way definition. I.E. if two parts can each replace each other, then you need to enter two records showing that Part#2 has a “SubstituteFor” relationship with Part#1, a with the positions of those numbers swapped. Key Fields a PartNumberA The # of the part that is the subject of PartNumberB’s relationship as defined in this record. PartNumberB The # of the part that “has” the relationship to another part Relationship "What is part number B in relation to part number "A". Make a dropdown list from your tblRelationship types to either load a code for more descriptive text, or exact shorter text from the list, depending on whether you chose the “2 field” or “”3 field” table for the dropdown list. Put the Parts table in twice in the relationships window, and then Link PartNumberA to PartNumber and PartNumberB to Partnumber I think that this overall structure will accommodate your wide ranging "relationship" possibilities and also your end objectives of what you want this database to do. Hope this helps a little Fred |
|
Thread Tools | |
Display Modes | |
|
|