A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Parts table design question



 
 
Thread Tools Display Modes
  #11  
Old August 20th, 2008, 08:31 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Parts table design question

Hello Tony,

A couple of thoughts that came to mind while reading your post, and then an
attempt to answer your question.

My background is more biased towards bieng the manufacturer (e.g. currently
fsinet.com) and making up / controlling our own part numbers, but I've had
some experience with databasing other people's part numbers.

By giving up the concept of unique part numbers, you are giving up a
lot....not sure I'd recommend caving so quickly on that one. One thing I've
had my folks do when databasing other people's part numbers where the "other
people" sre sloppy with them is to have two fields: Their part number and our
part number. 99.9% of the time we use their part number as our part number,
and in the rare casess where they sin, we can make our # different.

I really thought you'd end up wih 3 or 4 types of relationships, not 20, and
you still might want to try to do that. I might not have been clear what
I meant when I said "directional" I didn't mean making up 2 relationship
types for each relationship types, I just meant just considering the
relationship to be directional. For example, if Part#1 is a sub for part#2,
and Part #2 is a sub for part #2, you'd enter 2 records with the same
relationship type as follows:


#1 IsASubFor #2
#2 IsASubFor #1

I didn't understand what your desired goal was on your last question. Thile
I try to minimize "many-to-many" junction tables / relationships to absolute
necessities, one-to-many ones are no bigge. Also not sure whether my
"unique part number" intro is relevant to this. If it's jsut to record
that there are sever different potential suppliers for a given par number,
and to enable automatically adding their prefix, then I'd just add a
SupplierIDNUmber field to your parts table, and then make a Supplier tables.
Amongst fields to record whateve you want on them, I'd include:

SupplierIDNumber
SupplierSuffix.

And then link them on supplier IDNUmber.

Hope that helps a little.

Sincerely,

Fred

  #12  
Old August 21st, 2008, 11:10 AM posted to microsoft.public.access.tablesdbdesign
TonyT
external usenet poster
 
Posts: 154
Default Parts table design question

answers in-line again Fred

"Fred" wrote:

Hello Tony,

A couple of thoughts that came to mind while reading your post, and then an
attempt to answer your question.

My background is more biased towards bieng the manufacturer (e.g. currently
fsinet.com) and making up / controlling our own part numbers, but I've had
some experience with databasing other people's part numbers.

By giving up the concept of unique part numbers, you are giving up a
lot....not sure I'd recommend caving so quickly on that one. One thing I've
had my folks do when databasing other people's part numbers where the "other
people" sre sloppy with them is to have two fields: Their part number and our
part number. 99.9% of the time we use their part number as our part number,
and in the rare casess where they sin, we can make our # different.


The uniqueness of part numbers isn't due to mistakes by one
supplier/manufacturer, but comes as a result of the database user dealing
with perhaps 25 different manufacturers and it's possible that a Honda part
number might be the same as a Kawasaki part number for example. So either you
add in an arbitary manufacturer prefix, which you then have to remove &
replace with the suppliers arbitary prefix (as per my BS or BP example), or
use just the part number with another primary key. - see below for further
clarification of prefixes.


I really thought you'd end up wih 3 or 4 types of relationships, not 20, and
you still might want to try to do that. I might not have been clear what
I meant when I said "directional" I didn't mean making up 2 relationship
types for each relationship types, I just meant just considering the
relationship to be directional. For example, if Part#1 is a sub for part#2,
and Part #2 is a sub for part #2, you'd enter 2 records with the same
relationship type as follows:


#1 IsASubFor #2
#2 IsASubFor #1


Unfortunately this simplified approach won't give enough detail, a user
needs to know which is the bulk pack vs an item from it, or which is the
genuine or the pattern part. As you can see from the list of real-world
examples directionality is required to fully cover the, possibly compounded,
relationships between;
genuine, pattern, individual, bulk, assembly.


I didn't understand what your desired goal was on your last question. Thile
I try to minimize "many-to-many" junction tables / relationships to absolute
necessities, one-to-many ones are no bigge. Also not sure whether my
"unique part number" intro is relevant to this. If it's jsut to record
that there are sever different potential suppliers for a given par number,
and to enable automatically adding their prefix, then I'd just add a
SupplierIDNUmber field to your parts table, and then make a Supplier tables.
Amongst fields to record whateve you want on them, I'd include:

SupplierIDNumber
SupplierSuffix.

And then link them on supplier IDNUmber.


