database application development -- tables

Once you have determined the need for a database application, the next step is to determine what data you need (and what data you do NOT need). Then you must organize it in the best way for the purpose of the application. So, for instance, you would set up personnel information differently for an accounting application than for a team roster.

I find that I like to develop Access applications backwards. That is, find out what the client wants in the end, and be sure the tables, fields relationships, and so on, will produce that end. And often while you are developing, you see other demographics or queries the client may want in the future; make sure they are available when it dawns on the client in the future – you will save yourself a lot of grief.

Tables should not be loaded with unnecessary fields, but related fields should be kept together. If you have a car sales room, you should keep employee information in a separate table from customer information, since you need different fields for each after the address and phone number.

Keep in mind that you do not want to waste time or memory, so customize the fields as much as necessary from the outset. However, err on the side of generosity until the database is well-established.

Data types – Access (and most RDBMSs) allows you to define the information in each field – currency, dates and times, text, different types of numbers. But you can also customize these further for format and content. The most common data types are text (even numbers such as zip codes and phone numbers should be text), number (both integer and decimal), date (which includes time), hypertext link (web sites) and lookup. A short number is a decimal; a short integer only goes to about 3600 and has no negatives; a long integer is unlimited and can be negative. For dates and times you can define the format; this only determines how the information is displayed, not how it is saved or entered. The hypertext data type is very handy – you can open up WWW sites directly. For instance, I keep a database on my desktop which has information on the gazillion sites where I am registered. I can open the record for PayPal, and find out what my username and password are, which e-mail address was used – and click right on the hyperlink field to get there.

Most Access is set up with a default text length of 50 characters – this is too many for a first name, and too little for a description of a topic. Text fields can be up to 256 characters in length; define it as as small a length as practicable. If you expect a number to include a fraction, select “double” as the format; otherwise, select “integer”, since that requires the least amount of memory. For dates, you can select time formats, and various date formats. These selections are available on the grid below the list of fields – changing for each field in which the cursor is sitting. The description area after the data type is for ‘hints’ – this shows up in the status bar when the cursor is in that field.

The Lookup data type – avoids typos and mismatched information by limiting the entries in a field by creating a list for the user. I usually create a “base table” first, containing the selections I want to go in that field. Then the wizard takes over when you select that data type. Access automatically builds a relationship between the base table and the one holding the lookup data type.

You can also select a default value for a field, which saves the user a lot of time in data entry. For instance, if 80% of your clients are in North Dakota, enter that as the default value in the State field – the person doing data entry can change it if the client is from Florida. This is not a locked value.

You can set up parameters for the values to be entered as well. For instance, I created an Access database test for my Access students. One of the first things they had to do was enter the date they took the test. If that date was not the current date, they were lying about when they took the online test – the test was terminated.

You can index fields. But remember that the more indexes in a table the longer it takes to search the table. The usual recommendation is to index 1:8 (one indexed field if you have 8 fields). The key is also an index that allows no duplications.

Keep text fields as small as you can, since the database will reserve that memory size. If possible, instead of using the Memo type, use a text box (255 characters is the limit). The reason is that Memo fields cannot be printed off in a report. You can also put default data into a field, and either lock it or don’t. My feeling is that if the field is locked, it means that it can never hold any other data. If that’s true, why put it in at all? I had this problem with a database that the state was having one of my clients fill in monthly. The only zip codes you could enter were those in its list; if the zip code wasn’t on the list you could enter “00000” during a patient intake – but on discharge you had to choose a zip on the list. This was a problem because I live and work near the Rhode Island border, and about 2% of my client’s patients were from RI.

But if the vast majority of the time the information is the same, then set it as a default, to be changed when necessary. For instance, if I am developing a mailing list for a local not-for-profit, I know that 90% of the time the state in the address will be Connecticut. So I type that into the state field, and then the data entry person doesn’t have to type anything in that field unless it is NOT Connecticut. This can save a lot of time.

