Talk and Act
Whenever planning new tables in MS Access or redesigning existing tables, professional Access consulting companies need to confirm that each table ought to possess a column, or two or three columns, that uniquely identifies every record stored within the MS Access database table. Some Access database designers employ identification numbers, like social security numbers, employee Id numbers or product serial numbers. MS Access database designers, with different database designers for alternative types of databases call this the table’s primary key. MS Access makes use of primary key fields to index, i.e., immediately connect data from several tables and marshal the info all together.
If your database table already encompasses a distinctive identifier, like social security number for a dental patient, or product SKU that uniquely identifies each product in your hardware list, you’ll use that identifier as the table’s primary key. However, you need to be fully sure these values in this primary key column can never be the identical for an additional record record. You cannot have duplicate values for database primary keys. As an example, don’t use town names as a primary key, because names are seldom distinctive in a database’s record set. You may very easily have 2 cities with the same name in the same table, such as Sandpoint, Idaho and Sandpoint, Alaska.
Primary keys must never be empty, null or repeat. Briefly, primary keys must forever have distinctive values. If a column’s value can ever be unknown (a missing value) or presumably changed at some future purpose, this column ought to never be used as a primary key, or part of a composite primary key.
Always opt for primary keys whose values can never change. When you have got an MS Access database that contains more than one table, it’s doable and often practical that the table’s primary key will be used as a reference for different tables. If the primary key changes, the modification must additionally be applied everywhere the key is referenced in the opposite tables. Planning database tables where primary keys don’t modification reduces the possibility that the primary key would possibly not coincide with other dependent tables referencing it.
One typical technique used by most professional Access consulting services is to use an arbitrary value, like a guid, or sequential number. These arbitrary distinctive numbers are very useful when used as primary keys. For example, you might assign each fishing lodge invoice a unique invoice number. The invoice number’s only purpose is to uniquely delineate a fishing lodge’s invoice. Once assigned, it never changes and has no reason to change.
Several professional Access consulting services straight away take into account using a column that has the AutoNumber information kind, regardless whether or not there may be a social security number, a unique product SKU number or another distinctive identifier.
When your MS Access database tables use the AutoNumber data type, Access automatically assigns a value for you. Such identifiers have no value and are futile other than they are the primary key. The AutoNumber contains no relevant information regarding the record. Since there is no immediate relation to the first key and this knowledge outside of the record set, AutoNumber identifiers are ideal for primary keys as a result of they are doing not change. Primary keys containing relevant information or facts concerning a row, like postal codes, telephone numbers or a customer addresses, are more seemingly to alter because the factual information might modification throughout the lifetime of the data in your database.
There are times when two or more fields logically makeup an MS Access database table’s primary key. As an example, for an Alaska hunting and fishing lodge, a BookingDetails table stores details for Bookings would use two columns as a primary key: BookingId and LodgeServiceId. When primary keys comprises additional than one column, they’re often known as composite keys by database specialists.
Making your MS Access database design “right” is usually a matter of preference and no two individuals can design the database excactly the same. There are rules that make data access and maintainability less complicated, and these rules are learned in formal settings. One should try to always create the database scalable and maintainable.
Tags: database consulting, database consulting company, ms access consulting, ms access database consultants, ms access database consulting company
Posted in Databases · March 10th, 2010 · Comments (0)
Planning an MS Access database with no assistance of professional MS Access database consultants can be done, allowing that you understand the fundamentals of correct database design methodologies. When you’ve determined the purpose of your MS Access database, you will want to find and organize needed data that can be stored in your Access database.
To find and organize the data required, begin with your existing information. As an example, you might record client info on paper forms in an exceedingly old file cabinet or an Excel spreadsheet. Gather those documents and list every kind of data shown (for example, every box that you fill in on a common data entry form). Break the fields all the way down to their basic form, like 1st name and last name, rather than just “name.” If you don’t have any existing forms, pretend you have got to design a form to record the client information. What information would you set on the new form? What knowledge elements would you prefer to store or maybe run reports on in the longer term? Maybe you wish to understand how several fishing lodge guests that stayed with you in the past 2 years live in Alaska? Establish and list every one of these items. Another example, suppose you currently keep the fishing lodge guests list in a paper notebook. By closely examining these entries, you would possibly discover each entry contains a customers name, address, city, state, postal code and phonephone number. Every of these items represents a possible column in a table.
As you prepare this list, don’t worry concerning making it perfect or complete on your 1st attempt. Instead, list each item that comes readily to mind and remember that an MS Access database is an exceptionally simple and modifiable tool. If someone else in your business can be using the database, ask for his or her input. You can forever modify the table so long as you don’t have dependent programs looking forward to the information fields. This is not rocket science and you usually won’t have to use the services of an professional affordable Access consulting companies to extend the table.
Next, contemplate the varieties of reports, bulk emails, or brochure mailings you would possibly want to supply from the new database. As an example, you may want sales reports to point out fishing lodge guests by country, or a listing summary report that displays space or fishing charter boat inventory levels. You might also need to get type letters to send to purchasers that announces special sales. Design these probable reports in your mind, and picture what it’d appear as if it were sitting in front of you. What info would you place on the report? List each item. Do the same for the shape letter and for any alternative report you anticipate eager to conduct your daily operations.
Giving careful thought to reports, bulk email and brochure or flyer mailings helps identify information items you will would like in your business database. As an example, suppose you give Alaska hunting lodge clients the chance to opt in for newsletter, and you want to print a list of those that have opted in. To store that information, you add a “SendEmail” column to the hunting lodge’s consumer table. For every hunting lodge guest, you can either set the field to Yes or No, True or False, or 1 or 0.
Wanting to send Email newsletters to Alaska hunting lodge guests suggests another data purpose to store in the table. Once knowing that hunting lodge guests wish to receive Email newsletters, you will conjointly need to grasp the Email address to which to send them. Thus you need to record an Email address for each hunting lodge guest if available.
To design your table, contemplate these things carefully. Even expert Access consultants want to come back and add knowledge elements. This could occur moments once saving the table, or years when the need arises to extend the functionality of the database.
Tags: database consulting, database consulting company, ms access consulting, ms access database consultants, ms access database consulting company
Posted in Databases · February 25th, 2010 · Comments (0)