each supplier (or wholesaler NOT manufacturer) will use a different prefix
for each brand they stock, so supplier A uses BS-A12345 supplier B uses
BP-A12345 (for Briggs & Stratton), whereas supplier A uses HO-1234567-011-011
and Supplier B uses HP-1234567-011-011 for the same Honda Part. Just glanced
at 2 suppliers, one has 50 different prefixes for the range of machinery
brands they cover, whereas the second has perhaps 30 different prefixes for
the same machinery brands, plus the same prefix for different brands in quite
a few instances.
Hence the link table, as it can't be handled in a single supplier table.

But I want an order to supplierA to use the part numbers they understand &
an order to supplierB (for the same parts possibly) to use their part
numbers. Neither will be able to fulfill an order without knowing
manufacturer, so the prefix becomes part of the part number to these
suppliers and then has become inherited by their customers, who are my
customers :/

thanks again,

Is my situation really that unusual? I can't beleive it doesn't equate to
other trades, such as the motor trade or some other where the supplier is
usually a wholesaler rather than the manufacturer & there are pattern parts
available alongside genuine.

TonyT..


Hope that helps a little.

Sincerely,

Fred

  #13  
Old August 21st, 2008, 01:48 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Parts table design question

Hello Tony,

Answering your first and last question, having challenges databasing a fuzzy
complex situaiton or in trying to explain it to someone else isn't and
doesn't imply "unusual". What IS unusual is a situaiton or perceived
situaiton where, in an industry, there is a universal set of rules for the
middlemen's creation of their part numbers to the point where somebody can
depend on decomposing their part numbers by the same universal set of rules
and then so reliably derive data from the "pieces" of their part number that
you can database pieces of their part number.

Long story short, while you usually may be able to derive information from
the middleman's part numbers, I would simply treat them as the middleman's
whole part numbers. And the you will need to store their name and their
part number, the combination of those two fields should be unique.

Second, my method DOES understand the "directionality" of those
relationships, it just uses a different way to record it. If I perceive your
way correctly, here's a comparison using an example. Let's say that there
is some type of a liking relationship between Dick and Jane, you want to
record it.

Tony's method:

Set up relationship choices of all of the possibilities and then enter 1
record which specifies it.

Relevant relationship choices:

Choice #1 A likes B, B doesn't like A
Choice #2 B likes A, A doesn't like B
Choice #3 They both like each other.

And so Tony's method to record that they both like each other would be to
enter one record:

Field A Relationship Type FieldB
Dick They Both Like Each Other Jane


Fred's method would have only one relevant relationship choice: "A likes
B" and would record "both like each other" by entering two records:

FieldA RelationshipType FieldB
Dick A likes B Jane
Jane A likes B Dick


Sincerely,

Fred

  #14  
Old August 21st, 2008, 03:26 PM posted to microsoft.public.access.tablesdbdesign
TonyT
external usenet poster
 
Posts: 154
Default Parts table design question

Hi Fred,

"Fred" wrote:

Hello Tony,

Answering your first and last question, having challenges databasing a fuzzy
complex situaiton or in trying to explain it to someone else isn't and
doesn't imply "unusual". What IS unusual is a situaiton or perceived
situaiton where, in an industry, there is a universal set of rules for the
middlemen's creation of their part numbers to the point where somebody can
depend on decomposing their part numbers by the same universal set of rules
and then so reliably derive data from the "pieces" of their part number that
you can database pieces of their part number.

Long story short, while you usually may be able to derive information from
the middleman's part numbers, I would simply treat them as the middleman's
whole part numbers. And the you will need to store their name and their
part number, the combination of those two fields should be unique.


