Topic: General question on database schema
Hello again,
I'm about to start on my BIG order system project, and before I go too much further I thought I'd just sound out other peoples thoughts.
I have the problem that an item let's call it 'MyWidget' can be purchased from more than one supplier and therefore each supplier will have a different order code.
I think I've solved this by having a table called 'product_supplier' which consists of a foreign key supplierID from the suppliers table and a similar ID from the products table and the other entry will be the 'supplier part number'.
Now when a new product is sourced, the products table is filled with the product details and FK_ID's to the part_suppliers_table and the supplier part number to the product_supplier table.
Does this sound the correct way to account for MyWidget being available from multiple suppliers?
My other question is, I need to be able to account for 'backorders or To Follows', which is going to mean only a part quantity of an item has been delivered. Would it be best to create a 'backorder' table or just use a calculated field to work out quantity received - quantity ordered?
Any thoughts?
I've attached the database schema for reference.