Skip to main content Skip to main navigation Skip to footer

dotSquare Database

Understanding How DotSquare Databases are Organized

The database is the backbone of DotSquare. It requires a specific database structure to properly run and is updated automatically for every new version. For higher tier plans, like eCommerce and Multi-account plans, 2 databases with different structures, are required to be run.

DotSquare Database Flow

Database Requirements

In order for DotSquare to be able to properly connect to your database(s), please ensure the following.

  • MySQL 5.5 or greater

  • Database login credentials allow for full permissions

  • Both your web host and hosting plan allows for access from a different server than your own. This is more common on cheaper hosting providers/plans.





Main Database Tables

We separate and label our the tables in an organized fashion to make referencing the proper table quick and easy.


Table Name
Details

account_signup_access_codes

account_sub_people

For other persons attached to users. Useful for users' children or others who should not have their own login.

account_users

For users with logins or for listing users if no login is needed for users

       account_user_connections

Contains what types of interactions users can have (Ex. Liking, Following, Requesting, etc.)

              account_user_connections_switch

Contains the actual connection data.

       account_user_files

account_user_groups

       account_user_groups_switch

Controls which user(s) are assigned to which user group.

       account_user_group_batches

Batches of user groups

       account_user_group_batches_switch

Controls which group(s) are assigned to which batch.

       account_user_group_files

account_user_notifications

account_user_preferences

Contains profile preferences specific to each user. Types include Notifications and Privacy

       account_user_preferences_switch

Contains the actual preferences data.

account_user_status

Holds user profile status

admin_users

Admins with access to the DotSquare dashboard.

admin_user_invites

Stores invited admin data. This is different from adding an admin. In this case, the admin has the choice to accept the invite or not.

       admin_user_permissions

       admin_user_preferences

Contains DotSquare preferences specific to each admin

cal_calendars

Can have multiple calendars each with categories

       cal_events

              cal_event_tracks

Events can have multiple tracks that people can attend.

       cal_occurrences

For each instance of a recurring event.

cms_alerts

Contains all site alerts and accompanying data.

cms_categories

Contains the categories for each menu or sub-menu along with other critical data. For a full list of which menus are allowed to have categories visit the DotSquare Menu Section

$tab_spacecms_categories_order

If a menu allows for categories to be re-ordered, it is stored here. For a full list of which menus are allowed to have categories re-ordered, visit the DotSquare Menu Section

cms_contants

Contains constants that DotSquare will consider in it's functions (ex. email templates)

cms_custom_colors

Contains custom color codes of all dotSquare elements

cms_faqs

cms_feature_scopes

cms_feature_scopes_comparable_groups

cms_fields

Contains custom fields used for a menu. For a full list of custom fields visit the Custom Fields Section. For a full list of which menus are allowed to have custom fields visit the DotSquare Menu Section.

       cms_fields_data

Info on each completed custom field for each object

cms_files

cms_food_menu

cms_forms

Contains list forms created through dotSquare.

       cms_form_fields

Contains all fields, buttons, and data for each form.

cms_jobs_employers

For the employers of job postings

cms_jobs_positions

For job postings.

cms_media

Contains images and video data. This is used with cms_visual, org_locations, cms_portfolio, cms_simple_ads, cms_vendors, cms_jobs_positions, cms_references, cms_results

       cms_media_groups

cms_menu

Contains the dotSquare menus that are enabled for each partition type.

cms_notifications

Contains the notifications visible to DotSquare admins.

cms_obj_order

Contains data on how to order items in each re-orderable area. For a full list of which menus are re-orderable visit the DotSquare Menu Section

cms_portfolio

cms_profile_completion

For if you wish have admins complete certain steps inside the DotSquare dashboard. Useful with sub-accounts.

cms_references

Used for listing links to reference websites.

cms_results

Contains data for the "Results" menu. Used for things like race results.

       cms_results_details

cms_simple_ads

Contains data for the "Simple Ads" menu. Simple ads are used to display advertisements of the admin's choosing on a website.

cms_sliders

For animated images typically seen on welcome screens

       cms_slider_slides

cms_staff

cms_state

Contain information on how DotSquare is currently operating. This is not used much.

cms_tags

Contains tag data. This is used with cms_visual

cms_tag_groups

cms_tour_modules

For if you wish have admins complete certain steps inside the DotSquare dashboard. Useful with sub-accounts.

       cms_tour_module_switch

cms_vendors

       cms_vendor_notes

cms_visual

Contains data for the menus "Blog", "Galleries", and "Videos"

data_international_countries

For developer use. Contains countries data for each.

data_international_subdivisions

For developer use. Contains provinces/states and data for each.

data_location_codes

For developer use. Contains US states and data for each. (Will be deprecated soon)

data_us_holidays

For developer use. List of standard US holidays. This is used with org_hrs_exceptions

email_accounts

Contains email address credentials used with sending emails for your own email account.

email_lists

Custom dotSquare email lists. This is used with cms_alerts

       email_subscribers

       email_subscriptions

email_templates

Contains custom HTML email templates that you can design.

entry_bulletin

Contains posts for a message board

entry_contact

entry_correspondence

Contains reply messages for entry_user_support

entry_notifications

Contains contact info and delivery info for each visitor form. For a full list of which menus are allowed to receive notifications visit the DotSquare Menu Section

entry_testimonials

entry_user_support

log_account_users

Contains general analytics data for users. Examples of data stored are account creation and approved dates.

log_account_user_activity_daily

Contains analytics data for users grouped by day. An example of data stored is a visit to the site.

log_account_user_groups

Contains analytics data for user groups. Examples of data stored are user group creation and approved dates.

log_analytics_bots

