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

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

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

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.
(more on this below)

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?

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

"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.
(more on this below)

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

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

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

Tony,

See above, I attached my idea to your earlier post

Fred

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