I think you are mixing up 2 separate parts of my descriptions and examples
(or more likely I'm not being clear enough) the prefixing issue is separate
to the relationship problem. In my previous example Part number A12345 is NOT
made up, it is the genuine Part number given to a part made by Briggs and
Stratton, despite different Suppliers identifying any given part as having
been made by Briggs and Stratton with a prefix (eg BS or BP or BR etc. etc.
etc.) I can be certain that A12345 is A12345 - a genuine Briggs and Stratton
Part - regardless of whether a sticker on it says BS-A12345 or BP-A12345, so
don't need to store them as separate records, what I do need to know is which
supplier puts BS in front of EVERY Briggs & Stratton part they supply and
which Supplier uses BP instead.

Second, my method DOES understand the "directionality" of those
relationships, it just uses a different way to record it. If I perceive your
way correctly, here's a comparison using an example. Let's say that there
is some type of a liking relationship between Dick and Jane, you want to
record it.

Tony's method:

Set up relationship choices of all of the possibilities and then enter 1
record which specifies it.

Relevant relationship choices:

Choice #1 A likes B, B doesn't like A
Choice #2 B likes A, A doesn't like B
Choice #3 They both like each other.

And so Tony's method to record that they both like each other would be to
enter one record:

Field A Relationship Type FieldB
Dick They Both Like Each Other Jane


Fred's method would have only one relevant relationship choice: "A likes
B" and would record "both like each other" by entering two records:

FieldA RelationshipType FieldB
Dick A likes B Jane
Jane A likes B Dick


Another misinterpretation I fear
my entering ' reverse =' was just my way of ensuring that each selection
had an inferred opposite that could be entered without further selection
being required eg;

Aftermarket Bulk Pack Part Number reverse = Singular item from
Aftermarket Bulk Pack


would be stored as

FieldA RelationshipType FieldB
A9876 Bulk Pack of A12345
A12345 Item from Bulk Pack A9876

I was trying to show why 'Bulk' on it's own wouldn't be informative enough
to use in both directions, but is also the reason the number of lookup
options is so inflated, as the user needs to be able to choose either 'Bulk
Pack of' or 'Item from Bulk Pack', the oppposite (it's ID stored as another
field in the same table) could then be entered automatically as soon as the
first selection was made.

once again, thanks for your help,

TonyT..



Sincerely,

Fred

  #15  
Old August 21st, 2008, 04:28 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Parts table design question

Tony,

Sounds like you're on a good track. Not sure if there are any open
questions, if so I'd be happy to help.

One parting comment. Under the "Fred System" of recording relationships, in
your your example:

FieldA RelationshipType FieldB
A9876 Bulk Pack of A12345
A12345 Item from Bulk Pack A9876

Your second record would be an unneeded duplication because this
relationship can be defined as 1 one-way relationship. Having said that Dick
likes Jane, whether or not Jane likes Dick is a separate question. But,
having said that A9876 is a bulk pack of A12345, whether or not A12345 is an
item form Bulk Pack A9876 is NOT a seperate question, it is a re-statement of
the same question.

Good luck!

Fred




"TonyT" wrote:

Hi Fred,

"Fred" wrote:

Hello Tony,

Answering your first and last question, having challenges databasing a fuzzy
complex situaiton or in trying to explain it to someone else isn't and
doesn't imply "unusual". What IS unusual is a situaiton or perceived
situaiton where, in an industry, there is a universal set of rules for the
middlemen's creation of their part numbers to the point where somebody can
depend on decomposing their part numbers by the same universal set of rules
and then so reliably derive data from the "pieces" of their part number that
you can database pieces of their part number.

Long story short, while you usually may be able to derive information from
the middleman's part numbers, I would simply treat them as the middleman's
whole part numbers. And the you will need to store their name and their
part number, the combination of those two fields should be unique.


I think you are mixing up 2 separate parts of my descriptions and examples
(or more likely I'm not being clear enough) the prefixing issue is separate
to the relationship problem. In my previous example Part number A12345 is NOT
made up, it is the genuine Part number given to a part made by Briggs and
Stratton, despite different Suppliers identifying any given part as having
been made by Briggs and Stratton with a prefix (eg BS or BP or BR etc. etc.
etc.) I can be certain that A12345 is A12345 - a genuine Briggs and Stratton
Part - regardless of whether a sticker on it says BS-A12345 or BP-A12345, so
don't need to store them as separate records, what I do need to know is which
supplier puts BS in front of EVERY Briggs & Stratton part they supply and
which Supplier uses BP instead.

Second, my method DOES understand the "directionality" of those
relationships, it just uses a different way to record it. If I perceive your
way correctly, here's a comparison using an example. Let's say that there
is some type of a liking relationship between Dick and Jane, you want to
record it.

Tony's method:

Set up relationship choices of all of the possibilities and then enter 1
record which specifies it.

Relevant relationship choices:

Choice #1 A likes B, B doesn't like A
Choice #2 B likes A, A doesn't like B
Choice #3 They both like each other.

And so Tony's method to record that they both like each other would be to
enter one record:

Field A Relationship Type FieldB
Dick They Both Like Each Other Jane


Fred's method would have only one relevant relationship choice: "A likes
B" and would record "both like each other" by entering two records:

FieldA RelationshipType FieldB
Dick A likes B Jane
Jane A likes B Dick


Another misinterpretation I fear
my entering ' reverse =' was just my way of ensuring that each selection
had an inferred opposite that could be entered without further selection
being required eg;

Aftermarket Bulk Pack Part Number reverse = Singular item from
Aftermarket Bulk Pack


would be stored as

FieldA RelationshipType FieldB
A9876 Bulk Pack of A12345
A12345 Item from Bulk Pack A9876

I was trying to show why 'Bulk' on it's own wouldn't be informative enough
to use in both directions, but is also the reason the number of lookup
options is so inflated, as the user needs to be able to choose either 'Bulk
Pack of' or 'Item from Bulk Pack', the oppposite (it's ID stored as another
field in the same table) could then be entered automatically as soon as the
first selection was made.

