Chapter 3: Designing the Back-end Tables    

            This chapter is to help you develop the basics of good table designs which are a requirement of a flexible and efficient database. Without these conventions the database can become bulky and difficult to support and maintain. In the multi-user databases proper design is important to reduce the probability of database corruption which is a main concern for Access programs.                                                                       

The first aspect of developing a good database design involves pencil and paper sketches to determine: table fields, field sizes, and relationships. Keep your tables small and try not to repeat data- i.e. apply “normalization” (discussed in a later chapter). The table fields can be obtained form either the Medicare National Standard Format, from the more recent CCR standards (another excellent CCR site).  You can, of course, borrow the table structures of the MWEP back-end tables.

    Here are the following data relationships, of which 4 basic types exist:

“one to one”	This relationship is used to break up large tables with multiple fields into logical data groupings. An example would be patients to insurance information. This type of logical normalization is prudent, since some patients will have insurance and thus carry a one-to-one association with an insurance record while those that don’t won’t have the associated record, thus saving space.
“one to many”	This relationship includes the patient to medications relationship as well as the patient to account service dates relationships. In both of these situations there will be numerous elements in the “many” (medications or dates of service) that apply to the “one” (the patient record).
“many to one”	This relationship  is less commonly used, but can be ideal for certain situations, s.a. when you have a group of billings for a certain service date and you need a table that summarizes the data into one record. In this situation you will have: One patient record. Many dates of service, each with multiple lines of billings. One summary record per date of service summarizing the billings for that date.
“many to many”	This relationship could come in handy when you need a relationship between 2 tables in which all members can have multiple associations in the opposite tables, s.a. is the case when one posts groups of widgets.

In Access, you later could electronically post your relationships by clicking on the top toolbar: Tools à Relationships. You'll end up with the following applet:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You will need to:

1. Determine which Back-end table container is best for your EMR- Jet 4 vs. an SQL back-end.
2. Assign fields names and field widths.
3. Normalize your tables to avoid repeating data.
4. Obtain lookup table data from the internet, s.a.:

• ICD-9 Tables
• AMA CPT Code sets
• Zip Code Tables

Database models

The flat database table layout consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another. For instance, columns for name and chart number might be used as a part of a system security database. Each row would have the specific chart number associated with a specific user. Columns of the table often have a type associated with them, defining them as character data, date or time information, integers, or floating point numbers. This model is the basis of the spreadsheet, and represented the most common format in the first database implemented during the 1960’s and 1970’s. The flat database table model are considered somewhat inflexible due to their rigid structure and heavy reliance on applications programs to perform even the most routine processing.

The relational database table layout allows multiple tables to be used together through the use of pointers (or references), thus eliminating redundant data. In the late 1970s, this model originated in the academic research community and eventually became available in commerce, such as IBM DB2 and Oracle. In relational databases data is stored in tables made up of one or more columns. The data stored in each column must be of a single data type such as Character, Number or Date. A collection of values from each column of a table is called a record or a row in the table. Different tables can have the same column in common. This feature is used to explicitly specify a relationship between two tables. Values appearing in column A in one table are shared with another table.

Below are two examples of tables in a relational database for a local medical practice:

 

            Notice that these 2 tables have a common linking field- the numeric “ACCT” field. In most cases there must also exist key fields that represent special columns that are used to uniquely identify rows or records in the table. You do not have to define a primary key, but it's usually a good idea. If you don't define a primary key, Access asks you if you would like to create one when you save the table. To add a key field, select the primary key button:

            This allows you to have one patient with multiple medications, or a Master/Detail relationship. In the example above, the Patients table would have the “ACCT” field as the key field, and the Medications table would have the “ACCT” as well as the “Med_Name” fields as key fields thus allowing for unique records each with the same account number. Alternatively, a date/time, a sequential, or a random number field.

 

