Database design help

[FurryGorilla]

Hi all

Just need some advice on a database I'm designing. It's for a carpet company that wants to keep track of Customers, Suppliers, Orders and Stock. The only thing is, I am running into a slight problem with the stock tables.

Basically this is a simplified version of what I have so far:

tblSuppliers
SupplierID, Name, Address, PostCode

tblCustomers
CustomerID, Name, Address, PostCode

tblOrders
OrderID, Date, CustomerID


the following table identifies the stock type (this can be a roll, cut, remnant or other):

tblCategory
CategoryID, Name

I then have a stock table which is as follows:
tblStock
StockID, Name, SupplierID, SupplierCode, CategoryID, PurchasePrice, SalePrice

This is where the problem arises. Rolls have a length, width, roll number and colour associated with them. Remnants also have a length and width associated with them but can also have RollID if they have come fom a roll currently in stock. Cuts only have a length and width as they are ordered specially when the carpet is not in stock and is required for a specific order. Stock described as 'Other' only have a quantity associated with them.

They also said a new stock item needs to be created as a cut when a customer orders carpet off the roll.

Using this I would create an Order Items table like so:
tblOrderItems
OrderID, StockID, Qty (if category of StockID is 'other').

I have come up with having seperate tables for each of these categories and then placing a foreign key from these tables into the tblStock table using the primary key of that record but that seems to over complicate things. Would it be better to do it this way or keep it all in one table and only allow data to be input in certain fields depending on the category that has been selected?

Also as cuts are ordered specially they will not be in stock but a record needs to be kept of which customer has ordered the cut when it does come into stock. Should this information also be held in the tblStock table or somewhere else?

Hopefully I'll have explained everything properly (I'm a little confused and I have the information in front of me )

Thanks in advance
Chris ____________________________
Have you seen my munkee?

 

[wemeier]

My personal opinion would be that the tblStock table should ONLY contain information about objects in stock -- whether that object is a roll, remnant, special order or other.

Have fields for length, width and color (and maybe type -- Berber, shag, indoor/outdoor, etc.).

If a customer orders a cut from a roll, will you remove the cut length from the length of the associated StockID? In other words, If you have a 200ft roll of carpet and the customer orders 30ft would you update the carpet to now show 170ft? If so, I would create a new tblStock record for the 30ft portion and attach its StockID to the order.

If you don't have a separate purchase order system for ordering from the supplier, I'd also create a separate field for "In Stock". Whenever a customer orders a special order, I'd build a record on the tblStock table and leave "In Stock" false. When the special order comes in, I'd update "In Stock" to be true. This way, you know what is on special order and what has come in.

Hope this helps.