This is an old revision of the document!


*

The following tables list the main tables in mSupply (There are some others, but we're not telling you about those!). This information will help you if you are building your own reports.

Note that if a field is indexed, finding information (querying) using that field will be very quick.

Table Description
Transactions A transaction record is stored for each invoice
Transaction lines one transaction line record is created for each line on an invoice
Items An item record is created for each product or service purchased or supplied
Item lines Each batch of stock at a particular location has one item_line record
Names Each patient, customer or supplier has one name record
Purchase Orders One record is created for each purchase order to a supplier
Purchase Order Lines Each record is an order for one item on a purchase order
Quotes and Quote Lines Quotations submitted by suppliers
Tenders Tenders created to invite quote submissions from suppliers
Requistions Tables that store customer submitted stock and order data
Stock_take and Stock_take_lines Stock takes and their related records
Store Information about a particular virtual store
Drug_Register Drug Registration records (for use in the Drug Registration module)
Table Description
Abbreviations Stores abbreviation codes and their expansion for dispensing directions
Account_code Stores account codes associated with each item
Backorders One record for Customer backorder.
Bill of Materials One record for each mSupply user
Contacts One record for each contact
Currency One record for each curency
Drug interactions Drug interactions and Drug interaction groups
Item Categories One record for item category
Labels Miscellaneous labels
Log Each record contains one event recorded by mSupply
Users One record for each mSupply user
Item Departments The departments that an item can be assigned to
Item Directions The default directions that are assigned to items
Item notes The notes that are attached to items
Item Warning Link A join table that stores which items have which warnings
Name category tables Tables that store the categories that can be assigned to names (customers, suppliers, donors, manufacturers etc.)
Prescribers One record for each prescriber
Regimen records, Regimen and Regimen Lines Everything to do with storing ARV regimen records
Reminders A reminder is an event with a due date- like a to-do
Stores Stores and join tables that control access per-store
Warnings The warnings which can be attached to items
Custom stock 5 Holds the details of the values that are selectable in the 5th user field when receiving stock
Custom stock 6 Holds the details of the values that are selectable in the 6th user field when receiving stock
Custom stock 7 Holds the details of the values that are selectable in the 7th user field when receiving stock.
Custom stock 8 Holds the details of the values that are selectable in the 8th user field when receiving stock
Table Description
Number and Number re-use Stores serial numbers such as invoice numbers
Preferences The data for each preference is in binary format. You're not interested.
Item store join
Field name Description Type Length Ind
Note that records are created for each item- there should always be one record for each item in each store
id Long
store_ID Long
item_ID Long
inactive true means item is not visible in this store. False means it is visible bool
Name store join
Field name Description Type Length Ind
Note that records are only created as needed- that is a missing record implies the name is invisible for that store
id Long
store_ID Long
name_ID Long
inactive true means name is not visible in this store. False means it is visible bool
Name group
Field name Description Type Length Ind
key Long
name Alpha 40
Name note
Field name Description Type Length Ind
note_type Alpha 3
entry_date Date
note Text
name_key Alpha 8
key Long
Permissions
Field name Description Type Length Ind
item_dept_key Long
name_group_key Long Ind
Permissions
Field name Description Type Length Ind
name_group_key Stores which name groups have permission to be supplied which item departments Long
item_dept_key Long
Prefs
Field name Description Type Length Ind
item Internally used: storing preferences of text type(Now deprecated in favour of pref blob) Alpha 20
value Alpha 60
Reports
Field name Description Type Length Ind
report_name Alpha 80
report_blob BLOB
owner_key Long
key Long
last_updated Date
Ship method
Field name Description Type Length Ind
key Long
method Alpha 80
Transaction category
Field name Description Type Length Ind
key Long
category Alpha 80
type “ci” for customer invoices
“pi” for prescriptions
“cc” for customer credits
“si” for supplier invoices
“sc” for supplier credits
“sr for repacks
“bu” for builds
“in” for inventory adjustments
Alpha 3
Transaction Notes
Field name Description Type Length Ind
key Long
note Text
Units
Field name Description Type Length Ind
key Long
units Alpha 60
comment Text
Web favourites
Field name Description Type Length Ind
name_key Alpha 8
Item_key Alpha 8
type Alpha 3
Web messages
Field name Description Type Length Ind
key Long
active Boolean
show_until_date Date
headline Text
message Text

Previous: Diagnostics Next: Finalising transactions

  • Last modified: 2016/05/30 23:55
  • by Craig Drown