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 |
#11
|
|||
|
|||
Parts table design question
Hello Tony,
A couple of thoughts that came to mind while reading your post, and then an attempt to answer your question. My background is more biased towards bieng the manufacturer (e.g. currently fsinet.com) and making up / controlling our own part numbers, but I've had some experience with databasing other people's part numbers. By giving up the concept of unique part numbers, you are giving up a lot....not sure I'd recommend caving so quickly on that one. One thing I've had my folks do when databasing other people's part numbers where the "other people" sre sloppy with them is to have two fields: Their part number and our part number. 99.9% of the time we use their part number as our part number, and in the rare casess where they sin, we can make our # different. I really thought you'd end up wih 3 or 4 types of relationships, not 20, and you still might want to try to do that. I might not have been clear what I meant when I said "directional" I didn't mean making up 2 relationship types for each relationship types, I just meant just considering the relationship to be directional. For example, if Part#1 is a sub for part#2, and Part #2 is a sub for part #2, you'd enter 2 records with the same relationship type as follows: #1 IsASubFor #2 #2 IsASubFor #1 I didn't understand what your desired goal was on your last question. Thile I try to minimize "many-to-many" junction tables / relationships to absolute necessities, one-to-many ones are no bigge. Also not sure whether my "unique part number" intro is relevant to this. If it's jsut to record that there are sever different potential suppliers for a given par number, and to enable automatically adding their prefix, then I'd just add a SupplierIDNUmber field to your parts table, and then make a Supplier tables. Amongst fields to record whateve you want on them, I'd include: SupplierIDNumber SupplierSuffix. And then link them on supplier IDNUmber. Hope that helps a little. Sincerely, Fred |
#12
|
|||
|
|||
Parts table design question
answers in-line again Fred
"Fred" wrote: Hello Tony, A couple of thoughts that came to mind while reading your post, and then an attempt to answer your question. My background is more biased towards bieng the manufacturer (e.g. currently fsinet.com) and making up / controlling our own part numbers, but I've had some experience with databasing other people's part numbers. By giving up the concept of unique part numbers, you are giving up a lot....not sure I'd recommend caving so quickly on that one. One thing I've had my folks do when databasing other people's part numbers where the "other people" sre sloppy with them is to have two fields: Their part number and our part number. 99.9% of the time we use their part number as our part number, and in the rare casess where they sin, we can make our # different. The uniqueness of part numbers isn't due to mistakes by one supplier/manufacturer, but comes as a result of the database user dealing with perhaps 25 different manufacturers and it's possible that a Honda part number might be the same as a Kawasaki part number for example. So either you add in an arbitary manufacturer prefix, which you then have to remove & replace with the suppliers arbitary prefix (as per my BS or BP example), or use just the part number with another primary key. - see below for further clarification of prefixes. I really thought you'd end up wih 3 or 4 types of relationships, not 20, and you still might want to try to do that. I might not have been clear what I meant when I said "directional" I didn't mean making up 2 relationship types for each relationship types, I just meant just considering the relationship to be directional. For example, if Part#1 is a sub for part#2, and Part #2 is a sub for part #2, you'd enter 2 records with the same relationship type as follows: #1 IsASubFor #2 #2 IsASubFor #1 Unfortunately this simplified approach won't give enough detail, a user needs to know which is the bulk pack vs an item from it, or which is the genuine or the pattern part. As you can see from the list of real-world examples directionality is required to fully cover the, possibly compounded, relationships between; genuine, pattern, individual, bulk, assembly. I didn't understand what your desired goal was on your last question. Thile I try to minimize "many-to-many" junction tables / relationships to absolute necessities, one-to-many ones are no bigge. Also not sure whether my "unique part number" intro is relevant to this. If it's jsut to record that there are sever different potential suppliers for a given par number, and to enable automatically adding their prefix, then I'd just add a SupplierIDNUmber field to your parts table, and then make a Supplier tables. Amongst fields to record whateve you want on them, I'd include: SupplierIDNumber SupplierSuffix. And then link them on supplier IDNUmber. each supplier (or wholesaler NOT manufacturer) will use a different prefix for each brand they stock, so supplier A uses BS-A12345 supplier B uses BP-A12345 (for Briggs & Stratton), whereas supplier A uses HO-1234567-011-011 and Supplier B uses HP-1234567-011-011 for the same Honda Part. Just glanced at 2 suppliers, one has 50 different prefixes for the range of machinery brands they cover, whereas the second has perhaps 30 different prefixes for the same machinery brands, plus the same prefix for different brands in quite a few instances. Hence the link table, as it can't be handled in a single supplier table. But I want an order to supplierA to use the part numbers they understand & an order to supplierB (for the same parts possibly) to use their part numbers. Neither will be able to fulfill an order without knowing manufacturer, so the prefix becomes part of the part number to these suppliers and then has become inherited by their customers, who are my customers :/ thanks again, Is my situation really that unusual? I can't beleive it doesn't equate to other trades, such as the motor trade or some other where the supplier is usually a wholesaler rather than the manufacturer & there are pattern parts available alongside genuine. TonyT.. Hope that helps a little. Sincerely, Fred |
#13
|
|||
|
|||
Parts table design question
Hello Tony,
Answering your first and last question, having challenges databasing a fuzzy complex situaiton or in trying to explain it to someone else isn't and doesn't imply "unusual". What IS unusual is a situaiton or perceived situaiton where, in an industry, there is a universal set of rules for the middlemen's creation of their part numbers to the point where somebody can depend on decomposing their part numbers by the same universal set of rules and then so reliably derive data from the "pieces" of their part number that you can database pieces of their part number. Long story short, while you usually may be able to derive information from the middleman's part numbers, I would simply treat them as the middleman's whole part numbers. And the you will need to store their name and their part number, the combination of those two fields should be unique. Second, my method DOES understand the "directionality" of those relationships, it just uses a different way to record it. If I perceive your way correctly, here's a comparison using an example. Let's say that there is some type of a liking relationship between Dick and Jane, you want to record it. Tony's method: Set up relationship choices of all of the possibilities and then enter 1 record which specifies it. Relevant relationship choices: Choice #1 A likes B, B doesn't like A Choice #2 B likes A, A doesn't like B Choice #3 They both like each other. And so Tony's method to record that they both like each other would be to enter one record: Field A Relationship Type FieldB Dick They Both Like Each Other Jane Fred's method would have only one relevant relationship choice: "A likes B" and would record "both like each other" by entering two records: FieldA RelationshipType FieldB Dick A likes B Jane Jane A likes B Dick Sincerely, Fred |
#14
|
|||
|
|||
Parts table design question
Hi Fred,
"Fred" wrote: Hello Tony, Answering your first and last question, having challenges databasing a fuzzy complex situaiton or in trying to explain it to someone else isn't and doesn't imply "unusual". What IS unusual is a situaiton or perceived situaiton where, in an industry, there is a universal set of rules for the middlemen's creation of their part numbers to the point where somebody can depend on decomposing their part numbers by the same universal set of rules and then so reliably derive data from the "pieces" of their part number that you can database pieces of their part number. Long story short, while you usually may be able to derive information from the middleman's part numbers, I would simply treat them as the middleman's whole part numbers. And the you will need to store their name and their part number, the combination of those two fields should be unique. I think you are mixing up 2 separate parts of my descriptions and examples (or more likely I'm not being clear enough) the prefixing issue is separate to the relationship problem. In my previous example Part number A12345 is NOT made up, it is the genuine Part number given to a part made by Briggs and Stratton, despite different Suppliers identifying any given part as having been made by Briggs and Stratton with a prefix (eg BS or BP or BR etc. etc. etc.) I can be certain that A12345 is A12345 - a genuine Briggs and Stratton Part - regardless of whether a sticker on it says BS-A12345 or BP-A12345, so don't need to store them as separate records, what I do need to know is which supplier puts BS in front of EVERY Briggs & Stratton part they supply and which Supplier uses BP instead. Second, my method DOES understand the "directionality" of those relationships, it just uses a different way to record it. If I perceive your way correctly, here's a comparison using an example. Let's say that there is some type of a liking relationship between Dick and Jane, you want to record it. Tony's method: Set up relationship choices of all of the possibilities and then enter 1 record which specifies it. Relevant relationship choices: Choice #1 A likes B, B doesn't like A Choice #2 B likes A, A doesn't like B Choice #3 They both like each other. And so Tony's method to record that they both like each other would be to enter one record: Field A Relationship Type FieldB Dick They Both Like Each Other Jane Fred's method would have only one relevant relationship choice: "A likes B" and would record "both like each other" by entering two records: FieldA RelationshipType FieldB Dick A likes B Jane Jane A likes B Dick Another misinterpretation I fear my entering ' reverse =' was just my way of ensuring that each selection had an inferred opposite that could be entered without further selection being required eg; Aftermarket Bulk Pack Part Number reverse = Singular item from Aftermarket Bulk Pack would be stored as FieldA RelationshipType FieldB A9876 Bulk Pack of A12345 A12345 Item from Bulk Pack A9876 I was trying to show why 'Bulk' on it's own wouldn't be informative enough to use in both directions, but is also the reason the number of lookup options is so inflated, as the user needs to be able to choose either 'Bulk Pack of' or 'Item from Bulk Pack', the oppposite (it's ID stored as another field in the same table) could then be entered automatically as soon as the first selection was made. once again, thanks for your help, TonyT.. Sincerely, Fred |
#15
|
|||
|
|||
Parts table design question
Tony,
Sounds like you're on a good track. Not sure if there are any open questions, if so I'd be happy to help. One parting comment. Under the "Fred System" of recording relationships, in your your example: FieldA RelationshipType FieldB A9876 Bulk Pack of A12345 A12345 Item from Bulk Pack A9876 Your second record would be an unneeded duplication because this relationship can be defined as 1 one-way relationship. Having said that Dick likes Jane, whether or not Jane likes Dick is a separate question. But, having said that A9876 is a bulk pack of A12345, whether or not A12345 is an item form Bulk Pack A9876 is NOT a seperate question, it is a re-statement of the same question. Good luck! Fred "TonyT" wrote: Hi Fred, "Fred" wrote: Hello Tony, Answering your first and last question, having challenges databasing a fuzzy complex situaiton or in trying to explain it to someone else isn't and doesn't imply "unusual". What IS unusual is a situaiton or perceived situaiton where, in an industry, there is a universal set of rules for the middlemen's creation of their part numbers to the point where somebody can depend on decomposing their part numbers by the same universal set of rules and then so reliably derive data from the "pieces" of their part number that you can database pieces of their part number. Long story short, while you usually may be able to derive information from the middleman's part numbers, I would simply treat them as the middleman's whole part numbers. And the you will need to store their name and their part number, the combination of those two fields should be unique. I think you are mixing up 2 separate parts of my descriptions and examples (or more likely I'm not being clear enough) the prefixing issue is separate to the relationship problem. In my previous example Part number A12345 is NOT made up, it is the genuine Part number given to a part made by Briggs and Stratton, despite different Suppliers identifying any given part as having been made by Briggs and Stratton with a prefix (eg BS or BP or BR etc. etc. etc.) I can be certain that A12345 is A12345 - a genuine Briggs and Stratton Part - regardless of whether a sticker on it says BS-A12345 or BP-A12345, so don't need to store them as separate records, what I do need to know is which supplier puts BS in front of EVERY Briggs & Stratton part they supply and which Supplier uses BP instead. Second, my method DOES understand the "directionality" of those relationships, it just uses a different way to record it. If I perceive your way correctly, here's a comparison using an example. Let's say that there is some type of a liking relationship between Dick and Jane, you want to record it. Tony's method: Set up relationship choices of all of the possibilities and then enter 1 record which specifies it. Relevant relationship choices: Choice #1 A likes B, B doesn't like A Choice #2 B likes A, A doesn't like B Choice #3 They both like each other. And so Tony's method to record that they both like each other would be to enter one record: Field A Relationship Type FieldB Dick They Both Like Each Other Jane Fred's method would have only one relevant relationship choice: "A likes B" and would record "both like each other" by entering two records: FieldA RelationshipType FieldB Dick A likes B Jane Jane A likes B Dick Another misinterpretation I fear my entering ' reverse =' was just my way of ensuring that each selection had an inferred opposite that could be entered without further selection being required eg; Aftermarket Bulk Pack Part Number reverse = Singular item from Aftermarket Bulk Pack would be stored as FieldA RelationshipType FieldB A9876 Bulk Pack of A12345 A12345 Item from Bulk Pack A9876 I was trying to show why 'Bulk' on it's own wouldn't be informative enough to use in both directions, but is also the reason the number of lookup options is so inflated, as the user needs to be able to choose either 'Bulk Pack of' or 'Item from Bulk Pack', the oppposite (it's ID stored as another field in the same table) could then be entered automatically as soon as the first selection was made. once again, thanks for your help, TonyT.. Sincerely, Fred |
#16
|
|||
|
|||
Parts table design question
"Fred" wrote: Tony, Sounds like you're on a good track. Not sure if there are any open questions, if so I'd be happy to help. One parting comment. Under the "Fred System" of recording relationships, in your your example: FieldA RelationshipType FieldB A9876 Bulk Pack of A12345 A12345 Item from Bulk Pack A9876 Your second record would be an unneeded duplication because this relationship can be defined as 1 one-way relationship. Having said that Dick likes Jane, whether or not Jane likes Dick is a separate question. But, having said that A9876 is a bulk pack of A12345, whether or not A12345 is an item form Bulk Pack A9876 is NOT a seperate question, it is a re-statement of the same question. It is only needed as an option in the relationship lookup table to allow the user to select either; A is an item within Bulk pack B OR B is a multi pack of A. It seems to me that I should be storing both directions if I'm allowing selection of either? I suppose the only open question is, do you still think that this is the best approach now you know more of the issues involved? I'll post a new thread with my proposed table designs when I get round to them for your feedback. thanks again. TonyT.. |
#17
|
|||
|
|||
Parts table design question
Hello Tony,
"It is only needed as an option in the relationship lookup table to allow the user to select either; A is an item within Bulk pack B OR B is a multi pack of A. It seems to me that I should be storing both directions if I'm allowing selection of either?" In my opinion, no. Your searches/queries can follow a one-way relationship in either direction. "One way" is a context for the definition, not a travel restriction. Sincerely, Fred |
#18
|
|||
|
|||
Parts table design question
Hi again Fred,
I'm currently working thru a simplified idea that gives enough detail to be workable, but should be easier for the end user, when I have my thoughts together I'll post an overview in this thread for your feedback. cheers, TonyT.. "Fred" wrote: Hello Tony, "It is only needed as an option in the relationship lookup table to allow the user to select either; A is an item within Bulk pack B OR B is a multi pack of A. It seems to me that I should be storing both directions if I'm allowing selection of either?" In my opinion, no. Your searches/queries can follow a one-way relationship in either direction. "One way" is a context for the definition, not a travel restriction. Sincerely, Fred |
#19
|
|||
|
|||
Parts table design question
Hello Tony,
Sounds good. I think that what I have to offer on these types of questions is more helping with the thought / organizing / planning process than saying that a particular structure is the best one. You know your business a lot better than I do. Fred weith "TonyT" wrote: Hi again Fred, I'm currently working thru a simplified idea that gives enough detail to be workable, but should be easier for the end user, when I have my thoughts together I'll post an overview in this thread for your feedback. cheers, TonyT.. "Fred" wrote: Hello Tony, "It is only needed as an option in the relationship lookup table to allow the user to select either; A is an item within Bulk pack B OR B is a multi pack of A. It seems to me that I should be storing both directions if I'm allowing selection of either?" In my opinion, no. Your searches/queries can follow a one-way relationship in either direction. "One way" is a context for the definition, not a travel restriction. Sincerely, Fred |
|
Thread Tools | |
Display Modes | |
|
|