Hi All
I am fairly new to database design (a student but currently taking a break due to new baby) but am trying to help a friend who owns a restaurant. I am trying to create an alcohol inventory database. Their will be no customer table.
I have done a few tables but am sure before I can continue that there are some table or fields missing
SUPPLIER
supplier_id
supplier_name
attention
supplier_account_number
supplier_address 1
supplier_address 1
supplier_town
supplier_county
supplier_post_code
supplier_email_address
supplier_phone_number
supplier_fax_number
supplier_web_page
supplier_notes
PURCHASE ORDERS
purchase_order_number
purchase_order_date
supplier_id
payment_due_date
inventory_code
PRODUCTS
product_id
category
product_description
order_by
order_multiple
qty_per_unit
minimum
maximum
product_notes
INVENTORY
inventory_code
inventory_name
inventory_description
category_id
abv
case_qty
unit_qty
case_per_unit_price
net_value
vat_code
reorder_level
target_inventory_level
discontinued
inventory_comments
CATEGORY
CategoryID
Category
I would just love some comments to make sure I am on the right track so that I can continue with the database. I bought a brand new book on databases and in there are a few sample databases and because most of them have a sale inventory side to it, it therefore has customer fields and separate Inventory and Stock tables. Do I have to have a separate Inventory and Stock Table with details as follows:
INVENTORY
product_id
location
cost
qty_in_stock
qty_on_hand
STOCK
supplier_id
product_id
cost
date_received
qty
qty_allocated
qty_remaining
Also their Category Table only has category in it not category and category ID
Must I also have a Supplier Invoices table. And because I have a Products and Purchase Order table, do I therefore have to have a separate PurchaseOrderProducts table or will this only be the case if it was a normal sales database. A friend of mine that has a programming background suggested this so not too sure. I do not want to make it to intricate but also do not want to miss out too many things.
Any critique is greatly appreciated.
Thanks
I am fairly new to database design (a student but currently taking a break due to new baby) but am trying to help a friend who owns a restaurant. I am trying to create an alcohol inventory database. Their will be no customer table.
I have done a few tables but am sure before I can continue that there are some table or fields missing
SUPPLIER
supplier_id
supplier_name
attention
supplier_account_number
supplier_address 1
supplier_address 1
supplier_town
supplier_county
supplier_post_code
supplier_email_address
supplier_phone_number
supplier_fax_number
supplier_web_page
supplier_notes
PURCHASE ORDERS
purchase_order_number
purchase_order_date
supplier_id
payment_due_date
inventory_code
PRODUCTS
product_id
category
product_description
order_by
order_multiple
qty_per_unit
minimum
maximum
product_notes
INVENTORY
inventory_code
inventory_name
inventory_description
category_id
abv
case_qty
unit_qty
case_per_unit_price
net_value
vat_code
reorder_level
target_inventory_level
discontinued
inventory_comments
CATEGORY
CategoryID
Category
I would just love some comments to make sure I am on the right track so that I can continue with the database. I bought a brand new book on databases and in there are a few sample databases and because most of them have a sale inventory side to it, it therefore has customer fields and separate Inventory and Stock tables. Do I have to have a separate Inventory and Stock Table with details as follows:
INVENTORY
product_id
location
cost
qty_in_stock
qty_on_hand
STOCK
supplier_id
product_id
cost
date_received
qty
qty_allocated
qty_remaining
Also their Category Table only has category in it not category and category ID
Must I also have a Supplier Invoices table. And because I have a Products and Purchase Order table, do I therefore have to have a separate PurchaseOrderProducts table or will this only be the case if it was a normal sales database. A friend of mine that has a programming background suggested this so not too sure. I do not want to make it to intricate but also do not want to miss out too many things.
Any critique is greatly appreciated.
Thanks