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_lines__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 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

 

 

 

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

 

 

 

 

 

 

 

 

slyncyhub.inventory_item__c

Field name

data

example

relationship

Field name

data

example

relationship

 

 

 

 

 

 

 

 

public.dash_alias

Field name

data

example

relationship

Field name

data

example

relationship

 

 

 

 

 

 

 

 

public.dash_alias_staff

Field name

data

example

relationship

Field name

data

example

relationship