Is this a stupid relational method?

[BDibz]

Is it stupid to have a table that is essentially just a list of parts as the Primary Key, and then use these parts in the Inventory and Jobs tables?

I don't even really need the partName field, so we can ignore that for the purposes of this question.



It seems to be that this should be simplified, then, in some way; but it doesn't not work this way. So is it bad practice? If so, why?

[ranman256]

you dont need the relationship.
the tParts would be a lookup table to fill the other tables.

to me, the tParts table would also be the inventory. Why have 2 tables of parts?
tPartID
tPartName
Qty


you DO want 1toMany relations with ownership...
like 1 client has many Invoices.

for yours I think:
1 Job uses many parts picked from the tParts table. tParts is used to fill it in.
(some folks may disagree, but thats me)

[isladogs]

In general, tables with a PK field and only one other field are superfluous unless used as a junction table to avoid many to many joins.
If that's not what you have, it can be merged with tblInventory.

EDIT sorry - just realised ranman and I are saying much the same thing.

[BDibz]

to me, the tParts table would also be the inventory. Why have 2 tables of parts?

Sorry, I should clarify the situation as its a bit unusual.

Its a manufacturing scenario; each Job is an order for a quantity of one type of part.

The inventory is for electrodes; these are used to produce parts. The purpose of this DB is to determine what electrodes need to be ordered based on the monthly job forecast. So we have an ongoing database of parts. Then a new job is input with the part name and quantity. Then a query checks the available electrode inventory vs what electrodes will be needed.

So each electrode is used for just one part; each part is created by many electrodes.
Then each job can request one part, but one part can be requested by multiple jobs.

This is why it is set up as it is.

[BDibz]

In general, tables with a PK field and only one other field are superfluous unless used as a junction table to avoid many to many joins.
If that's not what you have, it can be merged with tblInventory.

EDIT sorry - just realised ranman and I are saying much the same thing.

Inventory refers to Electrodes on hand, a separate object from Parts. I think if anything, I could merge parts with jobs, since each job is an order for parts. However, multiple jobs can order the same parts, and I want to maintain relational integrity with the part names in case I have to update them.

[ArviLaanemets]

Instead of table tblInventory, you need another one, let's name it tblStorage, where you register incomings and outgoings for all parts. P.e.
tblStorage: StorageID, PartID, MovementDate, MovementType, MovementQty

where MovementType has values indicating arrival of purchased parts and sending parts into production. You can also have other movement types defined, like inventory correction, or scrapping, or selling out, or arrival products from production, etc.
MovementQty will be positive or negative depending on movement direction, or always positive with movement type defining the direction.

You can have an inventory table with this structure too, but there you store quantities of all parts in store at certain dates (p.e. yearly inventory). So you don't need to sum all movements over all history to get the quantity in store - instead you take quantities from last inventory, and add the sum of all movements after last inventory.