Working with Database Fields

 

            Access database fields are created by entering a field name, a field data type, and other field information such as field description, field size, indexing, and of course whether or not it is a key field. The field description is an option to identify the fields purpose; it appears in the status bar during data entry. After you enter each field's name and data type, you can specify how each field is used by entering properties in the property area. Before you enter any properties, however, you should enter all of you fields names and data types.

 

            The following table shows the various Access field data types:

Data Type	Used For
Text	Text or combinations of text, numbers, and punctuations. These numbers cannot be used in calculations, such as social security numbers. Size: up to 255 characters.
Memo	Lengthy text and numbers, such as notes or descriptions. Size: up to 64,000 characters.
Number	Numeric data to be used for mathematical calculations. For calculations involving money, use the currency type. Set the FieldSize property to define the specific Number type. Size: 1, 2, 4, or 8 bytes.
Date/Time	Dates and times. Size: 8 bytes.
Currency	Currency values. Use the currency data type to prevent rounding off during calculations. Accurate to 15 digits to the left of the decimal point and 4 digits to the right. Size: 8 bytes.
AutoNumber	Unique sequential or random numbers automatically inserted when a record is added. Size: 4 bytes.
Boolean	Fields that will contain only one of two values, such as Yes/No, True/False, On/Off. Size: 1 bit.
OLE Object	Objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data), created in other programs using the OLE protocol, which can be linked to or embedded into an Access table. You must use a bound object frame in a form or report to display the OLE object.
Hyperlink	Field that will store hyperlinks. A hyperlink can be a UNC path or a URL.
Lookup Wizard	Creates a field that allows you to choose a value from another table or from a list of values using a combo box. Choosing this option in the data type list starts a wizard to define this for you.

Numeric Field Size Properties
Setting	Description	Decimal Precision	Storage Size
Byte	Stores numbers from 0 to 255 (no fractions).	None	1 byte
Integer	Stores numbers from –32,768 to 32,767 (no fractions).	None	2 bytes
Long Integer	(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).	None	4 bytes
Decimal	Stores numbers from -10^38 -1 through 10^38 -1 (.adp) Stores numbers from-10^28 -1 through 10^28 -1 (.mdb)	28	12 bytes
Single	Stores numbers from –3.402823E38 to –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values.	7	4 bytes
Double	Stores numbers from –1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive values.	15	8 bytes
Replication ID	Globally unique identifier (GUID)	N/A	16 bytes

Developing A Blank Back-end Container

You can begin your Back-end program development by making a new Access database capsule. Click on File à New à then in the “New File” window, select “Blank database…”. At that point, a popup window call “File New Database” will be seen. Name your new database (in the example, the name is “dbNewDatabase.mdb”), and select a new folder to save it in if the default folder is not the one you want. Once you are finished, click on “Create”. Below you’ll see a blank new database container.

Creating and Using Your First Table

Creating a new table requires the following steps: 

1. Click on the Tables tab on the Access main screen
2. Click on the New button.

   

3. Choose the Design View and click the OK button.

4. Fill in the name, data type and description of each of the fields in the table.
5. Designate a primary key by clicking on one of the fields with the right mouse button and then choose Primary Key from the pop-up menu.
6. Save the table by pulling down the File menu and choosing Save.
7. Close the new table by pulling down the File menu and choosing Close.

To change the design of an existing table (e.g., to add, change or delete a field):

1. Click on the Tables tab on the Access main screen
2. Highlight the name of the table to be modified and click on the Design button.

3. Make the necessary changes.
4. Save the table by pulling down the File menu and choosing Save.
5. Close the table by pulling down the File menu and choosing Close.

To add, delete or change data in an existing table:

1. Click on the Tables tab on the Access main screen
2. Highlight the name of the table to be modified and click on the Open button.
3. Make the necessary changes to the data.
4. Save the table data by pulling down the File menu and choosing Save.
5. Close the table by pulling down the File menu and choosing Close.

To create or edit relationships between tables:

1. Pull down the Tools menu and select the Relationships menu item.
2. To display tables, right click and choose Add Tables
3. To create new relationships, drag a key field from one table and drop it on the associated field in another table
4. To edit an existing relationship, double click on the relationship line.
5. To delete an existing relationship, click on the relationship line and press the delete key.

