Best way to make this database?

Copper Contributor

What is the best way to create a database with information that will need to be updated multiple times?
Basically, I'm looking to create a database to track items for my company. I want to track when items are requested from our warehouse, when they're received from our warehouse, when they're shipped to a separate service, and when they're uploaded to the service. 

My initial idea was to have one table that can be updated through a form, but I think that is just going to cause more confusion than necessary. 

I know it would probably be easier to do this through Excel, but I would like to have an entry form so that anybody in the company an utilize the database with minimal confusion. 

1 Reply

@Kwoolridge 

Access is a development tool used to create relational database applications. One table is definitely not adequate nor appropriate for a relational database application. So we should start by looking at the three essential components, or layers of any relational database application.

  1. The Data layer, or Data tier. This is the component of any relational database application in which data is physically stored. This is made up of tables. The number of tables varies from situation to situation, but there is no case (that I can think of) where a single table could be called a relational database application. The relational part of the name comes from the fact that records in one table are "related" to records in one or more other tables. 
  2. The Interface layer, or Interface tier. These are the objects through which your users interact with the data--adding new records, updating existing records, filtering and selecting records for various purposes and so on. The two primary interface objects in a relational database application are the forms and reports.
  3. The Logic layer, or Logic tier. This is the code that automates various processes encountered in your database. It consists of VBA and macros. In Access, VBA and macros are two entirely distinct coding environments. In other Office applications, VBA and macro are, unfortunately, interchangeable names for the same thing. 

So, step one is to identify each of the things you want to track. You have identified at least three in your opening question:

  • "Items", whatever that is. I assume products you produce or sell?
  • "Warehouse", which apparently is a single location?
  • "Service", again a generic term that could mean a lot of different things.
  • "Shipping". This one may be a bit of a surprise to you, but it is "a thing" in the sense that each instance of shipping is a separate event of interest to you. 

I'm sure there will be others as you dig into the workings of your business rules. At this point, those four entities will be tracked in four tables, one for each. The RELATIONSHIPS between them are what differentiate a relational database from a group of lists. 

     Warehouses STORE items.

     Services RECEIVE items.

and so on.

I recommend you start by looking at the Northwinds sample relational database application. 

 

It does a lot of the things you need, plus many others. But looking at how it works should help you understand what you need to do when you create your own relational database application.

northwindtemplate.png