I am not a big proponent of coding everything with autonumbering ID fields – sometimes one of the fields, or a combination of them, are preferable. I know this is what they teach now, and it’s to let habits coincide with different DBMS setups. But one must be careful of not duplicating autonumbers in different tables. And readouts can become very cryptic as keys take over information. If the key field is short (up to 5 characters of text or a long integer), use it as its contents. If the key could change, as in a name, use an autonumber instead.

Text fields should not be left as the default 50 characters. Access does not save the memory until there is data in a field, but if you have “ABC” in the field it will then save 50 chars. So define the chars as the maximum that might be needed and no more – 15 for names, for instance. You can expand it if needed later. That’s one of the nice features of a relational database.

Avoid memo fields – they cannot be linked and chew up memory. If you cannot enter 255 characters or less into a text field, rethink your structure.

Memo field specifications

Memo fields in Office Access 2007 can store up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65,535 characters in a control on a form or report. In addition, you can now apply rich-text formatting to the data in a Memo field. For example, you can set colors, change fonts, and make data bold or italic ….

…

Unicode Compression

Access uses Unicode to represent data in Text, Memo, and Hyperlink fields. Unicode takes up more storage space because it uses 2 bytes per character instead of one. To help reduce file size, the default value of the Unicode Compression property for a Text, Memo, or Hyperlink field is Yes. When set to Yes, any character whose first byte is 0 is compressed when it is stored and uncompressed when it is retrieved.

Data in a Memo field is not compressed unless it requires 4,096 bytes or less of storage space after compression. As a result, the contents of a Memo field might be compressed in one record, but might not be compressed in another record.

http://office.microsoft.com/en-us/access/HA100963131033.aspx

retrieved 12/17/06

Field names should be short and clear – avoid overly cryptic names; the user will need to memorize them. If the field name is overly long, use the caption property to enter an easier name to recognize. I rarely use the caption property – better to name a field SysInfo, which is clear as to its purpose, than Customer’s System Information.

I also do not recommend building relationships at the beginning of a database design – I let them automatically develop as I build queries. But sometimes setting up relationships can help you figure out how to arrange the data. I’d rather use one of the UML modeling diagrams – or delete the relationships once I have it under control.

Sorting is easiest in the list view of the data table. Just select the heading on the field that you want to sort and click AàZ (ascending sort) or ZàA (descending). I do this to check some aspects of data integrity – blank field entries will show up at the top with an ascending sort; and it’s easy to scan down the field to find typos.

Filtering is another feature that I use constantly and teach to my users. Filtering takes all or part of a field entry and shows only those records that meet that criterion. It’s a nice down-and-dirty way to check for duplicates or variations. Just be careful when you close the table – Access will ask you if you want to save the design of the table. If you do, it will always be filtered after that – it appears as if you lost all the rest of the data! If you say no to the design – all the data will remain intact.

You can also ‘block’ the display of specific fields when you only need to read a few of them, so that in the datasheet view you don’t have to scroll, by selecting the fields you do not want to see; select the “Format” menu and click on “hide”.

Rather than save changes in the design of a table as you use the data, if you will be wanting to view the data in the same filtered or sorted manner several times, create a query (or even a form, depending on the desired result) to achieve the same thing. For instance, I have a database of all the static information on the network devices, but most of the time I am interested only in the device name, IP address, location and focus file name. So I whipped up a select query that only shows those fields, which I use several times a day. I can specify a particular location when I run the query if desired.

Oversized Databases: As you work on your databases, Access saves every keystroke (yeah, every single one), rather than just the end result. So you may suddenly find you are uploading and downloading huge files. To fix this problem, you need to "compact" or "pack" the database. To do so, open it and use the Tools menu \ Database Utilities \ Compact and Repair Database. The database will be packed under the same name as a snapshot of its current condition. You will be amazed at the resulting size, especially if you format the fields the right way.

All of this information is not just advice on managing MS Access – these are applicable to any RDBMs, and just as necessary.