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

 

[Fred]

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?

 

[Dale Fye]

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]

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

 

[TonyT]

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.

 

[Fred]

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 are:

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

 

[Fred]

Tony,

See above, I attached my idea to your earlier post

Fred

 

[TonyT]

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]

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

 

[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