Need help with Normalization please?

[DeeDee]

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

 

[DeeDee]

Anyone please?

 

[jzwp22]

An inventory system is one of the more challenging applications to build. I would first recommend looking at Allen Browne's site and his discussion of determining Quantity-on-hand. On the site, Allen has a basic table structure.

Another approach is to use a transaction table setup where any additions or subtractions from inventory are recorded in 1 table. You would probably still need a table to record data for physical inventories similar to Allen's tblstocktake table

tblStockTransactions
-pkStockTransID primary key, autonumber
-dteTrans (date field)
-fkProductID
-Qty (+ for additions to, - for removal from inventory)

Regarding some of your questions, since a purchase order can cover many products, you would need the table PurchaseOrderProducts. The purchase order table would be equivalent to Allen's tblAcq and the PurchaseOrderProducts would be equivalent to Allen's tblAcqDetail.

I'm not sure what all of the fields represent in your products and inventory tables. Could you explain further?

 

[DeeDee]

Hi, thanks for responding. I am currently on Allen Browne's site and will have a look some more.

I initially had a stocktransaction table and was advised not to use it because it will just make the application more difficult. And maybe that is why I cannot seem to go any further because my mind sees the transaction table.

Initially when I sat down and did the normalization I only had an inventory table. The tables I had were as follows:
tblSupplier
tblPurchaseOrders
tblInventory
tblCategory

This information I took from the invoice and delivery note that the supplier give to the restaurant. I ideally want to keep it small and simple because right now its a complete manual system only and they use a commercial Excel software for Accounting Purpose. Once I have this completed I am hoping to when I have a little more time to add a Employee Database and Customer Contact List for a Birthday Club onto it.

After doing some more investigation into Stock Control Systems I added the tblProducts.

The fields in Products relate to the details for the drinks. The category field is for example Wines, Beers, Soft drinks, etc. The min and max number they can order, when they need to order by and if they can order multiple cases.

The Inventory table has again a category field (my first error) ABV is in percentage and that is Alcohol By Volume (It is written on all their documentation). Then I have the case and unit quantity. The Net Value of each item with the VAT code. The reorder level and also target reorder level and a check box for whether the item has been discontinued.

 

[jzwp22]

I've never had to create an inventory system, so I'm not sure which method would be better, but I would guess that having 1 transaction table would be easier with which to work. You would of course need the main information about a transaction since the tblStockTransactions holds the detail records. Just brainstorming a little, I would have a table that holds the basic info about the transaction

tblTransaction
-pkTransID primary key, autonumber
-fkCompanyID foreign key to tblCompany (holds all customers and suppliers)
-dteTrans transaction date
-fkTransTypeID foreign key to tblTransType
-txtOrderNumber


tblTransType (2 records initially: Order and Sale)
-pkTransTypeID primary key, autonumber
-txtTransType

Now we can incorporate the transaction details with some modifications

tblStockTransactions
-pkStockTransID primary key, autonumber
-fkTransID foreign key to tblTransactions
-fkProductID
-Qty

You probably do not need to this: (+ for additions to, - for removal from inventory), since the type of transaction (fkTransTypeID) determines whether there is an addition (order) to or removal (sale) from inventory. You would just record the quantity with positive numbers.

Another thing that I thought of that might impact your table structure is whether a particular product can be purchased from multiple vendors. Can that occur? If so, these fields might be impacted if they vary by vendor: min and max number, order by, and multiple cases

 

[DeeDee]

for tblTransaction would or can fkCompanyID just be a foreign key to SupplierID because as mentioned before I will only have tblSupplier and no Customer table at all.

tblTransType - Can I have that witout the Sale. Because what the client would do is order stock in and when they stock take they will then decrease the value. Like I said its a restuarant that charge drinks separately and then fixed prices for buffet style meals.

Right now all their other stock are bought from multiple suppliers. But their requests for this inventory is to be able to have multiple suppliers and that is why I have a separate tblSupplier.

 

