Manufacturing database containing multiple serial numbers per part

[Pommetje77]

Hello all,

I am busy designing a manufacturing database containing multiple serial numbers per product, however I am stuck on a particular situation and get the feeling of canceling the project.
What I like to have is a database where I can pre-add serial numbers for a particular item. The items (same item number, different serial number) will follow different machining steps and each machining step will be approved by a documentation package which have a unique documentation package number. So the unique documentation package number shall be linked to the serial number. However I will end with 3 or 4 documentation package numbers (because of the various machining steps) Each documentation package number shall be linked to the serial number.
Additionally, per machining step I can have various non-conformances for each serial number, this non-conformance report numbers shall be linked to the serial number as well.

I have the following tables:

Items
ID
Part number
Description

Serialization
ID
Serial number
Batch number
Drawing revision

Purchasing Order
ID
PO number
Supplier
Quantity
Item

Documentation Package
ID
DP-number
Posting date
approval date
Approver

Non-Conformances
ID
NCR number
Part number
Serial number
description of the NC
Disposition of the NC
log date
closing date


In addition to the above, I would like to add serial numbers 1 time and don't want to have double numbers in the system. The items are pre-added and can be chosen by combo-box.
Per machining step, I have 1 purchasing order (so probably this can help with linking the data).

I've tried lots of things and many times it looked like I was almost there and then it gave me the message of having duplicated numbers somewhere and I could not proceed.

Hoping someone can help me in this or would give me a good start.



Thanks in advance
Ruud

[ranman256]

Then your table, below does not need ID. Make the SN the key, this will prevent duplication.

Serialization
ID
Serial number

[Pommetje77]

Hello Ranman256, thanks for your reply!
This is a very good start, thank you!
However, each Purchasing order can consist of a range of serial numbers.
for example,
Purchasing order P001 contains serial 0001ABC, 0002ABC, 0003ABC and 0004ABC of Item 1000AB2.
Then items 1000AB2 with serial numbers 0001ABC, 0002ABC, 0003ABC and 0004ABC are send to supplier 2 under purchasing number P002.

So the added serials of P001 shall be linked even to PO002 and supplier 2.
I cannot get this working.

waiting for your soonest reply!
Ruud