once again, thanks for your help,

TonyT..



Sincerely,

Fred

  #16  
Old August 21st, 2008, 04:42 PM posted to microsoft.public.access.tablesdbdesign
TonyT
external usenet poster
 
Posts: 154
Default Parts table design question



"Fred" wrote:

Tony,

Sounds like you're on a good track. Not sure if there are any open
questions, if so I'd be happy to help.

One parting comment. Under the "Fred System" of recording relationships, in
your your example:

FieldA RelationshipType FieldB
A9876 Bulk Pack of A12345
A12345 Item from Bulk Pack A9876

Your second record would be an unneeded duplication because this
relationship can be defined as 1 one-way relationship. Having said that Dick
likes Jane, whether or not Jane likes Dick is a separate question. But,
having said that A9876 is a bulk pack of A12345, whether or not A12345 is an
item form Bulk Pack A9876 is NOT a seperate question, it is a re-statement of
the same question.


It is only needed as an option in the relationship lookup table to allow the
user to select either; A is an item within Bulk pack B OR B is a multi pack
of A. It seems to me that I should be storing both directions if I'm allowing
selection of either?

I suppose the only open question is, do you still think that this is the
best approach now you know more of the issues involved?

I'll post a new thread with my proposed table designs when I get round to
them for your feedback.

thanks again.

TonyT..
  #17  
Old August 21st, 2008, 05:09 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Parts table design question

Hello Tony,

"It is only needed as an option in the relationship lookup table to
allow the
user to select either; A is an item within Bulk pack B OR B is a multi pack
of A. It seems to me that I should be storing both directions if I'm allowing
selection of either?"

In my opinion, no. Your searches/queries can follow a one-way relationship
in either direction. "One way" is a context for the definition, not a travel
restriction.

Sincerely,

Fred



  #18  
Old August 21st, 2008, 09:11 PM posted to microsoft.public.access.tablesdbdesign
TonyT
external usenet poster
 
Posts: 154
Default Parts table design question

Hi again Fred,

I'm currently working thru a simplified idea that gives enough detail to be
workable, but should be easier for the end user, when I have my thoughts
together I'll post an overview in this thread for your feedback.

cheers,

TonyT..

"Fred" wrote:

Hello Tony,

"It is only needed as an option in the relationship lookup table to
allow the
user to select either; A is an item within Bulk pack B OR B is a multi pack
of A. It seems to me that I should be storing both directions if I'm allowing
selection of either?"

In my opinion, no. Your searches/queries can follow a one-way relationship
in either direction. "One way" is a context for the definition, not a travel
restriction.

Sincerely,

Fred



  #19  
Old August 22nd, 2008, 02:57 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Parts table design question

Hello Tony,

Sounds good. I think that what I have to offer on these types of questions
is more helping with the thought / organizing / planning process than saying
that a particular structure is the best one. You know your business a lot
better than I do.

Fred




weith
"TonyT" wrote:

Hi again Fred,

I'm currently working thru a simplified idea that gives enough detail to be
workable, but should be easier for the end user, when I have my thoughts
together I'll post an overview in this thread for your feedback.

cheers,

TonyT..

"Fred" wrote:

Hello Tony,

"It is only needed as an option in the relationship lookup table to
allow the
user to select either; A is an item within Bulk pack B OR B is a multi pack
of A. It seems to me that I should be storing both directions if I'm allowing
selection of either?"

In my opinion, no. Your searches/queries can follow a one-way relationship
in either direction. "One way" is a context for the definition, not a travel
restriction.

Sincerely,

Fred



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.