Phocas has a new user documentation site. This site will be retired soon.

Design a database

Owned by Denise McGettigan
Last updated: Dec 03, 2023
3 min read

Generally, the Phocas Implementations team will design your databases for you. However, you can design new databases by yourself.

As every business is different, there is no real 'right' or 'wrong' way to design a database. However, all database designs in Phocas follow the same basic process. Once you've mastered this process, you can go back and revise your database at any time, to add more data or change its setup. With some practice, you will be able to design more complex databases in Phocas.

Design process

The image and steps below outline the basic process of designing a database in Phocas (green steps). As you can see, there are some steps (purple) that you need to take before and after the design process.

  1. Get your data into Phocas - You need to have your data files in Phocas, ready to add to your new database. Generally, the Phocas Implementations team will do this for you but you can add more files by yourself.

  2. Design the database:

    1. Create the database - The most common starting point is to create a brand new database from scratch but you might have a pre-loaded database design that you can open and modify.

    2. Add (connect) data to the database - Add your transaction file and other files from the data sources panel into the Stream section of the Design tab.

    3. Map the data to the database - Match the columns of raw data to their relevant section on the Design tab (Dimensions, Properties, Measures and Date).

    4. (Optional) Customize the database components and add more data - You can do these tasks later if required, after you have built your database.

    5. Save the database.

  3. Build the database.

  4. Give users access to the database - A new database added to Phocas is not accessible by any user unless explicitly granted in the user or database management screens. 

If you are designing a finance database, while the basic process is much the same as that described above, there are a couple of important points to note. Please read the Design a finance database page alongside the pages listed above.

Before you start - some tips

Before you jump into creating a database, it is worth spending a few minutes to consider the following tips, as it will likely save you time later on.

  • Identify your data source
    What data do you need and where does it live? Identify your data sources and check the quality of the data. You'll get better results if your source data is as 'clean' as possible. It is likely your data sources are already in Phocas but you can get more data if required.

  • Think about the users
    The design of your database affects how your team can use Phocas. To ensure your database is user-friendly, take into account the needs and skills of your users, the required data structure and naming conventions. Also, decide on who you want to be allowed to see data. 

  • Keep it simple
    If you're a beginner at designing databases, make your first one simple. Once you have your first successful build, add more databases. Depending on your Phocas environment, you might have access to one or more simple database designs to help you get started.

  • Understand the database basics
    Ensure you have a good understanding of the standard business intelligence terms associated with databases, including dimensions, properties, measures, and streams. See the Overview of Designer for more information.

Start designing your database: Create the database.