Basic Steps in Database Design
When contemplating the use of a database in solving a business
problem, the following general steps are essential to a good
relational database design. You should follow these steps when
designing your MS Access database for this course:
- Make a list of all the objects in your environment
- This would include everything you want to keep track of, for
example employees, customers, products, transactions, etc.
- For each object, list the attributes of interest
- Every object has several attributes that are of interest to
you. For example, an employee (object) would have several
atrributes such as last name, first name, middle initial, date
of employment, date of birth, etc.
- For each object, select a primary key - If
we assume that, at least to start, each object will comprise a
table in our database, which attribute of the object would you
select as a primary key? Remember that a primary key must
uniquely identify a row in the table. If an obvious
(unique) key is not readily apparent, you can either combine
attributes into a key, or make up a key field. For an
customer, for example, you could assign a customer number.
- Determine the relationships between the objects
- How does each object relate to the other objects in the
database? Which primary and foreign key fields are
connected?
This is a good starting point. Once you have reached this
point, you can now look at normalizing your database, creating
additional tables, refining your relationships, etc.. This
will lead to a sound and workable database design.