Contains analytics data from visitors marked as bots. This can be useful to view what is crawling your site.

log_analytics_hits

Contains useful DotSquare Analytics data

log_custom_behaviors

       log_custom_behavior_groups

log_errors

log_logins

Contains user login info tied to account_users. Admin login info is stored on dotSquare's servers

log_onsite_search

log_subscriber_activity

org_hrs

Each location can have it's own hours with multiple hours listed per day.

org_hrs_exceptions

org_hrs_sets

Each location can have it's own set of hours. This is useful for example if a location has different hours in the winter and summer. Used with org_hrs

org_locations

org_service_areas

org_social_media

partitions

One of the most important tables.

       partitions_integrations

Contains API keys from third parties like Google, Facebook, MailChimp, and more.

       partitions_preferences_switch

Used for partition level preferences (as opposed to user or partition type levels)

       partitions_segment_types

       partitions_types

reserve_cal

For if using the DotSquare reservations system.

       reserve_actions

For custom switches for each reservation event.

              reserve_action_items

       reserve_events

       reserve_groups

              reserve_group_hrs

              reserve_group_hrs_exceptions

              reserve_group_hrs_sets

       reserve_occurrences

       reserve_preferences

       reserve_services_menu

       reserve_setup

       reserve_slots

site_access_areas

Sites can have different areas that are accessible to different users or visitors.

site_fonts

Fonts are pulled from here on page load.

site_images

Images used in edit mode.

site_languages

site_navigation

By default, Header, Footer, and Mobile navigation is added. Used with site_pages

       site_navigation_items

Contains all navigational items for each navigation menu.

site_pages

Each website page is stored here. Used with site_navigation

site_template_themes

       site_template_themes_data

site_text

Text used in edit mode.




eCommerce Database Tables

The eCommerce database is only used if your client has the eCommerce or Multi-account DotSquare plan. We separate and label our the tables in an organized fashion to make referencing the proper table quick and easy.


Table Name
Details

affiliates

       affiliate_links

Each affiliate can have multiple links and codes.

       affiliate_tracking

Contains every instance a code was accessed or used.

billing_acct

billing_acct_balanace

billing_contacts

Different data from the account_users table. Stores critical payment and contact info for Users, User Groups, and Guests.

       billing_contacts_info

In cases where the customer is a guest or the user has multiple points of contact, that information is stored here.

       billing_contacts_methods

Contains all payment methods for a contact.

       billing_contacts_switch

Partitions gain access to a billing contact using this reference list. With this, if a contact needs to be removed for a partition, the contact does not need to be deleted, the reference just needs to be removed.

billing_giftcards

       billing_giftcards_balance

billing_invoices

       billing_invoices_contacts

Contains data on who an invoice was sent to.

       billing_invoices_fulfillment_events

Contains data on a fulfillment event such as postal tracking info.

       billing_invoices_items

       billing_invoices_item_fulfillment

Contains data on all items that are eligable for fulfillment. Used with invoices_fulfillment_status

       billing_invoices_item_groups

       billing_invoices_item_returns

Used with billing_invoices_item_fulfillment

billing_payouts

Lists all deposits to bank accounts

       billing_payout_transactions

billing_transactions

       billing_transactions_contacts

Contains data on who a receipt was sent to.

billing_transfers

Only used for sub-accounts. Stored data about transfers for future possible returns / refunds.

carts

       carts_items

       carts_item_groups

       carts_locations

For if a cart has items from multiple partitions or locations.

       carts_promos_redeemed

Contains data on which promos were redeemed, used, and more.

import

Contains all the eCommerce bulk downloads or CSV imports

       import_equivalencies

Contains data on which locations, categories, brands, items, item variants, and images are connected to integrations.

       import_items

Contains the data for each import.

invoices_fulfillment_methods

       invoices_fulfillment_status

items

       items_1structure

Contains data on what organizational structure is stored for items. This generally controls any item browsing menus and how it can be filtered.

       items_2categories

              items_2_3_collections

       items_3options

Contains prices, UPC data and other information related to the price variation.

       items_4info

Contains a directory of all detail IDs used for each item.

       items_5details

Contains the information about items such as descriptions, subtitles, and more. Used with items_4info

       items_6fields

Lists all fields available to items. This will be available to admins as a feild when adding / editing items.

       items_7attributes

Used with items_1structure

       items_8brands

items_extra_items

Contains items such as tax rates, discounts, promos, late fees, delivery fees, and more.

       items_extra_items_delivery

For delivery extra items.

       items_extra_items_discounts

items_giftcards

       items_giftcards_media

Contains background images for gift cards

items_inventory

Balance list of inventory

items_media

items_modifiers

items_rejections

Contains all instances when an item was rejected by an admin.

items_secondary_cart_actions_library

Contains all data for if a visitor is required to perform another step when adding an item to their cart..

log_billing

Contains records of all invoices, transactions and more.

log_subscription_plans

Contains records of changes in subscriptions

reasons

Contains preset reasons to users for admin actions. Used with items_rejections

subscriptions

Contains all subscriptions and their respective data

       subscription_items

       subscription_plans

For preset plans.

              subscription_plan_items

       subscription_schedule

Calendar of the past and the next coming subscription.

user_actions

Contains eCommerce specific actions taken by a user, such as favoriting or wishlists. Used with user_generated_lists

user_generated_lists

Contains lists you have created for user actions.

variables

Contains eCommerce specific data. Not used much.




Custom Databases and Tables

You can add custom databases to be recognized by dotSquare that do not follow our structure through the dashboard. It is required to add a database to be recognized by DotSquare if referencing it within the DotSquare software. If you are referencing your custom database in your website only, then there is no need to add the database to DotSquare.