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_users

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

       account_user_groups

       account_user_groups_switch

       account_user_info

admin_users

Admins with access to the DotSquare dashboard.

       admin_user_info

       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_occurrences

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

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_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_forms

Contains list forms created through dotSquare.

       cms_form_fields

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

cms_jobs

cms_media

Contains images and video data. This is used with cms_visual, org_locations, cms_portfolio, cms_simple_ads

       cms_media_groups

cms_menu

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

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_results

Contains data for the "Results" menu

       cms_results_details

cms_simple_ads

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_vendors

       cms_vendor_notes

cms_visual

data_location_codes

For developer use. Contains US states and data for each.

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_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_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_logins

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

log_onsite_search

log_subscriber_activity

log_user_behavior

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_third_party

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

       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_occurrences

       reserve_slots

site_access_areas

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

site_fonts

site_images

Images used in edit mode.

site_navigation

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

site_pages

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_conversions

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_invoices

       billing_invoices_contacts

Contains data on who an invoice was sent to.

       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_redeem

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

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_promos

For promo extra items, contains all details about the promotion.

              items_extra_items_promos_restrict

Contains black-listed brand(s) or item(s) for a specific promotion.

items_inventory

Balance list of inventory

items_media

items_modifiers

items_rejections

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

log_billing

Contains records of all invoices, transactions and more.

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.

              subscription_schedule_items

For if a coming subscription has a one-time change.

user_actions

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

user_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.