Table Relationships

[TC]

I need help designing a relationship between three tables.
see below.

PartsTable

Looks good.

VersionsTable
-------------
VersionID
PartNumber(Foreign Field)

Partstable and the VersionsTable have a one to many
relationship using the Partnumber Field.

If a part can have many versions, then, PartNumber and VersionID together
constitute a so-called "composite" (multi-field) primary key. It would be
good to mark them so. Personally, I like to put the "one" field first, & the
"many" field second - not that it makes any difference. So:

PartNumber ( composite )
VersionID ( primary key )

I've re-arranged the rest of your post, in order to answer your questions in
a logical sequence.

What I would like is when a user selects a partnumber to
automatically have the user choose the Part Version.

That is a user-interface issue. Just write your form(s), to make it so. Your
table design already lets a part have many versions.

Note not all Parts have Version data.

This seems to contradict what you say above: that when the user selects a
part number, they must automatically choose the part version. In any case,
this just means that part-to-version is what I call an "optional" 1-to-many
(ie. a "1 to 0-or-more"), not as "mandatory" 1-to-many (ie. 1 to
1-or-more").

I want to introduce a new table called ReturnedPartsTable

ReturnedPartsTable
------------------
RpTablesID
PartNumber
?
How do I relate the VersionsTable with the ReturnedPartsTable.
Should I completely eliminate the relationship between the PartsTable
and the ReturnedPartsTable and make the link to the Versionstabel?

Well, no-one can really answer that, unless you say (a) what that table
contains - what does a row in that table represent?, and (b) what do you
propose is the primary key of that table?

You< know what you mean by "returned parts" - but we don't - so you'll have

to tell us, before we can comment further.

HTH,
TC

 

[Stan]

I need help designing a relationship between three tables.
see below.

PartsTable
----------
PartNumber (PrimaryIndex)
PartDesc
etc...

VersionsTable
-------------
VersionID
PartNumber(Foreign Field)

Partstable and the VersionsTable have a one to many
relationship using the Partnumber Field.

I want to introduce a new table called ReturnedPartsTable

ReturnedPartsTable
------------------
RpTablesID
PartNumber
?


What I would like is when a user selects a partnumber to
automatically have the user choose the Part Version. How
do I relate the VersionsTable with the ReturnedPartsTable.
Note not all Parts have Version data. Should I completely
eliminate the relationship between the PartsTable and the
ReturnedPartsTable and make the link to the Versionstabel?

 

[Stan]

Thanks for the reply TC.
Let me clarify the application. The ReturnedPartsTable
will track defective parts that are returned for repairs.
The table has other fields such as fault type, returnedate
etc...

When the defectivepart is received at our warehouse I want
to document the Partnumber and the version in the
ReturnedPartsTable.

What is a good database design, should I have at least 1
version for each part and relate the VersionsTable with
the ReturnedpartsTable? or is it a better idea for me to
relate the ReturnedPartstable to both the Partstable and
versionstable?

Stan

 

[TC]

The solution will depend on whether yo need to track the serial # or other
unique identifying characteristic of the actual, physical part.

So, do you want to record the fact that "version 3 of part #123" was
returned - or that >serial 12345< of version 3 of part #123 was returned?

The answer to that question will determine the primary key of the
returned-parts table. If you do not need to know the serial #s, the PK of
that table will be a composite (multi-field) key comprising part # plus
version #. If you need to track the serial number, you'd add serial # to the
key as well.

Then, personally, I would relate part # + version # from the returned-parts
table to the same two fields in the version table. That is, one relationship
comprising two fields - not two relationships of one field each.

HTH,
TC