Database tables, fields and relationships
- Paolo Lencioni
This will show the important tables and fields where there are relationships. It will not list every table and every field.
There are 2 schemas that are used: slyncyhub and public.
slyncyhub contains all the cleansed and imported financial data
public contains all the data from the user interface such as variables, product mappings, staff
slyncyhub.invoice__c
This table contains financial information held in an invoice header
Field name | Data | example | relationships |
|---|---|---|---|
id | internally generate id/index. Joins the header to it’s relevant lines | 000000053081868a04 | invoice_line__c.invoice__r |
identifier__c | the invoice number in the source system (usually the unique invoice number but may be an internal index number) | 336455 | ???????? |
is_deleted | true/false/null | true/false |
|
issue_date_time__c | Date invoice was issued to customer | 2013-01-03 04:41:39 |
|
last_modified_date | Date invoice was modified | 2013-01-03 04:41:39 |
|
created_date | internal date when created in slyncy |
|
|
legal_monetary_total__c | Total tax inclusive value of invoice | $110.10 |
|
name | Inconsistently used and customer name is better to get from parties__c table |
|
|
note__c | field is not currently used | large varchar field of 32678 chars |
|
owner_id | The internal ID of the Slyncy datahub | 000000000000069dfa | All tables have an owner_id, this associates the data with the connector |
referenceid__c | field is not currently used |
|
|
status__c | field to indicate if invoice is finalized or not. Usually only finalized invoices are imported. | COMPLETED |
|
tags__c | indicates which splitter data belongs to if data gets split | branch1 |
|
accountingcustomerparty__r | the party that is responsible for paying the invoice (human) | 0000000535418915c7 | party__c.id |
customercontact__r | The party that the invoice relates to (patient). Inconsistently used as it is often impossible to trace mixed patient invoices to a single patient | 00000000057152066c | party__c.id |
relatedtransaction__r | inconsistently used |
|
|
tax_total__c | The amount of tax payable on the invoice. Not currently used |
|
|
customer_code__c | seems to be used to generate internal hashes |
|
|
client_key | primary key coming from source system, if it exists, otherwise a combination of fields to create a unique hash. Allows for internal identification of update vs insert |
|
|
source_id | Used to trace back to original record if splitters - internal use only |
| invoice__c.id on the source of the split hub |
slyncyhub.invoice_line__c
This table contains all the financial information of each invoice line.
Field name | data | example | relationship |
|---|---|---|---|
id | internally generated id/index. | 000000000000002b0d |
|
note__c | description of item invoiced | Consult Afterhours 6am Until 10pm |
|
unit_is_price_inc_tax__c | true/false. Usually true as always use inc tax values | true |
|
is_line_extension_amount_inc_tax | true/false. Usually true as always use inc tax values | true |
|
unit_price__c | Unit price of item. Inconsistently used and often have zero here depending on system | 171.61 |
|
line_extension_amont__c | the total of the line including tax | 22.30 |
|
invoiced_quantity__c | quantity invoiced | 0.2 |
|
created_date | internal date when created in slyncy |
|
|
last_modified date | date data was last changed. For API purposes |
|
|
name | inconsistently used and seems to have same value as id |
|
|
invoice__r | index feild of the invoice__c table | 000000000000001993 | invoice__c.id |
item__r | index key to the inventory_item__c table | 0000000000134665e8 | inventory_item__c.id |
order__c | not used |
|
|
staff_code__c | staff member who invoiced the item. Currently not an id due to source system limitations | JK/reception | public.dash_alias_staff |
client_key | primary key coming from source system, if it exists, otherwise a combination of fields to create a unique hash. Allows for internal identification of update vs insert |
|
|
converted_from__r | the id of the invoice line that converted this line so that conversions can be measured | 000000509901687b4e | invoice_line__c.id (refers back to same table) |
customer_contact__r | The PATIENT (not paying customer) that the line relates to | 000000004054926c33 | party__c.id |
|
|
|
|
|
|
|
|
slyncyhub.party__c
All patients and bill payers stored in this table.
Field name | data | example | relationship |
|---|---|---|---|
id |
|
|
|
auto_number__c | Internal unique id field |
|
|
company_identifier__c | Paying party identifier number in source system |
|
|
customer_identifier__c | patient/customer identifier in source system |
|
|
created_date | internal date when created in slyncy |
|
|
last_modified_date | Last change for API purposes |
|
|
email__c |
|
|
|
is_active__c | flag for when customers or patients are de-activated |
|
|
is_deleted__c | does not seem to be used. Rely on is_active for active records |
|
|
is_individual__c | true=patient |
|
|
main_address_city__c country__c postcode__c state__c street__c
| all address details | Cleveland Australia 4162 QLD Oak street |
|
mobile_phone__c | mobile phone number | 043956406 |
|
name | name of payer or patient as a long string | 8798 - Feline - Chantilly Ryan 63927 - Ian Macdonald |
|
owner_id | internal datahub/connector number |
|
|
person_birth_date | for patients we are storing birth date for some systems |
|
|
person_family_name__c | the surname of patients and people | Smith |
|
person_given_name__c | first names of people and patients | James |
|
person_honorific_prefix__c | Title | Mr,Mrs,Dr |
|
tags__c | tags for branch splitting | branch1,branch2 |
|
telephone__c | telephone number |
|
|
person_worksfor__c | reference back to the same table. Patients referred back to id of bill payer |
| party__c.id |
client_key | where possible we have tried to refer back to the party id in the source system in a human readable manner | client-64010 |
|
person_gender__c | where possible gender is stored for patients |
|
|
animal_neutered__c | for animals, neutering status recorded where possible |
|
|
animal_species__c | for animals species is recorded where possible | Canine,Dog,Cat,Feline etc |
|
slyncyhub.data_point__c
All financial data in the general ledger is stored as a datapoint with an associated data and time. Th
Field name | data | example | relationship |
|---|---|---|---|
id | unique id in slyncyhub database |
|
|
value | the numeris/financial value associated with the datapoint |
|
|
created_date | Internally generated created date |
|
|
date__c | date the datapoint was completed in the source system |
|
|
is_deleted | whether ot not deleted |
|
|
last_modified_date | internal last modified date |
|
|
name | this is actually a reference field to the financial_account__c table IF the row is a financal account row. If it is a manual datapoint then this is NULL |
| Sometimes financial_account__c.id if not a manual datapoint |
owner_id | internal datahub/connector number |
|
|
reference__c | seems to be the same as ‘name’ field unless it is associated with a splitter (has a tag) then it is different |
|
|
type__c | Indicates whether it is a manual or accounting software datapoint | ProfitAndLoss, MonMan |
|
client_key | the unique Id in the source system |
|
|
hash | Hash for detecting changes |
|
|
tags__c | used for tagging with a branch so we can split into business locations |
|
|
source_id | used for identifying the source of a split data row. |
|
|
|
|
|
|
|
|
|
|
slyncyhub.inventory_item__c
Stock items and services. Essentially anything that can appear on an invoice with a fee. A lot of fields in this table are not used as we do not utilise it for stock control and builds of materials
Field name | data | example | relationship |
|---|---|---|---|
id | unique id of product |
| item__r in invoice_lines__c table |
buying_price__c | purchase price from wholesaler |
|
|
catalogue_item_identification__c | unique id of product in source system |
|
|
created_date |
|
|
|
description__c | human readable description |
|
|
is_active__c |
|
|
|
is_bought__c |
|
|
|
is_buying_price_inc_tax__c |
|
|
|
is_selling_price_inc_tax |
|
|
|
is_sold__c |
|
|
|
last_modified_date |
|
|
|
name |
|
|
|
name__c | seems to hold same data as catalogue_item_identification__c |
|
|
owner_id | internal datahub/connector number |
|
|
selling_price__c |
|
|
|
tags__c | used for identifying the source of a split data row. |
|
|
public.dash_alias
The name of the staff member as it appears in Profitdiagnostix. Because people can have multiple log ins in source systems this allows us to associate more than one login user to an ‘alias’. It also allow us to give human unreadable login names more readable names eg AB → Andrew Barlow BVSc
Field name | data | example | relationship |
|---|---|---|---|
id | internal unique id |
| dash_alias_staff.alias_id |
data_hub | data hub it belongs to |
|
|
name | name of person |
|
|
email address |
|
| |
normalisation | ???? |
|
|
role | role in the business | primary, support, auto, inactive |
|
public.dash_alias_staff
This is the staff ID as they appear in the source system.
Field name | data | example | relationship |
|---|---|---|---|
id | internal unique id |
|
|
data_hub | data hub it belongs to |
|
|
staff | user login name as per source system |
|
|
alias_id | join to id in alias table |
| dash_alias.id |
public.dash_dashboard
The header entry for each dashboard
|
|
|
|---|---|---|
|
|
|
|
|
|
public.dash_dashrow
Each row or item in a dashboard. Essentially each visualisation you see in the dashboard and its configuration wrt. which chart, its position etc.
|
|
|
|---|---|---|
|
|
|
|
|
|
public.dash_report
Each report that is designed
|
|
|
|---|---|---|
|
|
|
|
|
|
public.dash_row
Each row or item in a Report (as opposed to a dashboard).
|
|
|
|---|---|---|
|
|
|
|
|
|