Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available
Each table in your database should hold the information on one subject. You might think of a subject as a collection of related information with common characteristics. For example, if you were creating a database to hold information about the operation of your ice cream stand, you might have an IceCream table. If you decided to sell sundaes as well as cones, you might add a Toppings table. Then, to associate ice cream and toppings in particular combinations and record the prices, you might add a Sundaes table.
At this point in the design process, don’t be concerned about having too many tables. It’s much more likely in the early stages that you won’t have enough. Other steps in the process will make it clear whether or not your preliminary set of tables is correct.
Probably the best way to get started on identifying what tables you need is to look at your preliminary list of fields. Look for logical groupings of information.
The name of each table must be unique in the database and each field name must be unique within a table. Table names should normally be plural. Table and field names should be as brief as possible (see below) but also should clearly identify the subject of the table or the data in the field. Avoid abbreviations and acronyms if you can as they can be cryptic to another user of the database. Employees would be a good table name; R2D2 would not be.
Different RDBMS products have different restrictions on the length of table and field names and which characters are allowed. Usually, you can’t go wrong if you use:
Most products are not case-sensitive in table and field names.
Some designers prefer to follow a naming convention that includes a tag that identifies each object (for example, tblEmployees for a table about Employees) and includes information about the data type in the field name (for example, curPayRate for a field of a currency data type). This can be very useful in identifying the different objects in the application (for example, if you wanted to give a table and a query the same name) and becomes almost a necessity if you begin developing code to help control your application. One such convention is the Leszynski Naming Convention (LNC). You can also create your own convention or there may be one already in use at your company.