To declare table relationships:

As discussed earlier, the single most important thing that distinguishes relational from the older flat style database is the table links. In the medical database example presented, the Patients table is related to the Medications table by virtue of the “ACCT” field appearing in both tables. Access has a means to make this relationship explicit using the Relationships screen. Access uses this information when designing reports, forms and queries that require more than one table to be displayed.

To get started, display the Relationships screen by pulling down the Tools menu and selecting the Relationships menu item. The blank Relationships screen will appear:

 

Now right click on the Relationships screen à Show Table to add more tables:

To connect the Customers table with the Accounts table to form a relationship, click on the ACCT field in the Patients table and drag it over on top of the ACCT field on the Medications table. Upon releasing the mouse button, the Edit Relationships dialog box will appear, and you’ll be able to set up at least one relationship between the tables to be linked:

Access will do its best to determine the Relationship Type (almost always One-to-Many).

One additional step to be taken is the check off the box labeled "Enforce Referential Integrity". This option puts constraints into effect such that a Medications record can not be created without a valid Patient chart and Access will also prevent a user from deleting a Customer record if a related Accounts record exists. At this point, click on the Create button to create the relationship.

If you wish to delete a relationship, simply click on the relationship line and press the delete button.

Jet 4.0 vs. MSDE vs. SQL Back-ends

At some point you need to determine what type of Back-end architecture your program is going to have as Back-end. As a developer of small to medium office systems, I am still an avid user of the “file-server” JET table structure. Over the years it has worked extremely well, even under the then-available underpowered LAN (at a miserable 11 mbps speed) and computers (going back to the 386 computers).:

• Jet can support up to 255 users from a technical and licensing standpoint, but is recommended for less than 20 total users. As the user base increases, performance degrades significantly with file-based databases.

• It is optimized for a small office with multiple powerful nodes, but not an expensive client-server configuration.

• Jet 4.0 does not require any significant increase in overhead IT maintenance in a moderately busy office.

 

Known problems include:

• Security is not as tight as in the MSDE / SQL architecture.

• As the number of simultaneous users multiplies past 20, performance and stability problems can begin to occur theoretically.

• Can handle up to 2GB of data.

• Data computation is limited to a single thread.

            MSDE is considered a more robust Back-end than the Jet 4.0 and is an easier method of having your project SQL ready. Unfortunately, you will lose some of the pliability found in JET. Some facts about this Back-end table repository:

• MSDE performance at five concurrent workloads or less matches SQL Server, and above five concurrent workloads is tuned to match Jet.

• Can handle up to 2GB of data.

• Data computation is limited to a single thread.

 

SQL Server is capable of supporting hundreds or thousands of concurrent users at the highest levels of enterprise-class performance, can handle an infinate number of concurrent workloads and users, and can hold terabytes of data. According to Microsoft: “If an application requires more than five concurrent users, SQL Server is the best option.” The only problem with an SQL server is that it is expensive to purchase and manage. If a small to medium sized office needs added power, then they should upgrade to a (a now inexpensive) megabit (1000 mbps) LAN and the theoretical "processing speed" hit would be obliterated. It would be much easier to spend less than a five hundred bucks on a LAN upgrade than to pluck $8000 or more on a client-server SQL system.

The condition under which each database engine is the most effective with Access 2000 in the small business organization is as follows. The overall vast majority of physician offices are small to medium sized and should not have any problems with a well-written Jet-based EMR product. In summary, the Jet Database Engine, a “file-server” architecture that locally does all the computation and recordset manipulation, should be used if:

• You want the highest compatibility with Access 97 or earlier versions. [not important anymore]

• Your environment has a small number (<20) of simultaneous users.

• You have very low resources, such as memory or disk.  Another way of stating this is,  "You don't want to blow your dough on a Back-end dedicted server."

