Transcript Designing Tables in Microsoft Access

Designing Tables in Microsoft
Access
By
Ed Lance
About Me
Independent Database Consultant
Worked with Access since 1.0
Many years designing, developing, tuning,
and developing with databases.
SQL Server and .Net development
consulting
http://SanDiegoDataPro.com
Topics
Introduction
Creating Tables
Setting up Relationships
Introduction
This presentation will cover designing
tables and relationships in MS Access
Not covering concepts much (i.e. relational
DB theory; I have slides on that).
Not covering linking external tables.
IMPORTANT!
Tables and relationships are the
foundation of your Access application
It’s vitally important that you get them
properly set up BEFORE you work on
queries, forms or reports.
– Avoids road blocks and kludges
– Designing other objects goes faster in Access
You need to have a working design,
(normalized) before you begin.
Creating a Table
Several ways to start
– Datasheet View: Just start entering data.
– Design View: Usual way, gives you all the
properties
– Table Wizard: Can save some time if you
have common data, and help you learn
Tables can get created other ways as well;
Make table queries, importing etc.
Table Design View
Allows you to edit the structure of the table
and set properties for fields (columns)
Pic here
Adding Fields
Enter Field Name, Data Type and
Description
Field Names can contain spaces
Naming Conventions?
Description is optional but is good for ‘self
documenting’ applications. (Watch out,
shows up in status bar in forms!)
Setting Primary Key
Data Types
Following data types are available:
–
–
–
–
–
–
–
–
–
Text: Character data up to 255 chars
Memo: Up to 64K chars
Number: Integer and Float types
Date/Time: Dates and Time values
Currency: Currency values without rounding loss
Autonumber: Long Int., Access manages
Yes/No: bit value.
OLE Object: Binary objects, e.g. Word, Excel docs
HyperLink: 64000 chars, treated as hyperlink
Field Properties
Properties in the General Tab:
–
–
–
–
–
–
–
–
–
–
Field Size: Number of chars or size of numeric type
Format: How are those number displayed
Input Mask: For data entry
Caption: Used by form designer
Default Value: If nothing entered in field
Validation Rule/Text: validation expressions
Required: Don’t allow Null values
Allow Zero Length: Allow “” in text fields
Indexed: Is this field indexed
Others: Unicode, IME, Smart Tags
Lookup Tab
Allows you to set the default control that
displays the value.
– Shows up everywhere.
– Can be overridden in forms & reports
For text and number, can use a combo or
list box.
For Yes/No, can use checkbox or combo
box
Indexes
An Index is like a hidden lookup table on
one or more fields.
– Can have a huge impact on Select queries
– Can slow down inserts & updates
Deciding what fields to index is an art.
– At least get Primary Key and foreign keys
– Good naming makes this automatic
An index can be on more than one field.
Indexes
Index dialog
– Index Name, Field Name, Sort Order
Properties
– Primary – Is field part of primary key
– Unique – Does the index enforce unique
values
– Ignore Nulls – Is Null considered a value?
Changing index is changing table design,
must save table.
Relationships
Very important!
Setting relationships at design time on
tables means Access is responsible for
referential integrity, not you…
Also, relationships are recognized
automatically when designing queries,
forms, reports
Relationship Window
Used to manage relationships and show
graphically.
Again, good naming helps you out.
Window is also saves layout of itself, so it
will ask you to save.
Relationship Window
Double-click relationship line to get
properties
Relationships can be more than one field.
Referential Integrity
– It’s a good thing…
– Cascading Updates. Not an issue if you
always use Autonumber PKs
– Cascading Deletes. Useful but dangerous.
Reviewing the Table
After you are done making tables, open
them all up and check them out.
Subdatasheets. Do we need these?
Print relationship window for
documentation.
Design Tips (IMHO)
Get table design down before you rush into it.
Name the PK field the table name w/o prefix (tblProducts
 ProductsID)
Name foreign keys same as the PK they join to.
Always use Autonumber PKs (this started an argument
at a dev. convention)
Don’t name fields reserved words.
Use appropriate data types.
Set relationships on tables, enforce referential integrity.
Create indexes, but not too many. Watch for ones
Access creates that you didn’t need.
Questions?