Base – Designing a Database

For a discussion of the formal theory of database design, see the Sum-it Database Design Course.

First Questions

Begin planning by asking yourself these questions:

  • What kind of data do I need to store?
  • What do I want to do with the data? What kind of reports do I want to be able generate?
  • How should the data be grouped

Simple linear databases only allowed you to work with one table or group of data in a data base. This limited what kind of data you could store and how it could relate. Relational databases like Base allow you to work with multiple tables of relatively independent data that can relate to each other in a variety of ways.

Figuring out how to group your data, or what tables you need, is an essential. Think carefully about what data is closely tied together, and what data needs to be independent of other groups (or tables) of data. In a simple database to catalog music, for example, you might want to three groups: Albums (Name, date, etc.), Songs, and Composers. These should be separate because an Album contains more than one song, and Composers(or Artists) produce more than one Album.

Planning Tables and Fields

Once you have given thought to these questions, you are ready to beginning planning what tables and fields you need.

Names of Tables and Fields

  • Field names must begin with a letter and must only contain letters, numbers, and underscores. No spaces or other characters.
  • Table names can contain spaces, but the standard is to use underscores instead.
  • Field names should be easily understandable by anyone using the database
  • Field names should be unique across the whole database. You can have a field called “ID” in each table, and the database will work (because Neo keeps track of what table it comes from), but it is more confusing for the User. It is better to name these fields Query_ID, Page_ID, etc., so it is clear in the field name what table it relates to.

Note: For more guidelines for creating good tables, see the book SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition) by John L. Viescas and Michael J. Hernandez and/or the Wikipedia article on Database Normalization.

List of Tables and Fields

Now make a list of the tables you need.

  • Include the names of all the fields that need to be included in each table.
  • Make sure to include a “Primary Key” in each table. Primary Keys are fields whose entry is unique for each record. Most often, it is best for the Primary Key to be an number (interger) that Base sets itself. But sometimes it makes sense for the primary key to be something else.
  • For each field, decide what type of field it needs to be (text, date, number, yes/no, etc.).
  • Decide how the table will relate to each other. What are the common fields?
  • If certain fields will usually (but not always) have a certain value (e.g. Country, etc.) make note of a default value to be entered when defining fields and creating the table.

Creating the Database Document

It is finally time to create a new database document. Under the File menu, choose New and then Database In the Database Wizard window, make sure the Create a new database radio button is selected. Click Next Make sure that the Yes, register the database for me and Open the Database for Editing settings are selected.
Click Finish.

The Next Steps

You are now ready to begin creating your tables. Table creation is covered in the following articles:

Creating a Table From a Spreadsheet
Creating a Table in Design View

Be sure to keep your notes about the design of your database. More decisions will need to be made later. It will be easier to make those decisions if you can refer to this list of tables and fields.

The following two tabs change content below.

allenpg

Latest posts by allenpg (see all)

Leave a Reply