[jzwp22]

for tblTransaction would or can fkCompanyID just be a foreign key to SupplierID because as mentioned before I will only have tblSupplier and no Customer table at all.

Sure you can use fkCompanyID to refer to supplier. I just left it generic so that it could relate to either supplier or customer (all would be records in the company table). If you are only concerned with suppliers then that is all that you would have in the company table (feel free to rename the tables as necessary for your application).

tblTransType - Can I have that witout the Sale.

Absolutely, the structure I proposed allows for future changes if necessary; again, I was just trying to make it generic, so if others come upon this thread they might find that it would meet their needs.

 

[DeeDee]

Ok thanks for that.

But in general do you think that the normalization is correct and obviously taken into account the new tables you provided and without adding any additional tables.

 

[jzwp22]

In your products table as shown below, one might say that the min/max fields are not normalized since they represent 2 levels of the product (a one-to-many relationship)

PRODUCTS
product_id
category
product_description
order_by
order_multiple
qty_per_unit
minimum
maximum
product_notes

I also noticed that your products are not tied to a specific supplier, does that mean that multiple suppliers can supply the product in exactly the same quanitities? What a about cost? Will one supplier charge the same for a product as another supplier?

 

[DeeDee]

Hi, I think it might have been another error on my side because like I said I initially only had the Inventory table and that is linked to supplierID and maybe products table should be linked too?

What would you suggest I do re: min and max values for some reason I cannot see what I could do further with that.

 

[jzwp22]

For the products table with respect to the min/max

PRODUCTS
product_id
category
product_description
order_by
order_multiple
qty_per_unit
product_notes

tblProductsInventoryControlLevels
-pkProdInvControlLevID primary key, autonumber
-fkProductID foreign key to PRODUCTS table
-longLevel (field to hold the numerical value)
-txtLevelType (text field to hold Maximum or minimum)

With respect to products and suppliers, if a product can only be supplied by 1 supplier then this would be the appropriate structure

PRODUCTS
product_id
category
product_description
order_by
order_multiple
qty_per_unit
product_notes
fkCompanyID foreign key to tblCompany (or tblSuppliers in your case)

If a product can be supplied by many suppliers, then this would be the appropriate structure

tblProductSuppliers
-pkProductSuppliersID primary key, autonumber
-fkProductID foreign key to PRODUCTS table
-fkCompanyID foreign key to tblCompany (or tblSuppliers in your case)


Now for things that change relative to the product/supplier combination, you would include those fields in tblProductSuppliers. For example, let's say that supplier 1 sells Product A but so does supplier 2, but the 2 suppliers charge different amounts. You would capture the cost in tblProductSuppliers

tblProductSuppliers
-pkProductSuppliersID primary key, autonumber
-fkProductID foreign key to PRODUCTS table
-fkCompanyID foreign key to tblCompany (or tblSuppliers in your case)
-currCost

Now, the other caveat to this is does the company you are preparing this database for track the products irregardless of the supplier or do they track the product differently if supplied by more than one vendor?

Now how are you going to handle price changes? Do you need to keep track of price changes over time?

 

[gemma-the-husky]

at what level are you doing this

eg - by bottle is one thing - but if you are trying to track sales of shorts, then it will be very tricky anyway.

 

[DeeDee]

Hi

Again thanks for all the responses, this look now so much more complicated than I thought. Hopefully I am following you correctly.

To answer your questions.
Regards to tracking products, they did not really go into too much specifics, the main purpose was just to have a database with all alcoholic drinks, etc that can alert when to reorder.

Again no mention of price changes were ever mentioned and they specified as long as they are able to add multiple suppliers.

Gemma

it would be by bottle, like I said before as drinks are consumed it wont be tracked but will be tracked on a weekly basis i.e. products coming in, amount left and how much to reorder.

 

[jzwp22]

You're welcome. Please post back if you have any other questions regarding the table structure.