Help with structure plz :)

[Kira]

Okay so I would say that it is safe to assume that my attempt at my first database is crude at best. I was just wondering if any of you design pros might be able to help me figure out what I need to create.

Alright so this Database is meant to keep track of inventory items that are held in a repair shop. There are already items in the shop, which I will have to get the data for later to put in a table. I need to have a database that has a form to submit used inventory as well as a form that keeps track of what stuff we put into the inventory(new and old parts). I would also like to have the items put in and taken out(which would come from form submissions) to to be updated with how much inventory I originally had so I can know how much of each item I have at any given time. I have also been asked to have a table with vendor records(I.E. name, address, phone, etc.) as well as a table with the records of parts purchased from said vendors.

Also what relationships would I need to have? i really don't have much of an idea on how to create a good, stable foundation.

I hate to sound like I want someone to do all the work, but I just do not want to start all over AGAIN. I am just simply asking for suggestions. Mainly table structure. Thank you for any input.

 

[georgedwilkinson]

You've already begun. Write out a narrative of what your systems does, what your users do and expect. Isolate all the nouns in your narrative (inventory items, vendors, purchases, etc.). Those are candidates to become tables in your system. Almost all of the ones that I mentioned have a standard name in the database modeling world. Look in the Northwind database at the tables there and you'll see what they could be. Once you have the candidate tables, see if there are opportunities to combine them or break them apart...for instance, a vendor and a customer are just about the same thing. You may want to store them in the same table (Parties?). If you have a Party with information that is not about that party (it wasn't there when the Party was born and it won't be there when the Party dies), like address or phone number, you have additional candidates for tables (Addresses, Telephones). Other potential tables:
PurchaseOrders
PurchaseOrderLineItems
Invoices
InvoiceLineItems
Parts (this would include all inventory items)
Inventories (this would include the "counts" of parts over time)

The list goes on. Please notice that my table names:
1. are plural
2. don't have spaces

When you've gotten all the tables figured out, try to determine how they relate to each other, using the words "each", "one", "zero", and "many". For instance, "each purchase order has one to many purchase order line items and each purchase order line item has one and only one purchase order". In this case, you have a one (purchase order) to many (purchase order line item) relationship, which can be created in Access' relationship screen. If you ever find through this process that you have a many to many relationship, you need to create a whole new table, called a junction or associative table.

Holler back here when you get stuck.

 

[Kira]

VERY helpful, but when you mention the "Inventories" table would that be a table that simply showed input/output of the inventory or would it update to "parts" table to show how much total there was at any given time?

 

[georgedwilkinson]

VERY helpful, but when you mention the "Inventories" table would that be a table that simply showed input/output of the inventory or would it update to "parts" table to show how much total there was at any given time?

It shows that an event occurred that gave you a base count for parts. Some people call them inventories, some call them "stock take" or something like that. It is also where you would record your beginning count of a part. If a person physically looked on the shelf and counted the number of a part and wanted to record that as the current inventory level, that event would be recorded in this table.

 

[Kira]

Alright. That really clears things up for me. thank you very much!

 

[georgedwilkinson]

Ain't nothin' to it! Always happy to help out.

 

[Kira]

would the Parts and Inventories fields be the same? Also what type of different fields would there be from the orders table and the order line table?

So my tables should be: Inventories, Parts, Orders, Order Line Items, and Vendors?(invoice # is a field in the orders table)

 

[georgedwilkinson]

Parts and Inventories are two different things that are related. The nomenclature may be different based on experience.

I'm assuming PurchaseOrders for your question but if you do internal order processing (i.e. a parts warehouse), you would have a separate PurchaseOrders/PurchaseOrderLineItems tables. The Orders table would have:
ID (Autonumber PK)
(Purchase)OrderNumber (UK)
OrderDate
ExpectedDate (Maybe)
OrderStatus (Maybe, unless you track order status over time)
VendorID (FK from Vendors/Parties table)
SpecialInstructions
DescriptiveNotes
Anything else specific to the order.

The OrderLineItems table would have:
ID (Autonumber PK)
OrderID (FK from Orders)
PartID (FK from Parts)
Sequence
Quantity
Status (Maybe)
StatusDate (Maybe)
SpecialInstructions (Maybe)
DescriptiveNotes
Anything else specific to the relationship between the order and the parts on that order. (Some ERP systems store actuals in this table, like the actual price, though that would probably be in the Invoices/Vouchers table).

 

[Kira]

what does FK and PK stand for?

 

[georgedwilkinson]

FK = Foreign Key
PK = Primary Key
UK = Unique Key (or Unique Index)

You'll need to really understand these concepts to keep moving forward. Access will prompt you for the PK when you create your tables. Best to know this stuff ahead of time, before it asks you.

 

[Kira]

Thanks again. I need to leave this office for now, but I will be on tomorrow 8am-5pm eastern time. I will undoubtedly need your assistance. If you are available that is. I feel better about this new database already.

 

[Kira]

Okay so I know that the Foreign key is simply the primary key from another table, but how do you assign it? Does someone have to go and look it up and then type it in every time they make a new row? And do unique keys have to be assigned or how does that work? And if I use the Pk from Parts as my FK in Orderlineitems what do I do about items that are ordered that are completely new? Here is a copy of my database so far. Does it look okay?