Designing Primary Keys For MS Access

Read More About Business

When planning new tables in MS Access or redesigning current tables, professional MS Access database consultants would like to make sure that every table ought to have a column, or two or three columns, that uniquely identifies each record stored within the MS Access database table. Some Access database designers use unique identification numbers, like social security numbers, employee Id numbers or product serial numbers. MS Access database designers, in addition to different database designers for alternative varieties of databases call this the table's primary key. MS Access uses primary key fields to index, i.e., speedily relate information from multiple tables and marshal the data 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 catalog, you'll use that identifier as the table’s primary key. However, you need to be absolutely bound these values for this primary key column will never be the same for another record record. You can't have duplicate values for database primary keys. As an example, don't use town names as a primary key, because names are seldom unique in a very database's record set. You could very easily have 2 cities with the identical name in the identical table, like Sandpoint, Idaho and Sandpoint, Alaska.

Primary keys must never be empty, null or repeat. In brief, primary keys should forever have unique values. If a column's value can ever be unknown (a missing value) or presumably modified at some future point, this column should never be used as a primary key, or part of a composite primary key.

Always choose primary keys whose values will never change. When you've got an MS Access database that contains more than one table, it's potential and typically practical that the table’s primary key will be used as a reference for other tables. If the primary key changes, the change should also be applied everywhere the key is referenced in the opposite tables. Coming up with database tables where primary keys do not modification reduces the prospect that the primary key might not coincide with alternative dependent tables referencing it.

One preferred technique used by most professional MS Access database consultants is to use an arbitrary value, such as a guid, or sequential number. These arbitrary distinctive numbers are very useful when used as primary keys. For instance, you might assign each fishing lodge invoice a unique invoice number. The invoice number's sole purpose is to identify a fishing lodge's invoice. Once assigned, it never changes and has no reason to change.

Several expert Access database consultants instantaneously contemplate employing a column that has the AutoNumber data sort, in spite of whether or not there could be a social security number, a distinctive product SKU number or some other distinctive identifier.

When your MS Access database tables use the AutoNumber data type, Access by design assigns a worth for you. Such identifiers have no value and are meaningless other than they're the primary key. The AutoNumber contains no relevant information regarding the record. Since there is no immediate relation to the first key and this data outside of the record set, AutoNumber identifiers are ideal for primary keys as a result of they do not change. Primary keys containing relevant information or facts regarding a row, like postal codes, telephone numbers or a client addresses, are more probably to change as a result of the factual info would possibly change during the life 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 contains more than one column, they're typically referred to as composite keys by database specialists.

Making your MS Access database style "right" is usually a matter of preference and no two people will design the database excactly the same. There are rules that make information access and maintainability simpler, and these rules are learned in formal settings. One ought to attempt to continuously make the database scalable and easy to work on by others.

Article Source: http://depositarticles.com/

NWDS supports this Alaska business and we are Database Consultants in Anchorage Alaska. They also specialize in aviation safety management systems, ICAO SMS, FAA SMS, IS-BAO SMS

Please Rate this Article

Not yet Rated

Click the XML Icon Above to Receive Business Articles Via RSS!

Additional Articles From - Home | Business

Our other websites
All about SEO
Beauty Tips Forum

	Print This Article
	Post Comment
	Add To Favorites
	Email to Friends
	Ezine Ready

Submit Articles
Member Login
Link Us
Submission Guidelines
Ezine Notifications
Article RSS Feeds
Sitemap

New Stuff
About Us
Developer
Good shopping portal
Link to Us
Contact Us
Privacy Policy
Terms of Service