Join Mark Swift for an in-depth discussion in this video define data elements, part of Access 2003 Essential Training.
- [Narrator] Once you have a mission statement and a good grasp of the system they're currently using, it's time for you to define your data elements. Let's revisit for a moment the document that I had with the Task List. The Task List defines the important information that needs to be captured by your database. Well, the first thing that I would do after making sure my task list was complete, and of course you do this through the interview process, as you're interviewing people and asking them what it is you want to capture, you're constantly looking at your task list, and this would probably be handwritten.
I've typed these up for the training so that they're easy to display on-screen but, you're probably going to be doing this with a pad of paper. You're looking at your task list and you're trying to categorize everything they're telling you within the existing elements in your task list. For example, if the first person said that they want to manage their customers and know exactly who their customers are, so I put down, "Enter and maintain customer information" and beside that, I have an example of some of those things based on what they're telling me. I want to keep track of their names and their addresses, et cetera.
If you go to interview the next person and they say that they want to keep track of the customer accounts, well, customer accounts and the customer information are all kind of bundled into one thing. So, I've added that to the customer information line rather than creating a new task for myself just to avoid the confusion of having too many items on my task list that are redundant and then trying to build too many objects within my database. What do I mean by objects? Well, when you're defining your data elements, there are a few things that you need to know the basics of what access does.
Access is going to allow you to design reports that can be viewed on-screen or printed. You have to design data fields for data entry, individual containers for pieces of data. You'll need to design tables, much like you would inside of a spreadsheet program. And in those tables, you're going to need to relate that information one to another. So, for example, on our task list here, sales are going to be related to customers. It's the customers that come in and buy items out of your store, so every sale you make is going to be related back to a customer, whether that customer is being tracked explicitly or just by a receipt number, that's a later detail.
You're going to be looking at the field design. So, the data design is the overall structure. The field design is your individual components. Form design. Well, forms are a method for people using the database to enter information into the database. While trying to define your data elements, you need to keep all these various things in play. What is going to be a form? What is going to be a result of a report? What is going to be an object? And when I say object, I mean a definable, real-world object that you can keep track of inside the database.
If I were looking at this task list, which we are, I can define objects as being customers. That's an item that I'm going to keep track of. Sales. Sales are an item that I'm going to be tracking of. The line items within the sales. That's something I've been asked to keep a detailed line item in each sales receipt and that line item is also an object in my mind. The inventory. Of course, those are objects. The inventory is made up of a series of real-world objects that need to be tracked.
And the employees. Off the top of my head, those are the objects that I want to record. And in order to create and work with those objects, of course, I'm going to need some supporting players. Let's take a look at that. And here we have a diagram that's outlining the design of our objects and our data elements. Now, I've done this on the computer so that we can look at it here. Normally, you would do this by hand and on a pad of paper. You're not going to spend time designing this on-screen because this is done during interviews and conversations with your end users and it's constantly evolving.
You're probably going to make notes on how the design of your database is going to look over and over again. You'll notice the objects that we came up with, here. We have Customers, Inventory, Employees, SalesReceipts, ReceiptLineItems, the Orders, Suppliers, OrderLineItems, and then some supporting players you see at the bottom. Those bottom three tables are really lookup tables. For example, under Customers, we have AccountType.
The AccountType's table is going to be a list of all the various account types that can then be applied to the AccountType and Customers. But that ventures on relationships, which is a discussion we're going to have next so, hang onto that for a minute. You need to spend your time interviewing your end users, interviewing your customer, and even if that's you, looking at your major objects, what are you trying to model in the real world? And then, what are the data elements that need to be broken out? Exactly what information do you want to capture about your customers? Do you need to have a second email address and a second phone number? Ask the questions.
Look at the data elements, lay them out in detail, and at this point, start thinking about what kind of a container you're going to use to hold this information. That'll make more sense later on but, think about it from a numerical or a text standpoint, and think about the size of your containers, right now. How big is the container going to be that will hold the phone number? How big is the container going to be for a zip code? How big is the container going to be for a first name or a last name? These sizes will come into play very soon.
After you've mapped out all your data in this very conceptual way, and you have your data diagram, you're ready to move onto the next step and finish working through your Seven-Step Methodology. Now you have to look at what is it about each object that you're trying to capture? And again, this information very well may come from an interview. It's not your place as the database designer, unless of course, you're designing it for yourself only, to answer the questions about what's being captured. So, I've included in here things like the last name, the first name, the company they work for, the street address.
All of your known customers are going to have a series of information that you'll want to keep inside your database. The important piece of data right at the top, and this isn't something you may know off the bat, so don't worry about it, is the account number. You need to have a unique identifier, something that is absolutely unique to that customer. Last name, and even the last name combination with the first name isn't always unique. You know if we go back to the phone book example, there are a lot of Smiths in the phone book and there'll probably be a lot of John Smiths in the phone book.
So, in order to differentiate, they'll keep track of the address, and the middle name, and as a user, you've got to figure out in the phone book exactly who you're looking for and if you don't know where they live, you're really in trouble. In a database, there can be no ambiguity, so you'll need to have a unique identifier for each object. And in this case, the account number will act as that. No two customers will share the same account number. And if you look across for the Inventory and the Employees, again, I've broken out all of the important details that need to be kept track of for each one of these items.
Now, also notice that at the bottom of all of my objects, I have a blank space. When you're writing, don't ever think you're done because just when you think you're done, somebody will come up with one more detail about that object that you'll want to track. Plus, there's also relationships that we have to talk about in a little bit and those relationships might fit one to another. Now, you can see a lot of other tables here. I've got a Manufacturer, and a Departments, PaymentMethods, and AccountTypes. These are all lookup tables and they're simply lists of data that will be identified within an object.
For example, under Customers, you'll see we have AccountType. Well, the different account types are listed down below in that separate table. And that's necessary, in order to have those accounts be manageable, we have a government account, a corporate account, and a personal account. And those are managed in a separate table and then linked back to our customers. This may be part of a learning process for you, but I'm showing you a finished example of what would happen if you mapped out your data elements and this is step number three.
While you're defining your objects, you should also start thinking about the reports, and again, that's your on-screen summaries of your information or your printed summaries, and the forms that are going to be used for entering information into your database. For example, looking at my task list here, I went through and identified the objects. We can also go through and identify some of these tasks as reports. A customer directory is an obvious example of a report. Mailing labels are an obvious example of a report. Let's take a look at the forms and reports that are going to be used in this database just from our initial summary.
Those might grow and change but, they're definitely something you want to try and identify here in the Define Your Data Element section. Here's a quick list, again, you'll doing it on paper, of the forms and reports that I can see from the task list. In the reports, whether they'd be on-screen or printed, we have our Customer Directory and Sales History, the Mailing Labels, our Customer Account Summary, and the Sales Summary by Department. The forms that we're going to need to enter that information will be Customer Information.
We're going to need to enter that. Employees and Inventory, obviously at the bottom of that list. And then, of course, the Sales Receipts and the Line Items, and the Order Form. The Order Form to bring more inventory into your store. Now, with the Sales Receipt, this is kind of an interesting situation. It really doesn't change the design process for you but initially, your sales receipt is going to be about entering the customer information. Or, if it's a walkthrough customer with no customer history, just generating a receipt number. But, the Sales Line Items probably aren't going to be entered.
In our examples, we're going to have to because we don't have any other equipment. But in a real store, you'd have a barcode scanner and that item would be scanned and then from your inventory file, it would match up the number and bring in the description and the cost, and everything right into your receipt. It doesn't really change the way you design your database. It just means that in the real world, we'd have another input device adding that information for us and it wouldn't have to be typed manually all the time unless there was something that needed to changed at the cash register.
As we all know, that happens. So that's a good overview of Defining Your Data Elements. This is a very, very, very important step. They're all important. You need to go through them step-by-step and I know it seems like a lot of work, but if you don't spend the time now, you'll be spending the time later, or you'll be scrapping your entire database and starting over again. When you're defining your data elements, there's one more thing we need to look at and that's normalization. Let's talk about that next.