Database Organization

Here at the College of Veterinary Medicine, the databases that I have used are Microsoft SQLServer, Microsoft Access, and ProISAM. ProISAM is the type of database that Vetstar (our Hospital system) uses. It is the proprietary database for the PROIV programming environment that Vetstar is written in. The main problem with this database is that it is not ODBC compatible which just means that you cannot connect to it and query it directly with other programming languages such as Visual Basic.

ODBC which stands for open database connectivity, is an industry standard for connecting to databases. We are planning to move the Vetstar database to a PostgreSQL database sometime in the next year. This will be ODBC compatible. Meanwhile we are now copying Vetstar tables to text and then loading them into a SQLServer database in order to be able to do the ad hoc queries that are needed.

Other databases we maintain on SQLServer are the Personnel database which keeps track of employee information which, by the way, is the source for the college directory for CVM on our Web page and for the picture directory that is published each fall among other things; the Student Services databases which hold all of the information about our students ( personal, student schedules, grades, Web Admissions to the college); the Medical Records database which holds all of the abstract information for procedures and diagnosis pertaining to animal visits.

We also have a SQLServer database which holds all of the archived data from VSI which is the hospital system we had before Vetstar. We are using MS Access databases in a variety of ways too. The Medical Records Depatment uses a record tracking and census database which are Access. The Business Office at the College of Veterinary Medicine, both in the Large and Small Animal Clinics’ has Access databases for keeping track of things related to spending, donations to the college, and budgeting. Access provides a user-friendly interface to work with tables, create reports and forms as well as being the repository for your data.

Many individuals are using Access for their own uses. Access is good for smaller databases and databases that only a few people will access at one time and for a database where you don’t need to worry about who can see or change what data. SQLServer is much better for larger databases and cases where many people will access the database at the same time. It is also much simpler to set permissions for various database objects in SQLServer. You can do some of that with Access, but only if you create a special workgroup for the database (having done this in the past, it is a bit of a nuisance to work with).

When you talk about database access, you may hear people talk about front-ends and back-ends. A back-end is the database where the data actually resides. The front-end is usually a program like Visual Basic, PROIV, or even Access which links to the tables in the back-end database and then allows you to use the data. We have a lot of front-end Access programs which link to a back-end Access database or a SQLServer database. SQLServer itself is just a back-end system. It doesn’t provide any real programming tools to create a user friendly interface. I like SQLServer. I like it because it is robust, flexible, easy to maintain and easy to backup.

I use Access a lot as a tool (as opposed to a full blown application) to link to SQLServer databases to manipulate the data and create special reports, etc. This is a characteristic problem with all databases. The Database design is very important. A poorly designed one will cause many problems and duplications of rows when you try to link tables together to find the information you are looking for in a query. So you do need to put a lot of thought into your design. That is the information that I was able to put together after interviewing one of the computer programmer analyst. (It was a long two hours).