DB Design Basics

[BigBertie]

I'm new to Access and am bumbling my way through creating my first 'proper' DB. (I'm using Access 2007)

I'm struggling to fully appreciate how best to structure my DB. If I explain my scenario I’d welcome any comments on how to improve. I’ve simplified the below (mainly by removing the fields from tables that have no relationships)

Customer table (with basic customer details)
- Name
- Ref (pk)

Order table (Each customer normally places one order per year)
- Auto ID (pk)
- Ref (from Customer table)
- Year

Items table (the items ordered within each order)
- Year (from Order table)
- Stock No. ref.
- Quantity

So, I have a number of unique customers, each of which place yearly orders (but sometimes it’s more frequent), and each order consists of normally just one item, but sometimes two or three (rarely more than this, but it is possible)

So, I was relating the Customers:Ref (one) to Orders:Ref (many). I cannot have the Year as the pk as other customers will have orders for the same year (as this customer could also have) so I included an auto ID. Is this ok?


Now how do I best relate the Orders to the Items table? Each order would typically have only one item (but sometimes more) and there are only 5 or 6 different items available, so a lot of customers will be ordering the same item. What should be the pk in this table (introduce another Auto ID?) and what should it relate to in the Order table?

I did think to combine the Orders and Items table, but I found I was duplicating a lot of information in the Order table. (there are many more fields than shown above, but I'm only showing the important ones to make the structure more clear)


I appreciate this is fairly basic but would appreciate some comments before I plunge head first into creating a database with fundamental flaws!

Thanks in advance.

 

[emorris1000]

Don't use year as the foreign key there. You need an extra table I think.

Try this:

Customer
-CustID (pk)
-Name

Order
-OrderID (pk) Unique identifier of a specific order, could be an autonumber
-Date year, full date, whatever.
-CustID (relate to customer table.)

OrderDetails
-Autnumber (pk) (meaningless really, but you need one and can't use the others as they aren't unique)
-OrderID (relate to Order table)
-ItemID (relate to Item table)
-QuantityOrdered (of the itemID in that record)

Items
-ItemID (pk)
-Stock No. Ref (not sure what that is)
-Quantity? (not sure if that was the quantity you had in stock. If that was for the quantity that someone ordered that's the quantityordered field in the field above)