Design a database
Create and set up a new database without the help of a Phocas Implementations consultant.
Last updated
Create and set up a new database without the help of a Phocas Implementations consultant.
Last updated
User permissions: Administration > Databases and Sync
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.
Watch this Phocas Academy video: Design your own database
Watch these PUG Training videos: Database Designer and Loading new data sets
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.
First, 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.
Next, design the database:
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.
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.
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).
(Optional) Customize the database components and add more data - You can do these tasks later if required, after you have built your database.
Next, build the database, then give users access to that database. A new database added to Phocas is not accessible by any user unless explicitly granted in the user or database management pages.
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 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.
