Database tables, fields and relationships

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

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

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

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
false=customer/bill payer

 

 

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
Jones

 

person_given_name__c

first names of people and patients

James
Fluffy

 

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
patient-2346

 

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

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

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

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

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

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