MODS - I'm not sure if this should go into this topic or "database concepts and design", please move if it should be there instead.
I'm trying to create a database that will track orders for new inventory that we have here at our shop. I'm trying to make it so we can input new orders and look up old orders and their related products. What I was thinking of doing is creating one master input form where the data can be put into when a new product is ordered. I would then have a query to lookup the data after it has been input.
Attached is the relationship report, please let me know what you think of it.
I haven't worked in Access in a looong time so if this is way off please let me know, thank you so much for your help!
Ummm... This will probably be moved, but I'll chuck in my first impressions here.
Why not just have the checkin fields in the Order table? Looks like a 1:1 relationship there. Same for VendorOrderInfo. Don't think either of these tables are needed really.
Shipto, ShipVia, ShippedFrom all look to be related and designed incorrectly. These should have primary keys that are ShipToID, ShipViaID and ShippedFromID respectively. They should be related to order in the same way as the Employee and Vendor are.
Product should be related to OrderDetails, not Order. With that design, you can only have one product per order. You need to relate them like this:
ORDER 1-----M ORDER DETAILS M-----1 PRODUCT
And Order Details needs to have both ProductID and OrderID in it and those two fields make up the primary key for order details.
frankly its a reasonable start, but i thgink you have some serious issues to resolve
why do you have an order detail table.. all of the detail there is a one to one realtionship, so it can quite happily reside int he orders table
your model only supports a single product per order.
classically UI'd expecgt an order header contiasning heaverything (eg order date, delivery date, po nuymber etc
an order detaisl table which identifies what product has been ordered, the quantity and the agreed price (if relevant)
I don't understand the vendor table and manufacturer table (unless this is to cater for you buying from several wholesalers the same range of products form different manufacturers.
I would probably expect some form of interesection table to identify which wholesalers / vendors stock which manufacturers products (you may even need togo further and identifyu which manufacturers products are sticked by which wholesalers, as not all wholesalers stock the whole range from each manufacturer