• In certain instances, processing the data at the level of the end-user computer can be preferred, unlike what happens in a client-server setup.

• Ease-of-use is a premium. [VERY IMPORTANT!!!] 

• There exist situations where local processing would be better.

Use the MSDE database engine, a “client-server” architecture where the computation and recordset manipulation is done on a dedicated server, if:

• You want to develop from a single code base, from a single user to thousands of users. [i.e. for a hospital, not a small office]

• You expect a future need for greater scalability. [i.e. you want to blow even more money in the future for power you may not need]

• You require easy merge replication with the central server. [you can replicate your Jet databases too, but even Microsoft suggests being careful if your table grows to too large a size, with any platform when replicating your data.

• You need the best security. [You can code in decent security within Jet 4.0.]

• You need greater reliability, such as transaction logging.

• Your system is online 24 hours a day, 7 days a week. [You can automate the backup process via code, as I've done with my programs]

• You need stored procedures and triggers.

The future for these softwares looks promising. As for JET, Access has always used the JET database engine. JET is owned by the SQL Server team and ships as part of Windows. Access 12 will come with a “privatized” copy of JET that now supports new data types, has improved performance, and better stability. It is fully backwards compatible, so existing solutions will run unchanged. It will continue to have much of the same look and feel, except it’ll be even better.

SQL Server Express is the newest version of SQL Server 2005 that can be installed and run on multiprocessor machines. One neat thing about MS SQL Server Express is that Microsoft has removed the MSDE governor that allowed only 5 concurrent connections at a time. Like MSDE, it helps developers build robust and reliable applications by providing a powerful database that is not only free and but easy to use. You can design schemas, add data, and query local databases, all inside the Visual Studio 2005 environment. If you need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server. On the downside, it still has the limitation that internally, the engine limits the number of threads to 1 so that only 1 CPU is used at a time. Features such as parallel query execution are not supported because of the single CPU limit.

Using the Upsize Wizard to Move Your Back-end Architecture from JET to MSDE

The Upsizing Wizard is an add-on tool that will take your Access database into the SQL Server environment. It is available from a variety of sources, including your MS Office 2000 (or higher) software disks and the Microsoft Developer Network. For an introduction to and demonstration of using the MS Access Upsizing Wizard as well as information on what to check prior to upsizing, design considerations, suggested troubleshooting techniques for common upsizing issues and additional resources for information, please refer to “How to Use the MS Access Upsizing Wizard” at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdeforvs.asp.

Applying this Tutorial to Your EMR Project

          At this point, begin creating your tables with the fields that you wish to assign in a normalized manner. You should refer to the back-end tables in the MWEP for assistance. You should start with at least` the following tables:

• General Information
• Medical (for patient health problems)
• Medications
• General Notes (for memo fields with special important notes on patients)
• Scheduler table
• Add some general lookup tables as noted above, i.e. the zip code and ICD9 tables.

As you develop your UserForms, you will find that you will need many more tables, s.a. for data lookup and for other patient data.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Bibliography:

1. http://www.databasedev.co.uk/fields_datatypes.html
2. "Chapter 45 - Using the MSDE Solution with MS Access 2000", URL: http://www.microsoft.com/technet/prodtechnol/sbs/45/reskit/part9/sbrk0945.msp
3.  URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/usingmsde.asp
4. http://msdn.microsoft.com/vstudio/downloads/addins/msde/add.aspx
5. URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdeforvs.asp
6. URL: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp
7. URL: http://amazingcharts.com/ub/ubbthreads.php/ubb/showflat/Number/9681/Searchpage/2/Main/1026/Words/sql+server+express/Search/true/Re_AC_to_EZ_Claim_Export_Place#Post9681

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Contents:

Database models

Developing A Blank Back-end Container

Using An Existing Database Project

Creating and Using Your First Table

Splitting Your Database

Jet 4.0 vs. MSDE vs. SQL Back-ends

Using the Upsize Wizard to Move Your Back-end Architecture from JET to MSDE

Applying this Tutorial to Your EMR Project