Parts table design question

[TonyT]

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

 

[Fred]

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]

Hi Fred,

thanks for response, answers in-line.

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

 

[TonyT]

answers in-line again Fred

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..

 

[Fred]

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

 

[TonyT]

Hi Fred,

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..

 

[Fred]

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]

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..

 

[Fred]

Hello Tony,

Re: "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

 

[TonyT]

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]

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