Database Schema for Document Revision 1

[zandsc1]

I have a schema that I'm trying to normalize:

Component(component_id, component_number, component_revision,...)

Component_lot(component_lot_id, component_lot_number, component_number, component_revision,...)

Component_supplier(component_supplier_id, component_supplier_name, component_number)

Ideally I'd like to get rid of the component_number in both the component_lot and component_supplier tables, as well as the component_revision in the component_lot table, and just use component_id.

The problem is that when the component revision is changed, I have multiple entries in the component table for the same component number, and I have to find a good way to determine which revision is active (it's not a simple 1, 2, 3 numbering system that I can sort by, and it's possible that a new revision could exist but not be active yet). Additionally, if I create a new revision of a component, it gets a new id and now I have to establish the supplier for the new revision of that component in component_supplier.

I've considered making an isActive field in component, but that doesn't seem like an effective solution to the problem. I've also considered keeping component_number as the fk in component_supplier, but there are instances where the component number could change (rare, but possible).

Any advice or links to articles would be appreciated.

 

[denimined]

I think that you need to break the revision from the component # in order to get this to work. The second part is to put a link to the revision table for the lot table.

C: Component - CID(PK), C#.
CR: Component/Revision - CRID(PK), CID(FK), Revision, Active
CL: Component/Lot - CLID(PK), CL#, CID(FK), CRID(FK)
CS: Component/Supplier - CSID(PK), CSName, CID(FK)

When a lot - or group of lots - need to change the revision they point to (for a given component), it is just a blanket change of one foreign key to a new one.

 

[zandsc1]

Ah very interesting denimined. That does solve some issues. I may be able to simplify further and just reference the CRID in the CL table, since that CRID has a FK pointing to the CID, and the CRID for a given lot should never change (once the lot number is created for a specific rev of a component, it doesn't change).

Thanks!