relationship anomoly

A

Alex

I have been stuck on this for a few days now and its
really starting to bother me!

I have the following tables,
Issue, Owner, Area, Priority, Part, Supplier. I need to
relate these so that
One issue has one part - which can have many suppliers for
one part.
Each issue has only one area, priority, part, one owner
etc.
is it possible to have a function to clock repeating
issues?
the main problem is how to join these tables, and what, if
any new tables to add?(eg, joiner many-many tables)

Any help v.much apreciated

Alex Marchant
 
J

Jeff Boyce

Alex

It sounds like you are saying that an Issue has attributes of "part",
"area", "priority", "owner", ..., but a Part can have one-to-many suppliers.

I'm trying to imagine, however, how a single part related to an issue could
have more than one supplier...

If you can have a part (?a tire, say) that can have many suppliers, and each
supplier can supply many parts, you need a "resolver" table that associates
parts with suppliers.

As for "a function to clock repeating issues", I have no idea what you mean
by that...
 
A

Alex Marchant

thanks for the reply jeff,

hopefully i can furthur explain :-

an Issue has only One problem part at a time
This problem part could have been supplied by many
suppliers, so need tables to show suppliuer details, part
details and if so, what supplier supplies what part.

Issues attributes include:-
what area the issues in
What proirity an issue is
The owner
Date
Description
Part Number
Part Desc

I want to get the details which will repeat in other
tables to normalise the db, but my normalisation skills
are a little weak!

Thanks for your time, any more help would be great

p.s - what i meant by clocking is that sometimes repeat
issues arise, which means i need a field to count repeats,
maybe something which increments when a button is clicked
or automatically changes when a form is opened.
 
J

Jeff Boyce

Alex

How can one problem part be supplied by more than one supplier? I
understand that a general part (tires) could be supplied by more than one,
but how can THIS SPECIFIC part (tire) be supplied by more than one?

Given a specific part number, how can this have come from more than one
source?

From your description, you probably don't need to include the Part
Description in the Issue table.

Here's a first rough cut at a table structure, given that I still don't have
a very clear picture of the data/domain you're working in...

tlkpOwner (a lookup table of owners)
OwnerID
OwnerName
... (other owner-specific info)

tlkpPart (a lookup table of parts - generic)
PartID
PartTitle
PartDescription

tlkpSupplier (a lookup table of suppliers)
SupplierID
SupplierName
SupplierAddress
... (more facts about a specific supplier)

trelPartSupplier
PartSupplierID
PartID
SupplierID
SupplierPartNumber (assuming each supplier can number/name a part
differently, i.e., a different SKU)

tlkpIssueArea (a lookup table of valid issue areas)
IssueAreaID
IssueAreaTitle
IssueAreaDescription

trelIssue
IssueID
IssueAreaID (foreign key - see above)
OwnerID (foreign key - see above)
PartSupplierID (foreign key - see above)
IssueDate (don't use "Date" -- it's a reserved word in Access)
IssueTitle (a short version)
IssueDescription (a long version)

I hope that comes close to what you're working with...
 
A

Al

your an absolute legend Jeff, that is basically what i
already had but its good to know i was on the right track.
I didn't have a primary key in supplier as i had a many-to-
many table.

Your right, one specific part cannot come from two
suppliers, One part can be supplied by different
suppliers, so a tire can come from supplier 1 or
supplier2, not both at once as i may have made out!

i think my problem is that each different supplier gives
the parts the SAME part number...

Thanks again for your help

Alex Marchant
 
Top