1

I'm making a sample database to test myself in MySQL workbench and was wondering the following:

I have five tables, Customer, Product, Stock(Quantity), Warehouse and Purchase Order.

For primary keys I have customerId, productId, warehouseId, and invoiceNo...but what would the stocks primary key be? Apologies if the answer is obvious, but as I understand it the foreign key would be the productNo.

Very new to this!

Any input would be greatly appreciated!

Improve this question
8
  • 1
    is there a 1 to one relationship between a product and a stock? If so why it a different table? – danblack Apr 25 '20 at 7:16
  • hi dan, it was a different table just to make it a bit more complex, for the learning process, 1 to 1 yes, If I'm correct in saying that means every one product has one stock attribute and vice versa? – Shane Apr 25 '20 at 7:26
  • these are my attributes for this example if it helps: CUSTOMER: customerNo, firstName, lastName, emailAddress, phoneNumber PRODUCT: productCode, name, description, price ORDER: orderID, customerNo, productCode, comment STOCK: productCode, warehouseCode quantity WAREHOUSE: warehouseCode, phoneNumber, address – Shane Apr 25 '20 at 7:26
  • 1
    Putting addition information in the question (especially when large) is usually desirable. SHOW CREATE TABLE {tablename} also recommended for readability. Welcome to DBA. – danblack Apr 25 '20 at 7:33
  • Thanks very much, would you mind if I asked one further question? I said id start light with three tables, customers, purchase order and product... If for example, a customer can have many orders, but an order can only be placed by one customer, are those multiplicities 1.* and 0.1 respectively? I'll assume for the sake of this example that my order and product are many to many. – Shane Apr 25 '20 at 8:23
1

Given with STOCK the same product could be multiple warehouses, the combined (productCode, warehouseCode) is the unique element for this table and that should be the primary key.

Improve this answer
1
  • thats great thank you! – Shane Apr 25 '20 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.