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, 2020 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, 2020 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, 2020 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, 2020 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, 2020 at 8:23

1 Answer 1

Reset to default
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, 2020 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.